Solved

Matching a cell and column two different workbooks using macros

Posted on 2009-04-13
7
736 Views
Last Modified: 2013-11-25

I have a workbook named Indy.xls, another one named Book1.xls, and another one named Customer.xls.  They are all saved in a folder marked C:\Users\Owner\Desktop\Real Indy. I would like cell A1 in Indy.xls to be matched against Column B:B in Customer.xls.  If there is a match in Customer. xls Column B:B to take the Info from Customer. xls Column C and print it in cell A1 of workbook Book1.xls.  If there is no match to take the info from cell A1 Indy.xls and print it in cell A1 of workbook Book1.xls.  This is what I have which does not work whatsoever.  I had it working if the sheets are in the same workbook.
Sub Matcher3()
ChDir "C:\Users\Owner\Desktop\Real Indy"
    Workbooks.Open Filename:="C:\Users\Owner\Desktop\Real Indy\Customer.xls"
If WorksheetFunction.CountIf(Workbooks("Customer.xls").Sheets("Customer").Range("B:B"), Workbooks("Indy.xls").Sheets("Invoice").Range("A1").Value) > 0 Then
Workbooks("Customer.xls").Sheets("Customer").Range("A1").Value = WorksheetFunction.VLookup(Workbooks("Indy.xls").Sheets("Invoice").Range("A1").Value, Workbooks("Customer.xls").Sheets("Customer").Range("B:B"), 1, False)
End If
End Sub
0
Comment
Question by:dave5m
  • 5
  • 2
7 Comments
 
LVL 16

Accepted Solution

by:
RichardSchollar earned 500 total points
ID: 24135557
Hi

You could do this via formula rather than thru code:

=IF(ISNA(MATCH('C:\Users\Owner\Desktop\Real Indy\[Indy.xls]Invoice'!A1,'C:\Users\Owner\Desktop\Real Indy\[Customer.xls]Customer'!$B:$B,0)),'C:\Users\Owner\Desktop\Real Indy\[Indy.xls]Invoice'!A1,VLOOKUP('C:\Users\Owner\Desktop\Real Indy\[Indy.xls]Invoice'!A1,'C:\Users\Owner\Desktop\Real Indy\[Customer.xls]Customer'!$B:$C,2,0))

entered into A1 of Book1.xls.  The formula basically says "if there's no match between Indy and Customer then use A1 in Indy, but if there is a match use the corresponding value from col C in Customer".

Richard
0
 

Author Comment

by:dave5m
ID: 24150397
if i do it with entering the poseted formula of =IF(ISNA(MATCH('C:\Users\Owner\Desktop\Real Indy\[Indy.xls]Invoice'!A1,'C:\Users\Owner\Desktop\Real Indy\[Customer.xls]Customer'!$B:$B,0)),'C:\Users\Owner\Desktop\Real Indy\[Indy.xls]Invoice'!A1,VLOOKUP('C:\Users\Owner\Desktop\Real Indy\[Indy.xls]Invoice'!A1,'C:\Users\Owner\Desktop\Real Indy\[Customer.xls]Customer'!$B:$C,2,0)) in cell a1 of Book1 i can use it but i have a slight problem.  i have this macro:
Sub Invoice_Click()
'
' Invoice_Click Macro
' Macro recorded 3/18/2009 by
'

'
    ActiveSheet.Shapes("Button 2").Select
    Selection.Characters.Text = "Invoice"
    With Selection.Characters(Start:=1, Length:=7).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        Sheets("Invoice").Select
        Range("a1").Select
    ActiveCell.FormulaR1C1 = "PLATE NUMBER"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = InputBox("PLATE NUMBER")
    Range("A3").Select
    Range("A4").Select
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Sheets("Invoice").Select
    ActiveCell.FormulaR1C1 = "YEAR"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = InputBox("Make")
    Range("A6").Select
    Range("A7").Select
where i put the "+" signs, i was hoping to enter the origiginal macro i wanted.  i have modified the formula above to enter "NA" if a match hasn't been found instead of the unfound plate Number and to take the input from workbook "Indy" cell a2 and enter it in cell a1 workbook "Book1".   so using the macro and formula above, what i want to do is to eneter a plate number.  if the plate number is found to have a match with Workbook "Customer" column b to enter the info from "Customer" column c to cell A1 book1 and to open  Book1 and end the macro.  which i can do.  But if there is no match (which i set up to say "NA") i want it to enter the input data from InputBox("PLATE NUMBER") in cell A1 Book1 and than got to next line of macro and enter the input data from InputBox("Make") in cell A2 Book1, and than run a match from about 30 folders saved in folder ChDir "C:\Users\Owner\Desktop\Make" with different makes of vehicles, ex Honda, Nissan, Toyota, Ford,etc.  and open the one that matches. (If there is no match to go to the next line of macro which asks the year) If there is a folder with that make, than i will ask the year,model and engine size and enter that input into cell A3, a4 and a5, of book 1 and run a  match on that to return Oil and air filter numbers from the Make folders which are my database. i think i explained it as simply as possible or if you can put the above formula in a macro i think i can figure it out?!
Any help is tremendously appreciated.
0
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24163033
OK

You can use the ExecuteExcel4Macro method of the Application object to return a result from a closed workbook.  For this you need to know

1. The path of the destination workbook (eg "C:\Users\Owner\Desktop\Real Indy\")
2. The name of the destination workbook (eg "Customer.xls")
3. The sheet name of the destination workbook (eg "Customer")
4. The range you want to target (in R1C1 style notation)

Converting the formula I originally gave you get this:

Dim varTemp As Variant 'variable to hold result

varTemp = Application.ExecuteExcel4Macro("VLOOKUP(" & Range("A1").Value & ",'C:\Users\Owner\Desktop\Real Indy\[Customer.xls]Customer'!C2:C3,2,0)")

If TypeName(varTemp) <> "Error" Then
  Range("B1") = varTemp
Else
  Range("B1").Value = Range("A1").Value
End If




Make sense?

Richard
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:dave5m
ID: 24185710
Huh? i tried to look  up ExecuteExcel4Macro method this weekend but i think i am more lost than when i started?  
0
 

Author Comment

by:dave5m
ID: 24188931
dont sweat it- i took your formula and changed it to =IF(ISNA(MATCH(A2,'C:\Users\Owner\Desktop\Real Indy\[Customer.xls]Customer'!$B:$B,0)),"NA",VLOOKUP(A2,'C:\Users\Owner\Desktop\Real Indy\[Customer.xls]Customer'!$B:$C,2,0))
in cell a3 oif book 1 and thgan deleted the indy folder and than wrote this macro and it works pretty good-i would love to learn the ExecuteExcel4Macro method (and when you look at my macro you might think i'm retarded) but right now i am glad i got this to work.  thank you very much!!
0
 

Author Comment

by:dave5m
ID: 24188951
i'm sorry here's the new macro if your interested:
Sub InvoicetoIndy()
'
' InvoicetoIndy Macro
'

'
    Workbooks.Open Filename:="C:\Users\Owner\Desktop\Real Indy\Book1.xlsx"
 
   
 
    With Selection.Characters(Start:=1, Length:=7).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        Sheets("Sheet1").Select
        Range("a1").Select
    ActiveCell.FormulaR1C1 = "PLATE NUMBER"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = InputBox("PLATE NUMBER")
    Range("A3").Select
    Range("A4").Select
   
    End With
    If Sheets("Sheet1").Range("A3").Value = "NA" Then
       With Selection.Characters(Start:=1, Length:=7).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        Sheets("Sheet1").Select
        Range("a4").Select
    ActiveCell.FormulaR1C1 = "YEAR"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = InputBox("YEAR")
    Do Until ActiveCell > 1960
    If Range("a5").Select < 1960 Then MsgBox ("iNVALID YEAR ENTER CORRECT yEAR") Else
    ActiveCell.FormulaR1C1 = "YEAR"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = InputBox("YEAR")
   Loop
    Range("A6").Select
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "MAKE"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = InputBox("MAKE")
    Range("A9").Select
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "MODEL"
    Range("A11").Select
    ActiveCell.FormulaR1C1 = InputBox("MODEL")
    Range("A12").Select
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "ENGINE SIZE"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = InputBox("ENGINE SIZE")
    Range("A15").Select
   
    End With
    End If

End Sub
0
 

Author Closing Comment

by:dave5m
ID: 31569488
i wrote two more threads i dont know if you can read them but my main problem was that i am very new to this and have no clue, so when you wrote the last comment with ExecuteExcel4Macro i was lost.  i have been working on this for 2 months so when you gave me the formula i knew i was close but both answers were not exact so i did not grade an a but within  a week of constantly tweeking i got what i needed (which is soo much faster than 2 months).  i am very appreciative though.  thgank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
macro for same location path 2 19
how to read all the sheets in excel 12 40
Search for a value in Column? 5 21
Excel 2016 Not Responding Issues 6 28
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now