| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with the IsNa Function in VB
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
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) "NewToVB" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
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? Bob Phillips wrote: 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) "NewToVB" wrote in message ... 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! -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
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" wrote: 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? Bob Phillips wrote: 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) "NewToVB" wrote in message ... 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! -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
Is there a reason you don't open the two workbooks in one instance of excel?
NewToVB wrote: 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" wrote: 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? Bob Phillips wrote: 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) "NewToVB" wrote in message ... 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! -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
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! "Dave Peterson" wrote: Is there a reason you don't open the two workbooks in one instance of excel? NewToVB wrote: 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" wrote: 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? Bob Phillips wrote: 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) "NewToVB" wrote in message ... 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! -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
It's not recognizing "IsEmpty" as keyword...any ideas?
"Dave Peterson" wrote: Is there a reason you don't open the two workbooks in one instance of excel? NewToVB wrote: 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" wrote: 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? Bob Phillips wrote: 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) "NewToVB" wrote in message ... 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! -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
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 "Bob Phillips" wrote: 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) "NewToVB" wrote in message ... 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! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
isempty() will work in VBA. I don't speak VS.
Maybe someone else knows. NewToVB wrote: It's not recognizing "IsEmpty" as keyword...any ideas? "Dave Peterson" wrote: Is there a reason you don't open the two workbooks in one instance of excel? NewToVB wrote: 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" wrote: 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? Bob Phillips wrote: 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) "NewToVB" wrote in message ... 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
Ahh I got it to work! Thanks, you're a genius :)
"NewToVB" wrote: 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 "Bob Phillips" wrote: 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) "NewToVB" wrote in message ... 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! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with the IsNa Function in VB
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) "Dave Peterson" wrote in message ... 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? Bob Phillips wrote: 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) "NewToVB" wrote in message ... 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! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ISNA Function | Excel Worksheet Functions | |||
| IF?ISNA?MATCH? Function? | Excel Discussion (Misc queries) | |||
| Vlookup - ISNA function | Excel Discussion (Misc queries) | |||
| ISNA function | Excel Discussion (Misc queries) | |||
| example if IF(ISNA()) function | Excel Worksheet Functions | |||
Linear Mode
