Problems with the IsNa Function in VB

[NewToVB]

I'm using visual studio to work with excel, Can anyone tell me why this isn't
working:

For i = 3 To lastRow Step 1

If
(LCV.WorksheetFunction.IsNA(LCV.WorksheetFunction.VLookup(LCV.Range("A" &
i).Value, oApp.Range("A1:J" & lastRow).Value, 10, False))) Then
LCV.Range("L" & i).Value = 0
Else : LCV.Range("L" & i).Value =
LCV.WorksheetFunction.VLookup(LCV.Range("A" & i).Value, oApp.Range("A1:J" &
lastRow).Value, 10, False)
End If

Instead of returning a zero where there is an N/A it just kicks me out of
the program. Any ideas? Thanks ahead of time!

 

[Bob Phillips]

Not tested as I have no idea what LCV or oAPp is, but you could try this

Dim tmp As Variant

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 IsEmty(tmp) Then
LCV.Range("L" & i).Value = 0
Else
LCV.Range("L" & i).Value = tmp
End If
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Typo alert:

If IsEmty(tmp) Then
should be:
If IsEmpty(tmp) Then

=======
To the OP, I don't know what oApp and LCV are either. But are you sure that you
didn't mix them up?

 

[NewToVB]

Well LCV and oApp are two different excel applications, i tried to use their
workbooks but it wouldn't let me... for the LCV.worksheetfunction.
Vlookup...I wasn't sure which one to put for "application" so I just used
LCV. Also oApp is from the previous week, LCV is from this week, I'm
searching for the values column A of LCV and see if they exist in column A of
oApp and if it exists return column 10 (J) .... Does that make since? Sorry
if its confusing. But based on that does my code still look right? Thanks
so much for the help!!

 

[Dave Peterson]

Is there a reason you don't open the two workbooks in one instance of excel?

 

[NewToVB]

Well, it seemed to give me an error when I was trying that. I'm using excel
2007 and Visual Studio 2005. Everything works fine up until the point where
it can't find the matching value from oApp. I'm about to try the suggestions
you guys gave me, I'll let you know how it turns out!

 

[NewToVB]

It's not recognizing "IsEmpty" as keyword...any ideas?

 

[NewToVB]

Ok, I guess I had to tweek it a little since I'm using VS 2005 but I have
this now and I'm getting all zeros:

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 = 0
Else : LCV.Range("L" & i).Value = tmp
End If

 

[Dave Peterson]

isempty() will work in VBA. I don't speak VS.

Maybe someone else knows.

It's not recognizing "IsEmpty" as keyword...any ideas?

 

[NewToVB]

Ahh I got it to work! Thanks, you're a genius

 

[Bob Phillips]

I said I hadn't tested it <g>



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)