Finding the Value in the "last" cell?

  • Thread starter Thread starter R
  • Start date Start date
R

R

I have an excel file that has a vba macro that grabs data from
someplace else every few minutes and writes the data to the next empty
line on a sheet "n". SInce the macro just appends the info to the
sheet when event x is triggered I never know what the last line (row
number) of the sheet will be. I need to have cell a1 (for example)
give me the data is whatever is *currently* the most recent (or latest
appended) data from column E. Thus, if the macro five minutes ago was
on row 245 and E245 had the text, "FRED" and now event x triggers the
macro so a new line of data is written to row E246 with E246 now
reading, "SAM" I need cell A1 to change from FRED to SAM. I hope this
explanation was clear enough. I have no clue how to do this-- thanks
for the help!!


[If it matters, the Macro Code is:
If (Now > Range("E1").Value + TimeSerial(0, 4, 0)) Then
NextRow =
Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(NextRow, 1) = Now()
Cells(NextRow, 2) = Now()
Cells(NextRow, 3) = Cells(1, 3)
Cells(NextRow, 4) = Cells(1, 4)
End If
 
not sure of your spreadsheet or existing code, but won't

cells(1,1).value = cells(nextrow,5)

do the trick
 
Perfect! Thank you both!


in Cell A1 put:

=OFFSET(A1,COUNTA(E:E)-1,4)

Regards

Trevor


R said:
I have an excel file that has a vba macro that grabs data from
someplace else every few minutes and writes the data to the next empty
line on a sheet "n". SInce the macro just appends the info to the
sheet when event x is triggered I never know what the last line (row
number) of the sheet will be. I need to have cell a1 (for example)
give me the data is whatever is *currently* the most recent (or latest
appended) data from column E. Thus, if the macro five minutes ago was
on row 245 and E245 had the text, "FRED" and now event x triggers the
macro so a new line of data is written to row E246 with E246 now
reading, "SAM" I need cell A1 to change from FRED to SAM. I hope this
explanation was clear enough. I have no clue how to do this-- thanks
for the help!!


[If it matters, the Macro Code is:
If (Now > Range("E1").Value + TimeSerial(0, 4, 0)) Then
NextRow =
Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(NextRow, 1) = Now()
Cells(NextRow, 2) = Now()
Cells(NextRow, 3) = Cells(1, 3)
Cells(NextRow, 4) = Cells(1, 4)
End If
Click to expand...
Click to expand...
 
Back
Top