Loop Help for Newbie

[teeitup16]

Thanks in advance for the help!

I've a large Excel file which backtests data. The file is setup to be based on 1 fixed cell. Changing that fixed cell sets the date that will be backtested.

I'd like to setup a Loop statement which will cycle through a large number of dates.

The loop statement would bascily

-Place 2 into cell G2
-Run a series of other VBA code
-Place 3 into cell G2
-Run the same VBA code
-Etc.

The code would run until all the dates had been completed. I guessing some how it could count the number of dates in a fixed column?

Thanks again for the help!!

DC

[nst1107]

Welcome to the forum, DC. You know what you're talking about, I'm sure, but I have no idea what you are referring to. Perhaps you could post a sample workbook with personal information stripped?

[teeitup16]

Here is small scale example of how the file looks. You can see how the 1 date cell changes all the information pulled.

I've written the VBA code to work with and copy the results into a different file which I run a pivot on, but I'm on sure as to how to loop through all the required dates.

The loop would
-place a 2 in cell G6
-Copy the results "F2-H4" in a different file
-place a 3 in cell G6
-Repeat copy of results etc.

In the real file this happens over a 1000 times and pulls more than a 1000 examples each time. I combine "like data" in VBA (I've already completed) and place it in a large data dump to work in a pivot table.

Thanks again for the help!!

DC

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim ws As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim NextRow As Long
Dim NumRows As Long
Dim i As Long, j As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set ws = Worksheets("Sheet2")
With Worksheets("Sheet1")

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
ws.Range("A11").Value = Array("Date", "Product 1", "Product 2", "Count")
NextRow = 1
For i = 2 To LastCol - 1

For j = i + 1 To LastCol

NextRow = NextRow + 1
ws.Cells(NextRow, "B").Value = .Cells(1, i).Value
ws.Cells(NextRow, "C").Value = .Cells(1, j).Value
Next j
Next i
NumRows = NextRow - 1

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
NextRow = 2
For i = 2 To LastRow

ws.Cells(NextRow, "A").Resize(NumRows).Value = .Cells(i, "A").Value
For j = 1 To NumRows

ws.Cells(NextRow + j - 1, "D").Value = _
.Cells(i, Application.Match(ws.Cells(j + 1, "B").Value, .Rows(1), 0)).Value _
+ .Cells(i, Application.Match(ws.Cells(j + 1, "C").Value, .Rows(1), 0)).Value
Next j

NextRow = NextRow + NumRows
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[teeitup16]

Oh "Distinguished Lord of VBAX" if only I was half that skilled my file would work that perfectly.

I've attachted a better example of what I'm trying to acomplish.

Here is the code I currrently have.

You can see that everything is the same with the exception of the second line "ActiveCell.FormulaR1C1 = "2" which becomes 3,4, etc.

What I'm looking for is a way to write this code only once and have it loop adding 1 to cell B1 each time.

Range("B1").Select
ActiveCell.FormulaR1C1 = "2"
Range("I5:K7").Select
Selection.Copy
Sheets("Pivot Data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Rows("1:3").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Daily Calc").Select
Range("B1").Select



ActiveCell.FormulaR1C1 = "3"
Range("I5:K7").Select
Selection.Copy
Sheets("Pivot Data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Daily Calc").Select
Range("B1").Select


ActiveCell.FormulaR1C1 = "4"
Range("I5:K7").Select
Selection.Copy
Sheets("Pivot Data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B14").Select
Sheets("Daily Calc").Select
Range("B1").Select


End Sub

[Bob Phillips]

I am sorry, and I don't wish to be rude, but I have absolutely no interest in working through your code.

You gave an example, I gave a solution. You now tell me that the layout is not actually like the original layout that you gave.

If you can explain to me what the differences are, what you actually want, in terms I understand, I will take a look.

[teeitup]

Thanks for the help, I'll make the first suggestion work.

DC