Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
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) "Dthmtlgod" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
Try this
Dim x As Rang x = Worksheets("Sheet2").Columns(2).Find(Left(Range("S heet1!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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
Dim x As Rang
Set x = Worksheets("Sheet2").Columns(2).Find... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
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 "chris: Put 'Set' in front of 'x'" wrote in message ... Dim x As Range Set x = Worksheets("Sheet2").Columns(2).Find... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
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 -- Regards, Tom Ogilvy "Dthmtlgod" wrote in message ... 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 "chris: Put 'Set' in front of 'x'" wrote in message ... Dim x As Range Set x = Worksheets("Sheet2").Columns(2).Find... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro Lookup | Excel Worksheet Functions | |||
lookup macro | Excel Discussion (Misc queries) | |||
Lookup macro | Excel Discussion (Misc queries) | |||
lookup macro?? | Excel Worksheet Functions | |||
Macro lookup | Excel Discussion (Misc queries) |