Using Active.Match to do a double match?

chipsworld

Board Regular
Joined
May 23, 2019
Messages
164
Office Version
  1. 365
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.



VBA Code:
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
How would I mark this portion of my headache as resolved?
I'm sure I will be like the terminator... I'll be back!
 
Upvote 0
Your welcome & thanks for the feedback.

We don't mark threads as solved here .
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top