Vlookup with Offset

[Moonbeam111]

I'm a little bit rusty on my excel knowledge and I know this one is beyond me. And I couldn't figure it out through looking through the message boards, although there were similar topics.

I have two sheets Sheet 1 and Sheet 2 in my workbook. I have a word in range ("F7") in the first Sheet 1 that must be found within range("J1254:J1378") in Sheet 2. When it finds the value in the Sheet 2's selected range ("J1254:J1378"), I would like it to search for the first empty cell starting from the cell it found (going up, not down or across) and offset from that empty cell to the right one. Than I would like it to copy and paste that found offset value to Range ("D3") in Sheet 1. How would I go about this?

Advice would be appreciated.

 

[rollis13]

Have try, may need some tweaking in case of issues (example: extra spaces in word, word not found, ecc.).

Option Explicit
Sub FindFirstEmptyUp()
    Dim cell  As Range
    Set cell = Sheets("Sheet2").Range("J1254:J1378").Find(What:=Sheets("Sheet1").Range("F7"), LookAt:=xlWhole).End(xlUp).End(xlDown).Offset(-1, 0)
    Debug.Print cell.Address                      '<- testing address, response in Immediate pane (Ctrl+G if pane not visible)
End Sub

 

[Moonbeam111]

Thank you, this is very close to what I'm needing. Only 1 problem, it returns the correct cells address (i.e. $K$1254) instead of the cells name in the output in sheet1 range("D3"). How would I go about fixing that?

 

[rollis13]

I would like it to search for the first empty cell starting from the cell it found (going up, not down or across) and offset from that empty cell to the right one.

If I correctly understood you could use:

Sub FindFirstEmptyUpOneRight()
    With Sheets("Sheet1")
        .Range("D3") = Sheets("Sheet2").Range("J1254:J1378").Find(What:=.Range("F7"), LookAt:=xlWhole).End(xlUp).End(xlDown).Offset(-1, 1)
    End With
End Sub

 

[Moonbeam111]

I tried your original code again and its actually pointing at the wrong offset.


Here is how my columns look in sheet2

	Title
price 1		description of price 1
price 2		description of price 2
price 3		description of price 3

Say I'm looking for "price 2". I need to offset that to reach the word "Title". No matter which word I'm looking for (example: price 1, price 2, or price 3) Id like it to offset to the word title.

So, in a macro, I'd like to do this by first finding and matching the correct word (example: price 1, 2, or 3) and then going up to the first empty cell and then going right one so that the offset stops on the word "Title". And then the word (example: "title") is put in range("D3") of sheet1.

Hopefully this clears things up any misunderstandings.

 

[Moonbeam111]

I apologize you're new code you posted works! Thanks!

 

[rollis13]

I apologize you're new code you posted works! Thanks!

Thank goodness, my head was bursting trying to figure out the exact offset .
Thanks for the positive feedback, glad having been of some help.

 

[Peter_SSs]

Say I'm looking for "price 2". I need to offset that to reach the word "Title". No matter which word I'm looking for (example: price 1, price 2, or price 3) Id like it to offset to the word title.

So, in a macro, I'd like to do this by first finding and matching the correct word (example: price 1, 2, or 3) and then going up to the first empty cell and then going right one so that the offset stops on the word "Title". And then the word (example: "title") is put in range("D3") of sheet1.

Then are you really sure the posted code does that?

Here is my sample data. It is the same as yours but I have added some text below "Title". I did this because without it, the code from post #4 returned nothing to Sheet1 D3 for me.

Moonbeam111.xlsm
	J	K	L
1253		Title
1254	price 1	wrong1	description of price 1
1255	price 2	wrong2	description of price 2
1256	price 3	wrong3	description of price 3
1257
Sheet2

With that extra data in Sheet2 I ran the code again and this is what my Sheet1 looks like.

Moonbeam111.xlsm
	D	E	F
3	wrong2
4
5
6
7			price 2
Sheet1

By my understanding of your description you might need something more like this.

Sub Test()
  Dim rFound As Range
  
  With Sheets("Sheet1")
    Set rFound = Sheets("Sheet2").Range("J1254:J1378").Find(What:=.Range("F7").Value, LookAt:=xlWhole)
    If rFound Is Nothing Then
      MsgBox .Range("F7").Value & " not found"
    Else
        If Len(rFound.Offset(-1).Value) = 0 Then
          .Range("D3").Value = rFound.Offset(-1, 1).Value
        Else
          .Range("D3").Value = rFound.End(xlUp).Offset(-1, 1).Value
        End If
    End If
  End With
End Sub

 

[Moonbeam111]

There might have been some kind of confusion but Rollis13's code seems to work for my purposes. I've been using it and, hey, if it isn't broke why fix it??

But I appreciate you posting and giving some assistance so I will try your code later today.

 

[Moonbeam111]

Thank you Peter_SSs your code also works great. I didn't have to tweak it at all. I'll use yours from now on and keep rollis13's as a backup.