Copy or refer to last Entry in a Row

[BillHassall]

I have searched through a number of posts and can't find anything that refers to this.
What I want to do is either create a formula in a cell that refers to the last populated cell in a different worksheet e.g for instance today I will populate AT2 and tomorrow AU2, the next day AV3 and I want this data on my daily report.

Alternatively I am using code to already copy and paste information, so I just need to find out how I refere to the last populated cell in a row, I have tried all manner of xldown and end and rows.count, buut can't get anything to work

Thanks

Bill

 

[JackDanIce]

Do you mean VBA code or an Excel formula?

VBA code:

=Cells(2,Columns.Count).End(xlToLeft).Select

Excel formula:

=HLOOKUP(9.99999999999999E+307,2:2,TRUE,TRUE)

I usually name that long number as a named range, e.g. BigNum so formula is easier to read, i.e:

=HLOOKUP(BigNum,2:2,TRUE,TRUE)

 

[BillHassall]

Thanks for the quick reply, the Hlookup works a treat and so does the VBA apart from the fact that I have formulas in all of the cells going forward that return "" values if other cells are empty and instead of going to the last cell with a number in it, it goes to the last cell with a formula in it. Is it possible to refer to the last cell with a result rather than a formula

Thw xltoleft is what I have been looking for so thanks again

Bill