help with vlookup or similar

[keri]

Hi,

I've been through lots of old posts but cannot seem to make anything
work.

I need to populate column B on sheet 1 with values from columns A:F on
sheet 2, based on the value in column A sheet 1

For example if on sheet1!A1 the value was "dog" I need to have vba
code to find ""dog anywhere in sheet2!a1:f100 and return the value one
column to the left of where it is found into sheet1!b1

This needs to be done in code. Many thanks,

 

[Dave Peterson]

This doesn't look like a job for =vlookup() to me.

And since you want to return the cell to the left, you're really looking at
B1:F100, right. If you find Dog in column A, then there's nothing to the left
to bring back.

And do you really mean that you only want to bring back the match for A1?

Option Explicit
Sub testme02()

Dim ActWks As Worksheet
Dim SearchWks As Worksheet
Dim RngToSearch As Range
Dim FoundCell As Range
Dim myRng As Range
Dim myCell As Range

Set ActWks = Worksheets("Sheet1")
Set SearchWks = Worksheets("sheet2")

With ActWks
Set myRng = .Range("a1")
'or for all the used cells in column A
'Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With SearchWks
Set RngToSearch = .Range("b1:F100")
End With

For Each myCell In myRng.Cells
With RngToSearch
Set FoundCell = .Cells.Find(what:=myCell.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
'not found???
'give a message or just leave it alone?
myCell.Offset(0, 1).Value = "Not Found!"
Else
myCell.Offset(0, 1).Value = FoundCell.Offset(0, -1).Value
End If
Next myCell

End Sub

I used lookat:=xlwhole and matchcase:=false. You may not want those.