PDA

View Full Version : Nested Loops?



lovespar
08-30-2007, 05:58 AM
I am trying to work out a macro that will do the following
Column A will be pasted into Cell $F$2, one value at a time.
Column B will be pasted into Cell $F$3, one value at a time.
Cells F2 and F3 are then input into some formulas to create a value in $R$4. This part is done with formulas not part of the macro.
Then this value $r$4 needs to be pasted into Column C. I hope I have explained this well enough for you to understand. So I think I need Loops within loops so it will take one calculation at a time and place into column C before it goes to the next set of numbers in Column A and B.
Do you have a ny suggestions?
All the data is on one work sheet.

Bob Phillips
08-30-2007, 06:08 AM
Give a couple of examples so we can visualise it.

lovespar
08-30-2007, 06:46 AM
I would like to send you an attachment but can't see the manage attachement button.
When I paste in the worksheet it comes out in text.
A B C D E F R
2 98
3

2 3 98
1 2
3 1

Bob Phillips
08-30-2007, 06:52 AM
That isn't excatly helpful, it just a string of characters.

Spreadsheet layout, and expected results ould be more helpful.

Norie
08-30-2007, 07:01 AM
I don't see why you would need nested loops for what you seem to want.

But then again what you seem to want isn't too clear.:)

If you want to attach a workbook use the Go Advanced button.

lovespar
08-30-2007, 07:55 AM
Thanks for helping me find that Manage Atatchement button. I am new to this site and haven't ever attached anything before.
Ok, On this SS
Column L needs to be pasted into B2, one value at a time
Column M needs to be pasted into B3, one at a time
This causes a caluclation to be entered into H18 (Total)
This calulation is not part of the macro,
Then the resluting number from H18 needs to be pasted into the N column, in the corresponding cell. So L3, and M3 would be the values that produced N3
Thanks for looking at this. I thought the nested loops would help the progression of filling in column N, one value at a time?

Bob Phillips
08-30-2007, 09:22 AM
There are #REF!s everywhere, so H18 isn't calculating anything.

Bob Phillips
08-30-2007, 09:26 AM
But all you need when you tidy up the errors is



Public Sub ProcessData()
Const TEST_COLUMN As String = "L" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 3 To iLastRow
.Range("B2").Value = .Cells(i, TEST_COLUMN).Value
.Range("B3").Value = .Cells(i, TEST_COLUMN).Offset(0, 1).Value
.Cells(i, TEST_COLUMN).Offset(0, 2).Value = .rangee("H18").Value
Next i

End With

End Sub

lovespar
08-30-2007, 10:40 AM
Thanks for the code. I am rotton with counters.:bow: