Using Active.Match to do a double match?

[chipsworld]

Hi all... Not sure if this is even a thing but...

I am trying to do what I can only describe as a VLOOKUP and a HLOOKUP and copy data to the appropriate cell from one workbook to another.

Basically, I want to open a window, select the import file, and copy the data to the root workbook based off of the column (VLOOKUP) and row (HLOOKUP) and then add the results to the quantity already there. like an x - y coordinate thing...

This is how far I have gotten thus far... I know it isn't very far, and it is returning an error message already, so any help would be great...

The source and destination workbooks/sheets are basically the same....all row titles are in column A and the date is in row 2 across top. this format can be changed to meet whatever I need it to be.

Private Sub cmd_Import_Click()
Dim wbThisWB As Workbook
Dim wbImportWB As Workbook
Dim strFullPath As String
Dim i, m As Long

Set wbThisWB = ThisWorkbook

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Please select a file to import:"
.Filters.Add "Excel and CSV files", "*.csv; *.xls; *.xls*", 1
.Show
On Error Resume Next 'In case the user has clicked the <Cancel> button
strFullPath = .SelectedItems(1)
If Err.Number <> 0 Then
Exit Sub 'Error has occurred so quit
End If
On Error GoTo 0
End With

Set wbImportWB = Workbooks.Open(strFullPath)
    Application.ScreenUpdating = False



' copy data into master sheet
i = wbThisWB.Sheets("s1 daily metrics").Range("a1").Value
m = Application.Match(i, wbImportWB.Sheets("demob ind").Range("A:A"), 0) [B] 'currently getting error message at this point "Run Time Error 9 - Script out of range"[/B]
'If IsError(m) = True Then
'MsgBox "The Value Is Not Found"
'Exit Sub
'End If
wbImportWB.Activate
Sheets("demob ind").Select
Range("B" & m).Select
Selection.Copy
wbThisWB.Activate
Sheets("s1 daily metrics").Select
Range("E1").PasteSpecial xlPasteSpecialOperationAdd
Application.CutCopyMode = False

    'wbImportWB.Close False 'Close the Import WB without saving any changes. Uncomment when the code goes live.

 

[Fluff]

Change m from Long to Variant.

 

[chipsworld]

Fluff... Now I am getting a run time error 13... on same line of code.

 

[Fluff]

You shouldn't if you have declared m like

Dim m As Variant

then you can use your error checking

m = Application.Match(i, wbImportWB.Sheets("demob ind").Range("A:A"), 0)
If IsError(m) = True Then
MsgBox "The Value Is Not Found"
Exit Sub
End If

 

[chipsworld]

Yes...that is exactly what I did.

Dim wbThisWB    As Workbook
    Dim wbImportWB  As Workbook
    Dim strFullPath As String
    Dim i As Long
    Dim m As Variant

m = Application.Match(i, wbImportWB.Sheets("demob ind").Range("A:A"), 0)

 

[Fluff]

I could understand if you still got a run time error 9 subscript out of range, but not a run time error 13 type mismatch.
If the value cannot be found then m would have a value of error 2042

 

[chipsworld]

Fluff,
Here is a link to the files in my google drive...maybe that will help?

Weekly Reporting pkg.zip

drive.google.com

 

[Fluff]

Thanks for that.
The error is on the previous line & is because A1 is text not a number, you will need to declare i as String

 

[chipsworld]

OK...now I am getting that same "runtime error 9, subscript out of range" on the Application.match line...

I did notice that I gave you the wrong source file though... the data tab in the workbook you have should be named "DEMOB ind" or changed in the code to "DEMOB Units"

The end goal is to make it so that when you select the source file, you would select the tab to import, but just trying to get it to work hardcoded for now.

Still not sure how I would integrate the (HLOOKUP) into this so that I can put the data into the right column. This one is a little bigger than I have gone before, and I have not been able to find any examples to work from online...

 

[Fluff]

now I am getting that same "runtime error 9, subscript out of range"

That means that the tab name in the code & on the actual sheet are not the same. They need to match exactly.