Move to next column and open new workbook

[HansHamm]

I just can't seem to get this...I have a folder where the workbook is found...it looks for the files and opens them.
Sub getdata()
Dim MAPReport As Workbook
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path
.SearchSubFolders = False
.Filename = "mp*.htm"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i)
Set wkbk = ActiveWorkbook
No problem so far...

Then I run the following:

Windows("MAPReport").Activate
Dim LastRow As Long
With Worksheets("Summary")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("E38:E" & LastRow)
.Formula = "=IF(INDEX('Market '!C:C,MATCH(Summary!$C38,'Market '!A:A,0))="""",IF(ISNUMBER(MATCH($C38,mp1.htm!$A:$A,0)),INDEX(m1.htm!$E:$E,MATCH(E$2,mp1.htm!$D:$D,0)),""""),""Inactive"")"
.Value = .Value
End With

End With

Again No problem....

But what I really am trying to do is to have the code open the 1st mp*.html it sees run the calculation and place the value into Column E, then start back over with the search and find the next mp*.html it sees run the calculation again and place in the next column...so on and so forth.
The reasoning behind this is simple; the number of mp* files vary from 5 -15 and I don't want to have to add or delete code, defeats the purpose.
I have looked and read but I cannot seem to get this to work.

I really appreciate help on this!

Hans

 

[Fenrirshowl]

You need to include the second set of code within the earlier code and include a parameter to target the desired column.

Something like:

Sub getdata()
Dim MAPReport As Workbook
c = 5 ' initially set to target column E
With Application.FileSearch
    .NewSearch
     .LookIn = ThisWorkbook.Path
    .SearchSubFolders = False
    .Filename = "mp*.htm"
    .MatchTextExactly = True
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute() > 0 Then
    For i = 1 To .FoundFiles.Count
    Workbooks.Open Filename:=.FoundFiles(i)
    Set wkbk = ActiveWorkbook

    Call Record_data(c, wkbk)
    c = c + 1
    Workbooks(wkbk.Name).Activate
    ActiveWorkbook.Close (0) ' 0 denotes close with no save
    Next i
    End If
End With
End Sub

Sub Record_data(colm, wkbkname)
Windows("MAPReport").Activate
Dim LastRow As Long
With Worksheets("Summary")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    With .Range(char(64+colm) & "38:" & char(64+colm) &  LastRow)
    .Formula = "=IF(INDEX('Market '!C:C,MATCH(Summary!$C38,'Market '!A:A,0))="""",IF(ISNUMBER(MATCH($C38," & wkbkname & "!$A:$A,0)),INDEX(" & wkbkname & "!$E:$E,MATCH(E$2," & wkbkname & "!$D:$D,0)),""""),""Inactive"")"
        .Value = .Value
    End With
    
End With
End Sub

Note that if you go past the Z column you will run into difficulties (due to the "AA" and beyond) but there are ways around that too. If 15 is the max number of columns, there's no problem. Alternatively (if all columns to the right of E are blank you can simply add a new column at E and the enter the data there (meaning the first workbook opened will be furthest right).

Fen

 

[HansHamm]

Fen,


This is GREAT!! But, now I have another issue that I did not think of earlier..
In the formula section I need the If(index'Market'!C:C... to do the following
Start in column C and do the index then move to column D and do the index.

I tried to mimc your code (wkbk etc...) to make this section work and while it runs nothing happens.


Thanks for your help so far!!!



Hans

 

[DirkStruan]

In that case you need to use some RC notation:

R1C2 refers to Row 1 Column 2 => B1

R[-1]C[2] means one row up and two columns across.

Therefore, given you need it linked to the current column the formula is in, i.e.

   .Formula = "=IF(INDEX('Market '!C[-2]:C[-2],MATCH(Summary!$C38,'Market '!

E is column 5, so referring to C is two steps to the left.

D