VB Macro - Put data in first empty cell in column not row

[MatthewSeven]

Hey Guys!

I have the following code to add data to a particular column but it looks for an empty row rather than looking for an empty column cell which is resulting in data being overwritten. Been searching for ages and get the same code for just empty row, Any fixes guys?



Private Sub CommandButton1_Click()


Dim LastRow As Long


LastRow = Worksheets("Totals").Range("A65536").End(xlUp).Row + 1
Worksheets("Totals").Range("DW" & LastRow).Value = TextBox1.Text
Worksheets("Totals").Range("DX" & LastRow).Value = Date
Worksheets("Totals").Range("DY" & LastRow).Value = Format(Now, "HH:mm")
MsgBox "Added"


Unload Me


End Sub

Thanks, Matthew

 

[VoG]

Maybe try

Private Sub CommandButton1_Click()


Dim LR As Long, LC As Long
LR = Worksheets("Totals").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Worksheets("Totals").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Worksheets("Totals").Cells(LR + 1, LC + 1).Value = TextBox1.Text
Worksheets("Totals").Cells(LR + 1, LC + 2).Value = Date
Worksheets("Totals").Cells(LR + 1, LC + 3).Value = Format(Now, "HH:mm")
MsgBox "Added"


Unload Me


End Sub

 

[MatthewSeven]

No unfortunately it still puts it on the next row rather than just the next cell down in the column DW.

 

[farmerscott]

Mathew,

it is a little unclear of what you want.

Do you want the data inputed in Col "DW" as-

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Last row
[/TD]
[TD]text box
[/TD]
[/TR]
[TR]
[TD]Last row +1
[/TD]
[TD]date
[/TD]
[/TR]
[TR]
[TD]last row +2
[/TD]
[TD]time
[/TD]
[/TR]
</TBODY>[/TABLE]




FarmerScott

 

[MatthewSeven]

Mathew,

it is a little unclear of what you want.

Do you want the data inputed in Col "DW" as-

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Last row[/TD]
[TD]text box[/TD]
[/TR]
[TR]
[TD]Last row +1[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TD]last row +2[/TD]
[TD]time[/TD]
[/TR]
</tbody>[/TABLE]




FarmerScott

Hey Scott!

Well the text box data is to go into column DW
Date to go in DX
Time into DY

I just need the values to go into the next empty cell in the column rather than it seeing if there is an empty row. At the minute if there is an empty row and data is in DW,DX,DY the macro will overwrite it instead of writing to the next empty cell in column.

Hope u kinda get what i mean lol
Thanks, Matthew

 

[farmerscott]

Mathew,

that explains alot.

Have a look at Col A. Does the last cell in Col "A" match the last used cell in Col "DW"? Your code

LastRow = Worksheets("Totals").Range("A65536").End(xlUp).Row +1

finds the last used cell in Col A and then adds 1 row to find the first empty cell to put the data in. Thus if Col "A" and Col "DW" do not have the same last cell then your code will overwrite the existing data.

Try

LastRow = Worksheets("Totals").Range("DW65536").End(xlUp).Row +1

This will find the last used cell in Col "DW" and add 1 row.

Are you using Excel 2003?

Hope that helps,

FarmerScott

 

[MatthewSeven]

Ahhhhhhhh Now i get it!! lol Thanks Scott! Very helpful!

Matthew