Solved

Matching a cell and column two different workbooks using macros

Posted on 2009-04-13
7
732 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

14 Experts available now in Live!

Get 1:1 Help Now