| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catch error
Hi,
I'm trying to catch if the value is not there but am unsuccessful. What have I missed. The IsError does not catch the error and so it errors on the TMRnowLMR line when it does not match. Set rngMatch = Sheets(ShName).Range(StartCol & StartRow & ":" & _ StartCol & EndRow) For iCtr = StartRow To EndRow Debug.Print "Blocked iCtr " & iCtr If (IsError(Application.Match(Sheets(ShName).Range(St artCol & iCtr), _ rngMatch, 0))) Then Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" GoTo Continue1 End If TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol & _ iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"), 4, False) Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR Continue1: Next -- Thanks for your help. Karen53 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Catch error
Never mind. I've got it.
If TrafficType = "Blocked2" Then For iCtr = StartRow To EndRow Debug.Print "Blocked iCtr " & iCtr TMRnowLMR = 0 On Error Resume Next TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol & iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"), 4, False) If TMRnowLMR 0 Then Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR Else Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" End If Next End If -- Thanks for your help. Karen53 "Karen53" wrote: Hi, I'm trying to catch if the value is not there but am unsuccessful. What have I missed. The IsError does not catch the error and so it errors on the TMRnowLMR line when it does not match. Set rngMatch = Sheets(ShName).Range(StartCol & StartRow & ":" & _ StartCol & EndRow) For iCtr = StartRow To EndRow Debug.Print "Blocked iCtr " & iCtr If (IsError(Application.Match(Sheets(ShName).Range(St artCol & iCtr), _ rngMatch, 0))) Then Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" GoTo Continue1 End If TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol & _ iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"), 4, False) Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR Continue1: Next -- Thanks for your help. Karen53 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Catch error
It is hard to help with the little info you have supplied.
Are StartCol and StartRow both numeric variables, because that won't work. What problems are you getting? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, I'm trying to catch if the value is not there but am unsuccessful. What have I missed. The IsError does not catch the error and so it errors on the TMRnowLMR line when it does not match. Set rngMatch = Sheets(ShName).Range(StartCol & StartRow & ":" & _ StartCol & EndRow) For iCtr = StartRow To EndRow Debug.Print "Blocked iCtr " & iCtr If (IsError(Application.Match(Sheets(ShName).Range(St artCol & iCtr), _ rngMatch, 0))) Then Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" GoTo Continue1 End If TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol & _ iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"), 4, False) Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR Continue1: Next -- Thanks for your help. Karen53 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Catch error
Hi Bob,
No, one variable is a string for the column and one is Long for the row number. The problem is it doesn't catch the error and errors out on the TMRnowLMR = line instead, because there is no match on that ictr. Those that match before the error process ok because they match. -- Thanks for your help. Karen53 "Bob Phillips" wrote: It is hard to help with the little info you have supplied. Are StartCol and StartRow both numeric variables, because that won't work. What problems are you getting? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, I'm trying to catch if the value is not there but am unsuccessful. What have I missed. The IsError does not catch the error and so it errors on the TMRnowLMR line when it does not match. Set rngMatch = Sheets(ShName).Range(StartCol & StartRow & ":" & _ StartCol & EndRow) For iCtr = StartRow To EndRow Debug.Print "Blocked iCtr " & iCtr If (IsError(Application.Match(Sheets(ShName).Range(St artCol & iCtr), _ rngMatch, 0))) Then Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" GoTo Continue1 End If TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol & _ iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"), 4, False) Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR Continue1: Next -- Thanks for your help. Karen53 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Catch error
If you declare TMRnowLMR as a variant, you can drop the "On error" stuff:
Dim TMRnowLMR as variant 'could be an error ... If TrafficType = "Blocked2" Then For iCtr = StartRow To EndRow Debug.Print "Blocked iCtr " & iCtr TMRnowLMR = Application.VLookup( _ Sheets(ShName).Range(StartCol & iCtr).Value, _ Sheets(ShName).Range("WebBlockedTMR"), 4, False) if iserror(tmrnowlmr) then 'no match Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" else Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR End If Next End If ===== You could check for a number, too: If isnumeric(TMRnowLMR) Then Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR Else Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" End If Karen53 wrote: Hi Bob, No, one variable is a string for the column and one is Long for the row number. The problem is it doesn't catch the error and errors out on the TMRnowLMR = line instead, because there is no match on that ictr. Those that match before the error process ok because they match. -- Thanks for your help. Karen53 "Bob Phillips" wrote: It is hard to help with the little info you have supplied. Are StartCol and StartRow both numeric variables, because that won't work. What problems are you getting? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, I'm trying to catch if the value is not there but am unsuccessful. What have I missed. The IsError does not catch the error and so it errors on the TMRnowLMR line when it does not match. Set rngMatch = Sheets(ShName).Range(StartCol & StartRow & ":" & _ StartCol & EndRow) For iCtr = StartRow To EndRow Debug.Print "Blocked iCtr " & iCtr If (IsError(Application.Match(Sheets(ShName).Range(St artCol & iCtr), _ rngMatch, 0))) Then Sheets(ShName).Range(LMRCol & iCtr).Value = "NA" GoTo Continue1 End If TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol & _ iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"), 4, False) Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR Continue1: Next -- Thanks for your help. Karen53 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CATCH-22 won't let me save the file | Excel Programming | |||
| catch event | Excel Programming | |||
| How to catch Run-time error '91' | Excel Programming | |||
| Catch Error in macro | Excel Programming | |||
| Catch-22 with Error 59 | Excel Programming | |||
Linear Mode
