How to get last entered value in a ROW?

[Excelwiz wanabee]

Hi,

I'm wondering if ya'll excel "wizards" can help me out. I want to be able to
get the "last entered" value for a particular row and subtract from it a
fixed field (i.e. L15-F15 where L15 is the last entered value (or G15, H15,
etc.; increments monthly) and F15 is a field that does not change in the
equation.)

Thanks,

Bill

 

[Ron de Bruin]

Try this to get the last value of row 15

ActiveSheet.Cells(15, Columns.Count).End(xlToLeft).Value

 

[Chip Pearson]

Bill,

Try something like

Dim LastRng As Range
Set LastRng = Range("IV15").End(xlToLeft)
MsgBox LastRng.Address


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

If you want a worksheet function, this gets the L15

=OFFSET(A15,,COUNT(15:15)-1)

--

HTH

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

 

[Excelwiz wanabee]

Hi Bob,

When I paste that into cell D15 I get a blue dot followed by a zero - and is
A15 supposed to be F15? I get same result.

Bill

 

[Excelwiz wanabee]

Ron - please excuse my ignorance but where do I paste that into?

Bill

 

[Excelwiz wanabee]

Chip - how can I get that value into, say, cell D15? And where do I paste
that programming? Please
excuse my ignorance

Bill

 

[Ron de Bruin]

Hi Excelwiz

Sub test()
ActiveSheet.Range("c1").Value = ActiveSheet.Cells(15, Columns.Count).End(xlToLeft).Value
End Sub

Alt-F11
Insert>Module from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "test" and press Run

 

[Excelwiz wanabee]

Tom, that appears to work! And I looked at the MATCH function but am unsure
as to the purpose of values you entered for that function; can you explain
why the 9.9999... ?

Thanks,

Bill

 

[Ron Rosenfeld]

Hi,

I'm wondering if ya'll excel "wizards" can help me out. I want to be able to
get the "last entered" value for a particular row and subtract from it a
fixed field (i.e. L15-F15 where L15 is the last entered value (or G15, H15,
etc.; increments monthly) and F15 is a field that does not change in the
equation.)

Thanks,

Bill

If there are no "blanks" then Bob Philips worksheet formula will work fine. If
there may be blanks in the row, then the *array-entered* formula:

=F15-OFFSET(A15,0,-1+MAX(ISNUMBER(15:15)*COLUMN(1:256)))

should subtract from F15 the last numeric entry in Row 15.

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron

 

[Bob Phillips]

Ron,

Good point!

Bob

If there are no "blanks" then Bob Philips worksheet formula will work fine. If
there may be blanks in the row, then the *array-entered* formula:

=F15-OFFSET(A15,0,-1+MAX(ISNUMBER(15:15)*COLUMN(1:256)))

should subtract from F15 the last numeric entry in Row 15.

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron