Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default OFFSET LOOKUP function in VBA?

Hi -

looking for a bit of guidance please. I am using a Userform to enter data into a database. Each database entry is entered as a new row and I have been using the following VBA code to specify the column and then find the next empty row to enter the data in.

I am trying (with not much success) to alter the code so that it finds the column header rather than specifying the column using RowCount - this is so that if the database changes (I.e. a column added to somewhere in the middle) that the data will still be entered in the correct location.

In the example code below the first offset enters data into column A titled "Test Date"; offset 2 enters to column B titled "Test Time"; offset 3 enters to column C titled "name".



'Offset function to find the next blank cell from A1 for data inputs

RowCount = Worksheets("Data").Range("A1").CurrentRegion.Rows. Count

With Worksheets("Data").Range("A1")
.Offset(RowCount, 0) = CDate(Me.txtTestDate.Value)
.Offset(RowCount, 1) = Me.ComboTime.Text
.Offset(RowCount, 2) = Me.txtName.Value


Thanks in advance
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default OFFSET LOOKUP function in VBA?

Hi again,

Am Fri, 4 Oct 2013 13:20:58 +0200 schrieb Claus Busch:

Cells(FERow, c.Column) = CDate(Me.txtTestDate.Value)


a bit shorter:

Private Sub CommandButton1_Click()
Dim FERow As Long 'First empty row
Dim c As Range

FERow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Me.txtTestDate 'Header name is TestDate
Set c = Worksheets("Data").Range("1:1") _
.Find(Right(.Name, Len(.Name) - 3), LookIn:=xlValues)
Cells(FERow, c.Column) = CDate(.Value)
End With
With Me.cmbTestTime 'Header name is TestTime
Set c = Worksheets("Data").Range("1:1") _
.Find(Right(.Name, Len(.Name) - 3), LookIn:=xlValues)
Cells(FERow, c.Column) = .Text
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default OFFSET LOOKUP function in VBA?

Hi thanks for the reply -

think I am almost there. Throws up a debug error and highlights : Cells(FERow, c.Column) = .Text

If I replace with the same as the first i.e. Cells(FERow, c.Column) = CDate(.Value) it appears to work fine.

What is this part of code stating? Sorry, not familiar with this line of code

Cheers
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default OFFSET LOOKUP function in VBA?

Thanks for your help it has worked a treat!

Out of interest what does the CDate part of the code below refer to?

Cells(FERow, c.Column) = CDate(.Value)

I have been using this line of code in the following With statements with no apparent issues - but just want to check I have got this right

Cells(FERow, c.Column) = .Text

.... changing .text for . value where appropriate.

Really appreciate the time to respond! you have helped save many hours!!!
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default OFFSET LOOKUP function in VBA?

Thanks so much for your time

I learn something every time I post on here!!
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default OFFSET LOOKUP function in VBA?

Sorry anther problem relating to this ....

I am getting an error after adding a number of these (I am using the format below). The error I get is "Run Time error '1004' Method 'Range' of object'_worksheet' failed. Any ideas why this might be happening? I haven't changed anything else in the VBA code

With Me.txtAnthroInitials
Set c = Worksheets("Data").Range("1:1") _
.Find(Right(.Name, Len(.Name) - 3), LookIn:=xlValues)
Cells(FERow, c.Column) = .Text
End With

With Me.cmbAnthroTime
Set c = Worksheets("Data").Range("1:1") _
.Find(Right(.Name, Len(.Name) - 3), LookIn:=xlValues)
Cells(FERow, c.Column) = .Value
End With
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup, then offset? SteW Excel Worksheet Functions 1 February 28th 08 03:17 PM
Can I use LOOKUP as the reference in OFFSET function? Danni2004 Excel Discussion (Misc queries) 5 June 15th 07 07:15 AM
Lookup with offset Mike K Excel Worksheet Functions 6 March 21st 07 05:20 PM
Lookup and offset Squeaky Excel Discussion (Misc queries) 1 March 30th 06 08:34 PM
lookup with offset? GEORGIA Excel Worksheet Functions 2 August 17th 05 09:28 PM


All times are GMT +1. The time now is 12:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"