VariantType.Empty error
 

On Error Resume I want the return to be " " ,so I have the code below, but it
doesn't return " " , instead it returns the value of the cell before it.
I've tried IsEmpty() rather than VariantType.Empty, but it doesn't recogize
that in Visual Studio 2005, it says IsEmpty() is no longer supported. Any
ideas on what I can do to get it to return " " instead of the value of tmp
that is stored from the previous loop?

Dim tmp As VariantType

For i = 3 To lastRow Step 1

On Error Resume Next
tmp = LCV.WorksheetFunction.VLookup(LCV.Range("A" & i).Value,
oApp.Range("A1:J" & lastRow).Value, 10, False)
On Error GoTo 0

If tmp = VariantType.Empty Then
LCV.Range("L" & i).Value = " "
Else : LCV.Range("L" & i).Value = tmp
End If

Next i

VariantType.Empty error
 

In VBA, you could use:

tmp = Empty
to set it back to empty.

If that doesn't work in VS, maybe you could do:

Dim tmp as variant' VariantType???
dim myStr as string 'ok in VS????

mystr = "no value found in the search!!!" 'some unique string

For i = 3 To lastRow Step 1

tmp = mystr
On Error Resume Next
tmp = LCV.WorksheetFunction.VLookup(LCV.Range("A" & i).Value, _
oApp.Range("A1:J" & lastRow).Value, 10, False)
On Error GoTo 0

If tmp = mystr then
tmp = " " 'I'd use "", don't include that space character
end if

LCV.Range("L" & i).Value = tmp

Next i


NewToVB wrote:

On Error Resume I want the return to be " " ,so I have the code below, but it
doesn't return " " , instead it returns the value of the cell before it.
I've tried IsEmpty() rather than VariantType.Empty, but it doesn't recogize
that in Visual Studio 2005, it says IsEmpty() is no longer supported. Any
ideas on what I can do to get it to return " " instead of the value of tmp
that is stored from the previous loop?

Dim tmp As VariantType

For i = 3 To lastRow Step 1

On Error Resume Next
tmp = LCV.WorksheetFunction.VLookup(LCV.Range("A" & i).Value,
oApp.Range("A1:J" & lastRow).Value, 10, False)
On Error GoTo 0

If tmp = VariantType.Empty Then
LCV.Range("L" & i).Value = " "
Else : LCV.Range("L" & i).Value = tmp
End If

Next i

--

Dave Peterson

VariantType.Empty error
 

I tried it without the space earlier and it didn't work. But I'll try it
this way and see if it works. Yeah, VariantType is what I have to us in VS.
And there is a property called VariantType.Empty....but it didn't seem to
work like IsEmpty. Thanks for the help. I'll let you know how it goes!

"Dave Peterson" wrote:

In VBA, you could use:

tmp = Empty
to set it back to empty.

If that doesn't work in VS, maybe you could do:

Dim tmp as variant' VariantType???
dim myStr as string 'ok in VS????

mystr = "no value found in the search!!!" 'some unique string

For i = 3 To lastRow Step 1

tmp = mystr
On Error Resume Next
tmp = LCV.WorksheetFunction.VLookup(LCV.Range("A" & i).Value, _
oApp.Range("A1:J" & lastRow).Value, 10, False)
On Error GoTo 0

If tmp = mystr then
tmp = " " 'I'd use "", don't include that space character
end if

LCV.Range("L" & i).Value = tmp

Next i


NewToVB wrote:

On Error Resume I want the return to be " " ,so I have the code below, but it
doesn't return " " , instead it returns the value of the cell before it.
I've tried IsEmpty() rather than VariantType.Empty, but it doesn't recogize
that in Visual Studio 2005, it says IsEmpty() is no longer supported. Any
ideas on what I can do to get it to return " " instead of the value of tmp
that is stored from the previous loop?

Dim tmp As VariantType

For i = 3 To lastRow Step 1

On Error Resume Next
tmp = LCV.WorksheetFunction.VLookup(LCV.Range("A" & i).Value,
oApp.Range("A1:J" & lastRow).Value, 10, False)
On Error GoTo 0

If tmp = VariantType.Empty Then
LCV.Range("L" & i).Value = " "
Else : LCV.Range("L" & i).Value = tmp
End If

Next i

--

Dave Peterson