Last Row Question?

[Michael168]

I have a workbook with 2 sheets.
Sheet 1 contains the values which be updated daily.
Sheet 2 uses to get the values from the last row of sheet 1 to perfor
calculation.

My question is how to make A2,A3 of sheet2 always get the value o
column K,L of the last row in Sheet 1?

Thanks
Michae

 

[Bob Phillips]

With Worksheets("Sheet1")
Worksheets("Sheet2").Range("A2").Value =
.Range("K" & Cells(Rows.Count,"K").End(xlUp).Row).Value
Worksheets("Sheet2").Range("A3").Value =
.Range("L" & Cells(Rows.Count,"L").End(xlUp).Row).Value
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Are you looking for a formula or vba code.

Are the values numeric or text?

 

[Michael168]

Hi, Tom

The values are in numeric
I am looking for a formula.
Thanks to Bob Philips for the VBA code.

Regards
Michael

 

[Tom Ogilvy]

A2: =INDEX(Sheet1!K:K,MATCH(9.99999999999999E+307,Sheet1!K:K))

A3: =INDEX(Sheet1!L:L,MATCH(9.99999999999999E+307,Sheet1!L:L))


If column K (or L) is blank, it will return #N/A

 

[JMay]

Tom:
The row argument within the Match() function below is:
9.99999999999999E+307 <<?? This evidently is computereeze for a real
row-number.
What does it equate to?
TIA,
JMay

A2: =INDEX(Sheet1!K:K,MATCH(9.99999999999999E+307,Sheet1!K:K))

A3: =INDEX(Sheet1!L:L,MATCH(9.99999999999999E+307,Sheet1!L:L))


If column K (or L) is blank, it will return #N/A

 

[Tom Ogilvy]

It is the largest number Excel can store. It doesn't have anything to do
with a row number . Match returns the row number.

--
Regards,
Tom Ogilvy

Tom:
The row argument within the Match() function below is:
9.99999999999999E+307 <<?? This evidently is computereeze for a real
row-number.
What does it equate to?
TIA,
JMay

 

[Bob Phillips]

In a previous question on the same topic, I was told that ...

1.79769313486232E+308 is the largest value a cell can hold, but it must
be calculated. 9.99999999999999E+307 is the largest value that can be
entered into a cell. Apparently the interpreter bails without checking
if the value can be held if the exponent is 308.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Limits specified in the Help:
Largest allowed positive number 9.99999999999999E307


the number you cite is the upper limit for a IEEE double precision number
and supposedly Excel operates with IEEE double precision. So for academic
interest, I assumw what you were told is correct. I say academic interest,
because Match won't accept the larger number. So i guess the approach has
its limitations although unlikely to be encountered.


--
Regards,
Tom Ogilvy

 

[Bob Phillips]

It wasn't a comment about Match, it was a comment about the largest number.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Limits specified in the Help:
Largest allowed positive number 9.99999999999999E307


the number you cite is the upper limit for a IEEE double precision number
and supposedly Excel operates with IEEE double precision. So for academic
interest, I assumw what you were told is correct. I say academic interest,
because Match won't accept the larger number. So i guess the approach has
its limitations although unlikely to be encountered.


--
Regards,
Tom Ogilvy

In a previous question on the same topic, I was told that ...

1.79769313486232E+308 is the largest value a cell can hold, but it must
be calculated. 9.99999999999999E+307 is the largest value that can be
entered into a cell. Apparently the interpreter bails without checking
if the value can be held if the exponent is 308.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

My comment was about the largest number as well. This sounds like the old
joke about engineers - where the information is absolutely correct, but
absolutely useless.
--
Regards,
Tom Ogilvy

It wasn't a comment about Match, it was a comment about the largest number.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

The version I heard concerned Microsoft (what a surprise!)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

My comment was about the largest number as well. This sounds like the old
joke about engineers - where the information is absolutely correct, but
absolutely useless.
--
Regards,
Tom Ogilvy

It wasn't a comment about Match, it was a comment about the largest number.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

anything

to