PDA

View Full Version : Solved: Do While



afoehrin
12-09-2008, 11:51 AM
Dears,
I have a little problem to continue my "Do While" conditional.

For example:

I have a rowcounter named "i".

The code is:
i = 8
Do while Cell(i,8) <> ""
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxx
xxxxxxxxxxxxx
i = i + 1
Exit do
Loop

At the end, the macro is not identifying the rowcounter. Can someone please tell me what am I doing wrong?
Many thanks!

Regards,
AJPF

Bob Phillips
12-09-2008, 11:52 AM
You have an Exit Do in there, so it it exits the first iteration.

afoehrin
12-11-2008, 08:38 AM
Good afternoon,

I made some changes but it is still not working. Can someone please check?

i = 8
Do While Cells(i, 5) <> ""
ActiveWindow.ActivateNext
Sheets("DBSR").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Rows("3:3").Select
Selection.Copy
Rows("2:2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.ActivateNext
Cells(i, 5).Select
ActiveCell.Offset(0, -1).Range("A1:J1").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("DB").Select
Range("B2:F2, BS2:BU2").Select
Selection.Copy
Sheets("DBSR").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("S2").Select
ActiveCell.FormulaR1C1 = "=sum(RC[-8]+ RC[-6]+RC[-4]+RC[-2])"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=sum(RC[-7]+ RC[-5]+RC[-3]+RC[-1])"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=sum(RC[-1]+ RC[-2])"
i = i + 1
Loop
Exit Do


many thanks,
AJPF

Bob Phillips
12-11-2008, 08:51 AM
You have still got the Exit Do, but fail to tell us how it doesn't work.

The code can reduce to



Dim i As Long
i = 8
Do While Cells(i, 5) <> ""

With Sheets("DBSR")

.Rows("2:2").Insert Shift:=xlDown
.Rows("3:3").Copy
.Rows("2:2").PasteSpecial Paste:=xlPasteFormats
.Cells(i, 5).Offset(0, -1).Range("A1:J1").Copy
.Range("I2").PasteSpecial Paste:=xlPasteValues
Sheets("DB").Range("B2:F2, BS2:BU2").Copy
.Range("A2").PasteSpecial Paste:=xlPasteValues
End With

With Sheets("DB")
.Range("S2").FormulaR1C1 = "=sum(RC[-8]+ RC[-6]+RC[-4]+RC[-2])"
.Range("T2").FormulaR1C1 = "=sum(RC[-7]+ RC[-5]+RC[-3]+RC[-1])"
.Range("U2").FormulaR1C1 = "=sum(RC[-1]+ RC[-2])"
End With
i = i + 1
Loop


test this and tell us what it does/doesn't do, maybe post an example workbook.

CreganTur
12-11-2008, 01:05 PM
The 'Exit Do' command forces a loop structure to end immediately once it is reached (as long as the Exit Do command is inside a Do...Loop structure). Generally the use of an exit statement is only to be used within some conditional statements within the loop structure. For example:
Dim i As Integer
i = 1
Do Until i > 10
MsgBox i
If i = 5 Then
Exit Do
End If
i = i + 1
Loop


I made some changes but it is still not working
Please define not working. Are you getting an error message? Are you seeing undesired results on your spreadsheet? Please provide specifics so we can point you in the right direction.

afoehrin
12-12-2008, 05:03 AM
Dear Randy,

Many thanks! It worked!

Dear xld,

Thank you for your help too.

Best regards,
AJPF