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

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

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