VBA to use VLOOKUP for finding specific data between 2sheets

Hi excel experts,

Good day.

I am working on a some macro but I couldn't make the code works.
I hope you could have some time to analyze it.

Background:
1) I have one workbook with 3worksheets. 1 is main and the 2 others are imported everytime (datax and datay)
2) I need to locate the matches (which was already solved) --> https://www.excelforum.com/excel-pro...ml#post5187039
3) Upon matching I neede to look for the remarks of the matched data and copy it from datax to datay

Here's the code that I looked upto but I can't make it work:

Please Login or Register  to view this content.

I hope to hear from you guys.

Thank you so much.

Good day Leith Ross,

Thanks for responding.

Here's the sample of what I am doing.

Thanks in advance.

Hi !

Can you elaborate 2) & 3) ? …

2) Which columns ?! As filtering is maybe not the way …

3) Copy from Remarks to where in datay ? Should we guess ? No !
Maybe if only your Excel sample has an expected result worksheet …

Hi Marc L,

2) For the background (https://www.excelforum.com/excel-pro...matches-3.html)
a. compare Columns ("DRW No." & "Ref") of datax, to columns of datay ("DWG. NO", "SYM.")
b. then highlight
c. filter the results
-It works perfectly as I wanted.. (column positions of datax and datay are not fixed all the time so the columns are called by their column header names)

3) Is an addition to what i needed..
"Remarks" column is also a header name from datax (with columns position is also not fixed) and will be copied to the first empty columns after the columns with data on datay

Sorry sir if my explanations seems to be unclear.

As always, thank you for your response.

Now it's clear enough but for your actual need filtering is just a waste of time as if I'm not wrong
the logic needs if in datax Remarks cells not blank must be copied to datay if datax DRW No. or Ref. match with datay …

Hi Marc L,

Yes that's it.

Thank you so much.

According to your attachment having few remarks and without any duplicate in sheets matching columns
starting from a not filtered datay sheet a beginner starter demonstration :

PHP Code: 

Sub Demo1()
        Dim H, Rg As Range, V, C%, Rc As Range, Rf As Range
            H = [{"DRW No.","Ref.","Remarks";"DWG. NO","SYM.",0}]
            Set Rg = Sheet2.UsedRange.Rows
            V = Application.Match(H(1, 3), Rg(1), 0)
            If IsNumeric(V) Then H(1, 3) = V Else Beep: Exit Sub
    With Sheet3.UsedRange
        For C = 1 To 2
            V = Application.Match(H(1, C), Rg(1), 0)
            If IsNumeric(V) Then H(1, C) = V - H(1, 3) + 1 Else Beep: Exit Sub
            V = Application.Match(H(2, C), .Rows(1), 0)
            If IsNumeric(V) Then H(2, C) = V Else Beep: Exit Sub
        Next
             H(2, 3) = .Columns(.Columns.Count).Column + 1
             Application.ScreenUpdating = False
        For Each Rc In Rg("3:" & Rg.Count).Columns(H(1, 3)).SpecialCells(xlCellTypeConstants)
             Set Rf = .Columns(H(2, 1)).Find(Rc(1, H(1, 1)).Text, , xlValues, xlWhole)
              If Rf Is Nothing Then Set Rf = .Columns(H(2, 2)).Find(Rc(1, H(1, 2)).Text)
          If Not Rf Is Nothing Then Rc.Copy .Parent.Cells(Rf.Row, H(2, 3))
        Next
    End With
             Set Rf = Nothing:   Set Rg = Nothing
             Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Hi Marc L,

Good evening (here).

Thanks for your help. It works well with the sample. I'll try it again tomorrow.
And inform you with the results.

Thank you so much.

Hi Marc L,

I would like to know if in the code column positions of headers are indicated.
(Sorry I don't understand PHP syntax.)
Coz in the sample sheet column header starts at merged rows 2,3.
How can I adjust the code to fit my needs? what are the adjustable and fixed variables in the code?

Thanks.

Thanks for the rep' added !

A smart worksheet does not need any merged cells …

No fixed column position 'cause you wrote
« column positions of datax and datay are not fixed all the time so the columns are called by their column header names »
like you can see within my VBA demonstration !
This VBA code works with first row used in each worksheet for headers so normally a mod is not necessary
or it's weird to not attach a matching layout sample according to the real workbook !
See the words Columns & Rows in the code …

Yeah that's right.. no fixed columns/rows
The code perfectly works in the sample data.

However, I am a little bit confused why when copied to the actual workbook its not working wherein the only difference are the headers position.
It started on row 3,4 instead of row2,3
Could you explain even part of it
for example: H(2,3) means?
I really wanted to understand the code..
Thank you.

Hi Marc L,

Sorry if I have so many follow up questions..

I also changed the sheets number depending on where the datax and datay is.
aside from Sheet2 and Sheet3.. what other codes indicate sheet number?

Thanks

No row issue with a smart worksheet but in your case …
Posting a different layout workbook sample than the real one means
you are very confident with your VBA skills in order to amend the code ‼

As I yet wrote, the code does not start from the absolute rows position #2 & 3 but on the first used row
as you can read within the code, just see .UsedRange.Rows …

You can add a Find codeline in order to get the header row # …

For each statement once the text cursor on it you can hit F1 key then read its VBA relative help.
Except for Match which is the MATCH worksheet function so the help is on Excel side …

Sheet2 is not the sheet # but the CodeName of a sheet, can be replaced by any valid worksheet reference …
See \1

Could be easier with a named range or if you convert the range as a true Excel table :
no need anymore to find out each header column position like in my demonstration with the H variable array
- to understand see in step by step mode hitting F8 key the variables contents in the VBE Locals window -
so the code should directly start at the For Each codeline and almost all previous codelines would be useless,
no row issue whatever the first row (headers) position #, no …

This has been solved thru this link
https://www.excelforum.com/excel-pro...ml#post5188143

Thanks to all those who sincerely helped.
Such a blessing to everyone.

Hi Marc L,

I just read your reply.. sorry

I am not confident with my VBA skills.
Data are too confidential to be shared in a platform like this.
I hope you understand that.
I can guarantee that the workbooks are closely similar to each other except for the contents and header starting position.

Thank you so much for your help.
I really appreciate your effort for answering and catering all my queries.

I just wanted to make it clear that I am not just here to copy and paste.
I wanted to learn from experts like you so that I too would be able to create something on my own in the future so as not to disturb experts like you more often.

Thanks for your help.
(by the way this --> "!!" offends me)

Thanks for this revert !

'‼" was not to offend, as it is very not difficult to attach a sample workbook respecting the real workbook layout …

Marc L,

Sorry for the misunderstanding.
I'll remember your advise.

I found the difference why it is working in the sample and not in real workbook.
The first two rows in the datax(in violet font).

If you're still interested in helping me solve this using your code.
Coz I still do.

Thanks