Lookup Macro

[Dthmtlgod]

I have the following piece of code. It looks to see if a cell is zero

If Range("Sheet1!J" & I).Value = 0 Then

**** This is where I need assistance. If it is zero, then I need to do a
lookup on the value of
left(range("Sheet1!A" & i), 3) in another worksheet (Sheet2, column B). It
is trying to find a match on the first three digits of column A to Sheet2,
column A, and return the lookup value of B to column Sheet1!J.

range("Sheet1!j" & I).value =

*****
End If

I have been trying to get this to work for a couple of hours now, any
assistance is much appreciated.

 

[Dthmtlgod]

Basically,

I am trying to incorporate this into the macro
The range should equal this LOOKUP(LEFT(D5,3),HTC!A2:A200,HTC!B2:B200)

 

[Guest]

Try this
Dim x As Rang
x = Worksheets("Sheet2").Columns(2).Find(Left(Range("Sheet1!A" & i).value, 3), XlLookAt := xlPart
range("Sheet1!j" & I).value =x.value

I'm not sure if your looking for a whole match value or a partial, so your gonna have to make changes as needed to XlLookA

----- Dthmtlgod wrote: ----

I have the following piece of code. It looks to see if a cell is zer

If Range("Sheet1!J" & I).Value = 0 The

**** This is where I need assistance. If it is zero, then I need to do
lookup on the value o
left(range("Sheet1!A" & i), 3) in another worksheet (Sheet2, column B). I
is trying to find a match on the first three digits of column A to Sheet2
column A, and return the lookup value of B to column Sheet1!J

range("Sheet1!j" & I).value

****
End I

I have been trying to get this to work for a couple of hours now, an
assistance is much appreciated

 

[Guest]

Dim x As Rang
Set x = Worksheets("Sheet2").Columns(2).Find...

 

[Dthmtlgod]

Thanks Chris,

I am receiving an error on this.
Run Time Error 448:
Named Argument Not Found

It errors out on the Set R line.

Z = 5
Do While Range("DCI!A" & Z).Value = Date
If Range("DCI!J" & Z).Value = 0 Then
Set R = Worksheets("HTC").Columns(2).Find(Left(Range("DCI!D" & Z).Value, 3),
XlLookAt:=xlWhole)
Range("DCI!J" & Z).Value = R.Value
End If
Z = Z + 1
Loop

 

[Tom Ogilvy]

there is no xlLookAt:= named argument. The argument name is Lookat:=


for you other question:

Dim res as Variant
res = Application.LOOKUP(LEFT(Range("D5"),3), _
Range("HTC!A2:A200"),Range("HTC!B2:B200"))
if not iserror(res) then
msgbox res
else
msgbox "Not found"
End if