Solved

Matching a cell and column two different workbooks using macros

Posted on 2009-04-13
7
756 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

751 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