hlookup via vba
 

Hello all,

I am trying to lookup a certain value, "VALUE", in a range on a certain
sheet, then once the cell is found with that word, offset by one column and
copy thae value in the adjacent cell. this data is then returned to a
separate sheet and pasted in a specified cell. Following is my current
attempt at this, but it isn't returing the desired result. Any help?

The first macro gets the value of the loadstep and feeds it into the second
macro. This part works fine, as the pasting of "Load Step" & loadstep in the
second macro works as intended.

Sub feed_loadstep()
Summary.Activate
Range("a115:d119").ClearContents

Sheets("LoadSteps").Activate
Do While Cells(5 + i, 1) < ""
LS = Cells(5 + i, 1)

Call read_disp_3(LS)

Sheets("LoadSteps").Activate
i = i + 1
Loop
Summary.Activate
End Sub

Sub read_disp_3(loadstep)
Dim myrange, c

Summary.Activate
Summary.Range("b115").Activate

Cells(115 + loadstep - 1, 1).value = "Load Step " & loadstep
Sheets("disp_ls" & loadstep).Select

myrange = ActiveSheet.Range("b:b")

For Each c In myrange
If c = "VALUE" Then
c.Select 'this appears to be the problem, how would you
select the
'correct cell?
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Select
Selection.Copy
Summary.Activate
Cells(115 + loadstep - 1, 2) = 1
ActiveSheet.Paste
Exit For
End If
Next c

End Sub


Thanks for any help.

Jason