Link to home
Start Free TrialLog in
Avatar of dave5m
dave5m

asked on

Matching a cell and column two different workbooks using macros


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
ASKER CERTIFIED SOLUTION
Avatar of RichardSchollar
RichardSchollar
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dave5m
dave5m

ASKER

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.
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
Avatar of dave5m

ASKER

Huh? i tried to look  up ExecuteExcel4Macro method this weekend but i think i am more lost than when i started?  
Avatar of dave5m

ASKER

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!!
Avatar of dave5m

ASKER

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
Avatar of dave5m

ASKER

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.