Loop macro to copy user input range and paste to user input range in unknown open workbook

Question

I am very new to VBA. I have, for a few weeks now, been trying to develop a code that will mimic the vlookup and hlookup function in Excel.

I am building a macro that copies and pastes data from one workbook into another.

The data will have reference values in a left hand side column of both the source workbook and the destination workbook.

The reference values in the destination workbook will be in a different order to the reference values in the source workbook.

The data to be copied will be located 4 columns away from the reference values. The data (along with the reference values) can be thousands of rows and hundreds of columns long.

The destination workbook and the workbook where the source data is located will both be open.

The user will specify the location of the reference values in both the source workbook and the destination workbook.

The user will also specify the range of data to be copied

All of the data will be copied.

Here is a sample file i have been working on. My actual data will be far greater than this. The data is copied from here: Original Workbook

The data is then pasted into this workbook. The destination workbook will look similar but you can see the reference data is in a different order: Destination Workbook

Also, after a successful loop in the same sheet (where the paste range is the same sheet in same workbook) I get this error: I also get “Runtime error 91. Object variable or with block variable not set.” This is what I got so far:

> Sub copyv5input()
>  
> Dim wsSrc As Worksheet Dim wbSrc As Workbook Dim wsTgt As Worksheet
> Dim wbTgt As Workbook Dim vRng1 As Range Dim vNo As Range Dim rNum As
> Integer Dim vRef1 As Range Dim vRng2 As Range Dim vDest1 As Variant
> Dim vDest2 As Variant Dim vDest3 As Range Dim cNum As Integer Dim
> cNum2 As String Dim vNew2 As Range
> 
> rNum = 1 
> cNum = 1 
>     Set vRng1 = Application.InputBox("Select the range of reference data:", Type:=8)    '1
>     Set vRef1 = vRng1.Cells(rNum, cNum)     '1
>     
>     
>     
>     Set vRng2 = Application.InputBox("Select the reference data range for destination:", Type:=8)   '2
>      
>         
>    
>     Set vDest1 = vRng2.Find(what:=vRef1)    '2
>     Set vDest2 = Range(vDest1.Address)      '2
>     Set vDest3 = vDest2.Offset(0, 1).Resize(, 4)    '2
> 
> Do While vRef1 <> ""
> 
> Set vNo = vRef1.Offset(0, 4).Resize(, 4)    '1
>          
>          If vRef1 = vDest1 Then
>         
>             vNo.copy Destination:=vDest3
>         
>         
>          End If
>     
>     rNum = rNum + 1
>         
>             Set vRef1 = vRng1.Cells(rNum, cNum)
>             Set vDest1 = vRng2.Find(what:=vRef1)
>             Set vDest2 = Range(vDest1.Address)      '2
>             Set vDest3 = vDest2.Offset(0, 1).Resize(, 4)
>      Loop
>        
> 
> End Sub

Thanks in advance!

Answer 1

Hi welcome to stack overflow, Im afraid your question is a little difficult to follow (or maybe its just me). Hopefully im on the right track but i think you want to

1. select a list of values you want to lookup
2. select a range with those values in and additional data in columns that you want to return
3. add the looked up data column to the reference list

You could use the Application.WorksheetFunction property for vlookup or the way i would do is to loop through every value looking for a match, then return the value on the same row but on another column. This can be a little slow for long data lists but its simple and will work

 Sub copyv5input2()


 Dim vRng1 As Range

 Dim rNum As Integer
 Dim rNum2 As Integer
 Dim vRef1 As Range
 Dim vRng2 As Range
 Dim cNum As Integer
 Dim lookupV As String
 Dim foundR As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Set vRng1 = Application.InputBox("Select the range of reference data:", Type:=8)
Set vRng2 = Application.InputBox("Select the reference data range for destination:", Type:=8)   '2
cNum = Application.InputBox("Select the column number you want to return from reference data:")


For rNum = 1 To vRng1.Rows.Count

    lookupV = vRng1.Cells(rNum, 1).Value
    For rNum2 = 1 To vRng2.Rows.Count
        If vRng2.Cells(rNum2, 1) = lookupV Then
            vRng1.Cells(rNum, 1).Offset(0, 1) = vRng2.Cells(rNum2, cNum).Value
            foundR = foundR + 1
            GoTo 10
        End If
    Next rNum2
10
Next rNum

With Application
    .ScreenUpdating = true
    .Calculation = xlCalculationAutomatic

End With
MsgBox "complete, " & foundR & " values returned", vbInformation, "auto lookup"

 End Sub