Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |