last try - do until range
 

I am trying to do a loop and i get a message box that says loop without do.
xrow = 5
xcolumn = AD
Cxrow2 = 1
Range("AD5") = ("Jan-06")
Range("AE5") = ("Feb-06")
Range("AF5") = ("Mar-06")

Do Until Range("xcolumn" & 6) = ""
Sheets("Reporting month").Select
If Range("L1") = "Jan-06" Then

Range("AD6:AD44").Select
Selection.Copy
Sheets("Data").Select
Range("AD6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

If Range("AE" & xrow) = "Feb-06" Then
Range("AE6:AE44").Select
Selection.Copy
Sheets("Data").Select
Range("AE6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
If Range("AF" & xrow) = "Mar-06" Then
Range("AF6:AF44").Select
Selection.Copy
Sheets("Data").Select
Range("AF6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
xcolumn = xcolumn + 1
On Error Resume Next
ActiveSheet.Next.Select
If Err.Number < 0 Then Exit Do
On Error GoTo 0
Loop

End Sub

I want to do is copy a column from a sheet and paste the data to an empty
column on another sheet based on month and year. I want this to happen untill
there is no more data for that month.
Then next month I want it to take Feb 06 coulmn and do the same thing, and so
on for the following month. I don’t want the previous months overwritten in
the template.

Below is an example of my data
Thank You in advance.
Nadia

moving left to right
Column AD
Inbound Nov-05 Dec-05 Jan-06 Feb-06
Oct-03 0 0 0
Nov-03 3 3 3
Dec-03 9 8 8
Jan-04 62 59 59
Feb-04 132 127 127
Mar-04 221 212 212
Apr-04 180 167 167

last try - do until range
 

If I am not explaining this to well please let me know...I really need help.
I have a worksheet with 3 tabs, I am copying from the calculation tab
(contains formulas), and trying to paste to the Data tab. The date condition
I was trying to link to the reporting month which contains Dec-05 thru Dec-07
as a list which list box is linked to and so are my formulas in the
calculation tab. I am not sure that this is the best way to do it...I just
need to copy the data based on the month I am reporting on.

thanks again for all your help in advance.

NadiaR wrote:
I am trying to do a loop and i get a message box that says loop without do.
xrow = 5
xcolumn = AD
Cxrow2 = 1
Range("AD5") = ("Jan-06")
Range("AE5") = ("Feb-06")
Range("AF5") = ("Mar-06")

Do Until Range("xcolumn" & 6) = ""
Sheets("Reporting month").Select
If Range("L1") = "Jan-06" Then

Range("AD6:AD44").Select
Selection.Copy
Sheets("Data").Select
Range("AD6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

If Range("AE" & xrow) = "Feb-06" Then
Range("AE6:AE44").Select
Selection.Copy
Sheets("Data").Select
Range("AE6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
If Range("AF" & xrow) = "Mar-06" Then
Range("AF6:AF44").Select
Selection.Copy
Sheets("Data").Select
Range("AF6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
xcolumn = xcolumn + 1
On Error Resume Next
ActiveSheet.Next.Select
If Err.Number < 0 Then Exit Do
On Error GoTo 0
Loop

End Sub

I want to do is copy a column from a sheet and paste the data to an empty
column on another sheet based on month and year. I want this to happen untill
there is no more data for that month.
Then next month I want it to take Feb 06 coulmn and do the same thing, and so
on for the following month. I don’t want the previous months overwritten in
the template.

Below is an example of my data
Thank You in advance.
Nadia

moving left to right
Column AD
Inbound Nov-05 Dec-05 Jan-06 Feb-06
Oct-03 0 0 0
Nov-03 3 3 3
Dec-03 9 8 8
Jan-04 62 59 59
Feb-04 132 127 127
Mar-04 221 212 212
Apr-04 180 167 167

last try - do until range
 

I am trying to do a loop and i get a message box that says loop without do.

If you take the meat out of your macro and just look at the structu

Do Until Range("xcolumn" & 6) = ""
If Range("L1") = "Jan-06" Then



If Range("AE" & xrow) = "Feb-06" Then

End If


If Range("AF" & xrow) = "Mar-06" Then

Loop

End Sub

you see you have two IF statements that are never terminated with End IF.
Since you loop statement appears in the "middle" of an If statement with no
Do within the IF statement, you get the error.

--
Regards,
Tom Ogilvy


"NadiaR" wrote:

I am trying to do a loop and i get a message box that says loop without do.
xrow = 5
xcolumn = AD
Cxrow2 = 1
Range("AD5") = ("Jan-06")
Range("AE5") = ("Feb-06")
Range("AF5") = ("Mar-06")

Do Until Range("xcolumn" & 6) = ""
Sheets("Reporting month").Select
If Range("L1") = "Jan-06" Then

Range("AD6:AD44").Select
Selection.Copy
Sheets("Data").Select
Range("AD6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

If Range("AE" & xrow) = "Feb-06" Then
Range("AE6:AE44").Select
Selection.Copy
Sheets("Data").Select
Range("AE6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
If Range("AF" & xrow) = "Mar-06" Then
Range("AF6:AF44").Select
Selection.Copy
Sheets("Data").Select
Range("AF6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
xcolumn = xcolumn + 1
On Error Resume Next
ActiveSheet.Next.Select
If Err.Number < 0 Then Exit Do
On Error GoTo 0
Loop

End Sub

I want to do is copy a column from a sheet and paste the data to an empty
column on another sheet based on month and year. I want this to happen untill
there is no more data for that month.
Then next month I want it to take Feb 06 coulmn and do the same thing, and so
on for the following month. I dont want the previous months overwritten in
the template.

Below is an example of my data
Thank You in advance.
Nadia

moving left to right
Column AD
Inbound Nov-05 Dec-05 Jan-06 Feb-06
Oct-03 0 0 0
Nov-03 3 3 3
Dec-03 9 8 8
Jan-04 62 59 59
Feb-04 132 127 127
Mar-04 221 212 212
Apr-04 180 167 167

last try - do until range
 

I am really new to VB, what do I need to do to correct this..thanks!

Tom Ogilvy wrote:
I am trying to do a loop and i get a message box that says loop without do.

If you take the meat out of your macro and just look at the structu

Do Until Range("xcolumn" & 6) = ""
If Range("L1") = "Jan-06" Then



If Range("AE" & xrow) = "Feb-06" Then

End If

If Range("AF" & xrow) = "Mar-06" Then

Loop

End Sub

you see you have two IF statements that are never terminated with End IF.
Since you loop statement appears in the "middle" of an If statement with no
Do within the IF statement, you get the error.

I am trying to do a loop and i get a message box that says loop without do.
xrow = 5
[quoted text clipped - 63 lines]
Mar-04 221 212 212
Apr-04 180 167 167

last try - do until range
 

each IF has to be terminated with an end if

If condition then
' things to do if true

End if

so just make sure you have matching endif "blocked" around your instructions
that you want to execute.

You do have what we call a single line IF statement and that is fine

If condition then Exit Do

that one is fine by itself since the action to take is on the same line.

--
Regards,
Tom Ogilvy



"NadiaR" wrote:

I am really new to VB, what do I need to do to correct this..thanks!

Tom Ogilvy wrote:
I am trying to do a loop and i get a message box that says loop without do.

If you take the meat out of your macro and just look at the structu

Do Until Range("xcolumn" & 6) = ""
If Range("L1") = "Jan-06" Then



If Range("AE" & xrow) = "Feb-06" Then

End If

If Range("AF" & xrow) = "Mar-06" Then

Loop

End Sub

you see you have two IF statements that are never terminated with End IF.
Since you loop statement appears in the "middle" of an If statement with no
Do within the IF statement, you get the error.

I am trying to do a loop and i get a message box that says loop without do.
xrow = 5
[quoted text clipped - 63 lines]
Mar-04 221 212 212
Apr-04 180 167 167