Having a Cell on Form Sheet Look up Last Value in a Column on Another Sheet

[USFengBULLS]

Hello,

I am trying to have a certain cell on a form be populated at all times of a currency value on another sheet that is log.
Basically, I need it to look up the Last value/cell that has a value in it in Column E on the Log Sheet.

1. Do I use Lookup or Vlookup to tackle this?
2. Would I Call the procedure/macro at the Workbook Open () Level?

​

 

[USFengBULLS]

Well, I figured it out with =LOOKUP(9.999999E+307,'CO LOG'!E9:E150) in the cell I want to store this value in.
However, I still would like to know a way of doing this in VBA. Any help would be appreciated, thanks.

 

[steve the fish]

Heres how you could find that value. You dont say what to do with it:

MsgBox Sheets("CO LOG").Range("E" & Rows.Count).End(xlUp).Value

 

[USFengBULLS]

I want to store it in on Sheet1.Range("D5") when the user opens the workbook everytime.

 

[steve the fish]

I prefer to just call other macros when using Workbook_Open so in the ThisWorkbook module place:

Private Sub Workbook_Open()

LastValue

End Sub

Then in a standard module place this:

Sub LastValue()

Sheet1.Range("D5").Value = Sheets("CO LOG").Range("E" & Rows.Count).End(xlUp).Value

End Sub

 

[USFengBULLS]

Yes, Thank you for that. I believe it is not working though only beacuse of the first 9 cells in the E column on that CO LOG Sheet. Cell E1 & E2 are part of a merged cell, Cells E3:E7 are all blank cells, Cell E8 is part of merged cell and Cell E9 is the header or beginning of were the currency values begin. Cells E10:E150 will be the range I need this Statement to check through down to the last cell that has a value in it.

 

[steve the fish]

If you only want to check from cell E150 back up to E10 why does it make a difference whether any cells above are merged? What exactly does its not working mean?

 

[USFengBULLS]

Meaning its not storing the last value that is there in that column. The last value in that column is $100...but when I run the ThisWorkbook Open () that call the LastValue Macro the cell is blank on Sheet1.Range("D5") when it should be $100.
Sorry, I just thought the merged cell and blank cells before it were effecting it and therefore being blank.