Last Row Question?

M

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
 
B

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)
 
M

Michael168

Hi, Tom

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

Regards
Michael
 
T

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
 
J

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

Tom Ogilvy said:
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
Click to expand...
 
T

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

JMay said:
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
Click to expand...
 
B

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)
 
T

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
 
B

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)

Tom Ogilvy said:
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 said:
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)
Click to expand...
Click to expand...
 
T

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

Bob Phillips said:
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)
Click to expand...
 
B

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)

Tom Ogilvy said:
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

Bob Phillips said:
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)
Click to expand...
anything
to
Click to expand...
Click to expand...
 
Top