Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping question
I have the following code and I am experiencing a Run-time error 1004
Application-defined or object-defined error. The loop executes fine the first time through but fails on the second pass. Any ideas are appreciated. MyVar is valid the second time around too. Public Sub Parse() Dim myArr, myVar As Variant Dim Unit As Variant Set MyRange = Worksheets("Units").Range("A1:B11") myArr = Split(ActiveCell.Value, ",") For Each myVar In myArr ' Iterate through the array 'What do we do with each element? x = Application.WorksheetFunction.VLookup(myVar, MyRange, 2, False) Next End Sub Thanks, Charles |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping question
The vlookup is not going to be happy if it does not find something... Try
this instead... Public Sub Parse() Dim myArr As Variant Dim x As Variant Dim myVar As Variant Dim MyRange As Range Dim rngFound As Range Set MyRange = Worksheets("Sheet1").Range("A1:B11") myArr = Split(ActiveCell.Value, ",") For Each myVar In myArr ' Iterate through the array 'What do we do with each element? Set rngFound = MyRange.Find(myVar).Offset(0, 1) If rngFound Is Not Nothing Then MsgBox rngFound.Value Next end sub "Charles" wrote: I have the following code and I am experiencing a Run-time error 1004 Application-defined or object-defined error. The loop executes fine the first time through but fails on the second pass. Any ideas are appreciated. MyVar is valid the second time around too. Public Sub Parse() Dim myArr, myVar As Variant Dim Unit As Variant Set MyRange = Worksheets("Units").Range("A1:B11") myArr = Split(ActiveCell.Value, ",") For Each myVar In myArr ' Iterate through the array 'What do we do with each element? x = Application.WorksheetFunction.VLookup(myVar, MyRange, 2, False) Next End Sub Thanks, Charles |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping question
sorry just noticed one line is incorrect...
If Not rngFound Is Nothing Then MsgBox rngFound.Value "Jim Thomlinson" wrote: The vlookup is not going to be happy if it does not find something... Try this instead... Public Sub Parse() Dim myArr As Variant Dim x As Variant Dim myVar As Variant Dim MyRange As Range Dim rngFound As Range Set MyRange = Worksheets("Sheet1").Range("A1:B11") myArr = Split(ActiveCell.Value, ",") For Each myVar In myArr ' Iterate through the array 'What do we do with each element? Set rngFound = MyRange.Find(myVar).Offset(0, 1) If rngFound Is Not Nothing Then MsgBox rngFound.Value Next end sub "Charles" wrote: I have the following code and I am experiencing a Run-time error 1004 Application-defined or object-defined error. The loop executes fine the first time through but fails on the second pass. Any ideas are appreciated. MyVar is valid the second time around too. Public Sub Parse() Dim myArr, myVar As Variant Dim Unit As Variant Set MyRange = Worksheets("Units").Range("A1:B11") myArr = Split(ActiveCell.Value, ",") For Each myVar In myArr ' Iterate through the array 'What do we do with each element? x = Application.WorksheetFunction.VLookup(myVar, MyRange, 2, False) Next End Sub Thanks, Charles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping question | Excel Discussion (Misc queries) | |||
Looping Question | New Users to Excel | |||
Looping Question | Excel Discussion (Misc queries) | |||
Looping Question | Excel Programming | |||
Looping Question (I think?) | Excel Programming |