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 |
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) |