How would I add a loop for this?

[hansjhamm]

I had worked on this earlier and had to put it aside; now I have to
come back to it...
The code below is what I am doing currently. The "Open Filename...c1"
is only one of many workbooks that would be opened. I name these
c1,c2,c3 etc...Example this time I may only have 3 "C's". Next time it
could be 7 "C's".
What I want to do is this, once the code below runs and is completed,
then go and find the next "C*.htm in the folder and do this code again,
but place the data in the next adjacent column, in this case column E.

Last, the sumproduct works correctly and it runs thru about 400 rows
filling in data. It takes about 45 seconds at this time. Is there a VBA
code that would do this faster?


Thanks,

Hans

Sub getdata()
Dim ColumnTest As Workbook

Workbooks.Open Filename:=ThisWorkbook.Path & "\c1.htm"
Windows("C1.htm").Activate

Range("A9").Select
Selection.Copy
Windows("Column Test.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Font
.Name = "Tahoma"
.Size = 8
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
End With
End With

Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("D6" & LastRow)
.Formula =
"=SUMPRODUCT((c1.htm!$A$1:$A$65000=$C6)*(c1.htm!$E$1:$E$65000=""yes""))-SUMPRODUCT((c1.htm!$A$1:$A$65000=$C6)*(c1.htm!$D$1:$D$65000=""Would
you like to add any comments?"")*(c1.htm!$E$1:$E$65000=""yes""))"
.Value = .Value
End With

End With


End Sub

 

[Dave O]

For the first question, check out the VBA function called DIR, which
allows you to refer to a specific directory and run through all or just
a few of the files it finds there. For the second, you might enter a
line at the beginning of the code that sets calculation to manual, and
another line at the end of the code that sets calculation back to
automatic. This will speed prevent Excel from calculating everytime a
SUMPRODUCT formula is generated by your code.

 

[Dave O]

Here's an example of using DIR in a loop:

FileName = Dir("c*.xls")
Do While FileName <> ""
'your code and acitivities go here
FileName = Dir
Loop

'Set calc to manual
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

'Set calc to auto
With Application
.Calculation = xlAutomantic
.MaxChange = 0.001
End With

 

[hansjhamm]

Dave,
In meetings last week, this is the first I have been able to get back
to this; What I was using before was/is this line:
Dim MapReportLite As Workbook
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path
.SearchSubFolders = False
.Filename = "m*.htm"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i)
Set wkbk = ActiveWorkbook

Then I follow it up with this to extract the data I am looking for:

Windows("MAP Report Lite").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,m1.htm!$A:$A,0)),INDEX(m1.htm!$E:$E,MATCH(E$2,m1.htm!$D:$D,0)),""""),""Inactive"")"
.Value = .Value
End With

End With

This is where I am TOTALLY lost, Once the Sheet has been opened and the
data extracted. How in the world do I tell Excel, now go to the next
column (in this case column F) and do the same thing for the next sheet
(in this case M2.htm)?
Basically, keep doing the same thing until all M* sheets have been
opened in the folder and the data extracted...

Am I making any sense of this?

Thanks,

Hans