Cell value from using XLUP

[Wes]

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

 

[Scott Huish]

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

 

[Wes]

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.

 

[Rick Rothstein]

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...

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

 

[Wes]

Thanks Rick,

You understanding is exactly what im looking for.

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

Wes

 

[Rick Rothstein]

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.

 

[Wes]

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

Values in Column B are text from B2 onward.

 

[Wes]

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.

 

[Scott Huish]

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
	B	C	D	E	F
1	128
2					14
3					16
4	16
5	14
Sheet1

 

[Rick Rothstein]

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?