I use in VBA the following excellent procedure which allows me to perform Index Match based on certain parameters:
Sub DoIndexMatch(Target As Range, matchvalue As Range, comparedvalue As Range, DestinationColumn As String)
Dim c As Range
For Each c In matchvalue
If IsNumeric(Application.Match(c, comparedvalue, 0)) Then
Range(DestinationColumn & c.Row) = Application.WorksheetFunction.Index(Target, Application.WorksheetFunction.Match(c, comparedvalue, 0), 0)
DoIndexMatch Range("ccprojectstable!v2:v" & Rows.Count), Range("E2", Range("E" & Rows.Count).End(xlUp)), Range("ccprojectstable!A2:A" & Rows.Count), "B"
DoIndexMatch Range("accountingtable!F2:F" & Rows.Count), Range("E2", Range("E" & Rows.Count).End(xlUp)), Range("accountingtable!A2:A" & Rows.Count), "O"
I would like to convert this procedure with by using Excel Application
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Applic
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
In order to:
Be able to perform IndexMatch across two Workbooks and not two Worksheets as it is set up in my procedure.
Be able to open the files concerned by the indexmatch with vbscript language : most recent, file name string etc.
Be able to initialize the action outside excel even if we call ExcelApplication it will be a vbs which initialize the action
Be able to log activities
The idea is to:
1-Open Destination Workbook based on a specified directory if it is already open skip this action
2-Open Source Workbook based on a specified directory if it is already open skip this action
Destination and Source Workbooks can be in a different directory.
3- Call the indexmatch action in which I should be able to specified:
: reference Target Range that will be "transferred" to the Destination Workbook that will receive the Index.Match
edRange: compared range that will be used in the destination workbook to be able to transfertSourceWorkbookTar
-SourceWorkbookMatchRange : this Range will be used after the comparison with the DestinationWorkbookCompare
dRange in order to perform the Index Match
: this is the Column that will receive the Target in DestinationWorkbook comming from SourceWorkbookTargetRange
4-Save and close the both files.
If DestinationWorkbooks and SourceWorkbook doesn't exist, exit sub
If DestinationWorkbook cannot be saved, exit sub
Thank you very much for your help.