VLookUp Error Handling Needed  
Author Message
workinghardathome





PostPosted: Thu Apr 20 13:41:01 CDT 2006 Top

Excel Programming >> VLookUp Error Handling Needed

I have a Vlookup table.
Iâ??m looping through a column of letters.
The value of cell F2 is inserted into named range â??_Inâ??.
Named range â??_Outâ?? returns what that letter gets converted to.
If there is not a match from the Vlookup, Iâ??m not sure how to handle the
error.
The desired outcome is whatever the value not matching up (varA) should
remain as is.
Sincerely,
Arturo


Sub Convert()
Dim myRange As Range
Dim rO As Integer
Dim coL As Integer
Dim LoopCount_C As Integer
Dim LoopCount_R As Integer
Dim VarA As Variant
Dim VarB As Variant


''' Rows("1:9").Delete Shift:=xlUp
Range("A1").Select
Set myRange = ActiveCell.CurrentRegion
rO = myRange.Rows.Count - 1
coL = myRange.Columns.Count
Range("F2").Select
For LoopCount_C = 1 To coL
For LoopCount_R = 1 To rO
VarA = ActiveCell.Value
Range("_In").Value = VarA
VarB = Range("_Out").Value

'Error Handling Needed.
'If no match is found then
'ActiveCell.Value = VarA

ActiveCell.Value = VarB
ActiveCell.Offset(1, 0).Select
Next
ActiveCell.Offset(-rO, 1).Select
Next

End Sub

Excel14  
 
 
TomOgilvy





PostPosted: Thu Apr 20 13:41:01 CDT 2006 Top

Excel Programming >> VLookUp Error Handling Needed
For LoopCount_R = 1 To rO
VarA = ActiveCell.Value
Range("_In").Value = VarA
VarB = Range("_Out").Value
if not iserror(varB) then
ActiveCell.Value = VarB
End if
ActiveCell.Offset(1, 0).Select
Next

The activecell already has the value of varA, so no action is required if
there is no replacement value.

--
Regards,
Tom Ogilvy





> I have a Vlookup table.
> Iâ??m looping through a column of letters.
> The value of cell F2 is inserted into named range â??_Inâ??.
> Named range â??_Outâ?? returns what that letter gets converted to.
> If there is not a match from the Vlookup, Iâ??m not sure how to handle the
> error.
> The desired outcome is whatever the value not matching up (varA) should
> remain as is.
> Sincerely,
> Arturo
>
>
> Sub Convert()
> Dim myRange As Range
> Dim rO As Integer
> Dim coL As Integer
> Dim LoopCount_C As Integer
> Dim LoopCount_R As Integer
> Dim VarA As Variant
> Dim VarB As Variant
>
>
> ''' Rows("1:9").Delete Shift:=xlUp
> Range("A1").Select
> Set myRange = ActiveCell.CurrentRegion
> rO = myRange.Rows.Count - 1
> coL = myRange.Columns.Count
> Range("F2").Select
> For LoopCount_C = 1 To coL
> For LoopCount_R = 1 To rO
> VarA = ActiveCell.Value
> Range("_In").Value = VarA
> VarB = Range("_Out").Value
>
> 'Error Handling Needed.
> 'If no match is found then
> 'ActiveCell.Value = VarA
>
> ActiveCell.Value = VarB
> ActiveCell.Offset(1, 0).Select
> Next
> ActiveCell.Offset(-rO, 1).Select
> Next
>
> End Sub
 
 
Arturo





PostPosted: Thu Apr 20 13:48:02 CDT 2006 Top

Excel Programming >> VLookUp Error Handling Needed THANK You Tom!



>
> For LoopCount_R = 1 To rO
> VarA = ActiveCell.Value
> Range("_In").Value = VarA
> VarB = Range("_Out").Value
> if not iserror(varB) then
> ActiveCell.Value = VarB
> End if
> ActiveCell.Offset(1, 0).Select
> Next
>
> The activecell already has the value of varA, so no action is required if
> there is no replacement value.
>
> --
> Regards,
> Tom Ogilvy
>
>
>

>
> > I have a Vlookup table.
> > Iâ??m looping through a column of letters.
> > The value of cell F2 is inserted into named range â??_Inâ??.
> > Named range â??_Outâ?? returns what that letter gets converted to.
> > If there is not a match from the Vlookup, Iâ??m not sure how to handle the
> > error.
> > The desired outcome is whatever the value not matching up (varA) should
> > remain as is.
> > Sincerely,
> > Arturo
> >
> >
> > Sub Convert()
> > Dim myRange As Range
> > Dim rO As Integer
> > Dim coL As Integer
> > Dim LoopCount_C As Integer
> > Dim LoopCount_R As Integer
> > Dim VarA As Variant
> > Dim VarB As Variant
> >
> >
> > ''' Rows("1:9").Delete Shift:=xlUp
> > Range("A1").Select
> > Set myRange = ActiveCell.CurrentRegion
> > rO = myRange.Rows.Count - 1
> > coL = myRange.Columns.Count
> > Range("F2").Select
> > For LoopCount_C = 1 To coL
> > For LoopCount_R = 1 To rO
> > VarA = ActiveCell.Value
> > Range("_In").Value = VarA
> > VarB = Range("_Out").Value
> >
> > 'Error Handling Needed.
> > 'If no match is found then
> > 'ActiveCell.Value = VarA
> >
> > ActiveCell.Value = VarB
> > ActiveCell.Offset(1, 0).Select
> > Next
> > ActiveCell.Offset(-rO, 1).Select
> > Next
> >
> > End Sub