Solved

Matching a cell and column two different workbooks using macros

Posted on 2009-04-13
7
740 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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