Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How would I add a loop for this?
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:D" & 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How would I add a loop for this?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How would I add a loop for this?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How would I add a loop for this?
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 Dave O wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop time seems dependent on unrelated workbook - Why? | Excel Worksheet Functions | |||
Loop gone crazy | Excel Discussion (Misc queries) | |||
Do Loop | Excel Discussion (Misc queries) | |||
VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) |