vlookup in vba

**kstv** · 07-21-2011, 03:18 PM

I keep getting "Error 2042". See code snip bellow:

Function Lookup(ByVal search As String, ByVal col As Integer)
On Error GoTo err_catch
Dim varZip As Variant
 
Windows(afn).Activate
varZip = Application.Vlookup(search, Range("A1:G42085"), col, 0)
 
If IsError(varZip) Then
    Lookup = ""
Else
    Lookup = varZip
End If
exit_catch:
    Exit Function
err_catch:
    MsgBox Err.Description
    Call ErrorLog("LookUP - " + Err.Description)
    Resume exit_catch
End Function

Not sure why I keep getting Error 2042. Please advise.

Thanks,
Stephen

**TMS** · 07-21-2011, 03:20 PM

Welcome to the forum. Please take a few moments to read the forum rules and enclose your code snip in CODE tags ... before anyone can offer advice or assistance.

Regards

**TMS** · 07-21-2011, 03:27 PM

Just as a quick answer ... in the expectation that you will add code tags.

It's not a good idea to use Excel and/or VBA function names as variables, subroutines or functions.

LOOKUP is a standard Excel function so it might give VBA a headache.

Regards

**kstv** · 07-21-2011, 03:32 PM

Thanks. I changed the function name to FindZipCode. I still get the same error.

**TMS** · 07-21-2011, 03:42 PM

Please post a sample workbook with some typical data with the code in context. I'd like to see an example of how you are using this function.

Regards

**kstv** · 07-21-2011, 06:07 PM

see attached sample code.

I have two files. Main file and the validator file. I cannot place any macros in the main file so I need to interact with the main file using another excel file.

All I need to do is post the city, st, and county information in the main file. The main file contains 47,000 rows. I tried using a DO WHILE look and it took too long to complete. The VLOOKUP function is much more robust but it is not cooperating with what I need to do.

Please advise.

Thanks,

Stephen
New Text Document.txt

**tigertiger** · 07-21-2011, 06:24 PM

Why don't you post excel file and say what you need and expected result?

because reading the text file is not easy to understand what your need is

**kstv** · 07-21-2011, 07:06 PM

Sorry about that.

Please see attached.

project_file.xlsx
zip_code_validator.xlsm

**kstv** · 07-21-2011, 07:07 PM

both file needs to by opened and ctrl+g from zip_code_validator to launch the form.

Thanks again.

stephen

**tigertiger** · 07-21-2011, 08:17 PM

Originally Posted by kstv

both file needs to by opened and ctrl+g from zip_code_validator to launch the form.
stephen

It is confusing for me to read your long code

therefore, I just make a sample sub (see in module 2) that makes the same result as your need, I hope you can understand what it run and correct it appropriated to your problem,

see the sub in module 2 from attached file,

Sub sample()
    Dim WbSource As Workbook, WbDestination As Workbook
    Dim WsSource As Worksheet, WsDestination As Worksheet
    Dim RgnSource As Range, RgnDestination As Range
    Dim ColSearch As Long, ColGet As Long
    
    Set WbSource = Workbooks("zip_code_validator.xlsm") 'ActiveWorkbook
    Set WsSource = WbSource.Sheets("zip")
    Set RgnSource = WsSource.Range("A1:M151")
    
    Set WbDestination = Workbooks("project_file.xlsx")
    Set WsDestination = WbDestination.Sheets("Sheet1")
    
    Set RgnDestination = WsDestination.Range("B2:B15")
    
    'SetValue RgnDestination, ColSearch, RgnSource, ColGet
    ColSearch = 6:    ColGet = 2
        
    With RgnDestination
     .FormulaR1C1 = _
        "=VLOOKUP(RC" & ColSearch & ",[" & WbSource.name & "]" & WsSource.name & "!" & _
           RgnSource.Address(, , xlR1C1) & "," & ColGet & ",0)"
      .Value = .Value
    End With
    
End Sub