Cell value from using XLUP

Wes

Board Regular
Joined
Jan 30, 2004
Messages
195
Hello All,

I have a task pertaining to getting a value into a cell using XLUP.
What I'm trying to do is column B contains a list of items. Using XLUP id like to find the last value in column B and place it in D2. Then in D3 that value would be the second value up from the first value found in column B using XLUP.

Any help would be really appreciated.

Thanks

Wes
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Code:
Sub test()
Dim c As Range
Set c = Range("B" & Rows.Count).End(xlUp)
Range("D2") = c
Set c = c.End(xlUp)
Range("D3") = c
End Sub
 
Upvote 0
Thanks Scott,

That works great to capture the correct value for F2. But the value in F3 is the top value of the list.

In the example im using the value for F2 is from B5 (the first value up in the column) then in F3 its showing the value from B1. The value id like to place into F3 would be the B4 value.
 
Upvote 0
Assuming your data is in Column B starting at cell B2 and that you want you output to go to Column F starting at cell F2, does this macro do what you want...
Code:
Sub ReverseListFromColumnB()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  With Range("F2:F" & LastRow)
    .Formula = "=INDEX(B:B,23-ROW(B2:B" & LastRow & "))"
    .Value = .Value
  End With
End Sub
 
Upvote 0
Thanks Rick,

You understanding is exactly what im looking for.

However the macro for some reasons is returning "0" values ... thoughts?

Wes
 
Upvote 0
Thanks Rick,

You understanding is exactly what im looking for.

However the macro for some reasons is returning "0" values ... thoughts?
It worked for me when I tested it on my made up data.

What do you mean "returning "0" values"? Where... in every cell or only in the UNKNOWN cells?

What is in the cells of Column Y... constants or formulas? If formulas, post the formula in cell Y2.
 
Upvote 0
The value shown in the F2 and downward is zero "0".

Values in Column B are text from B2 onward.
 
Upvote 0
It worked for me when I tested it on my made up data.

What do you mean "returning "0" values"? Where... in every cell or only in the UNKNOWN cells?

What is in the cells of Column Y... constants or formulas? If formulas, post the formula in cell Y2.

The value shown in the F2 and downward is zero "0".

Values in Column B are text from B2 onward.
 
Upvote 0
You changed it from Column D to Column F, but that's what my code does. This is the result after running my macro, how is it not right?


Excel 2010
BCDEF
1128
214
316
416
514
Sheet1
 
Last edited:
Upvote 0
The value shown in the F2 and downward is zero "0".

Values in Column B are text from B2 onward.

Hmm! That is not what I get with my made up data. Can you post a copy of your workbook to DropBox so that we can download it here in order to test our proposed solutions directly on your actual data?
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top