Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro problem. How do I test the entire range
In the macro below, I am attempting to test the value of the contents
of an Column (F). Based on that value, the macro should copy the row contents to a new workbook. I suspect I am attempting to use the wrong command as I am a newbie. Compile error is: Statement invalid outside Type block Sub UPDATE_STATUS() ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select Range("F:F") As Range If Value = "P" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Personal").Cells(Rows.Cou nt, 1).End(xlUp)(2) End If If Value = "C" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Corporate").Cells(Rows.Co unt, 1).End(xlUp)(2) End If If Value = "D" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Disconnect").Cells(Rows.C ount, 1).End(xlUp) (2) End If ActiveWorkbook.Save End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro problem. How do I test the entire range
Range("F:F") As Range
is the problem. I can't discern what you want. But: Dim Range("F:F") As Range isn't valid either. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro problem. How do I test the entire range
On May 4, 10:09 am, merjet wrote:
Range("F:F") As Range is the problem. I can't discern what you want. But: Dim Range("F:F") As Range isn't valid either. Hth, Merjet Here is my next attempt: Still problems as a I have a range problem ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select Dim x For x = 1 To 65535 Range("Fx").Select If Value = "P" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Personal").Cells(Rows.Cou nt, 1).End(xlUp)(2) End If If Value = "C" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Corporate").Cells(Rows.Co unt, 1).End(xlUp)(2) End If If Value = "D" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Disconnect").Cells(Rows.C ount, 1).End(xlUp) (2) End If Next x ActiveWorkbook.Save End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro problem. How do I test the entire range
On 4 May, 15:01, Frank wrote:
In the macro below, I am attempting to test the value of the contents of an Column (F). Based on that value, the macro should copy the row contents to a new workbook. I suspect I am attempting to use the wrong command as I am a newbie. Compile error is: Statement invalid outside Type block Sub UPDATE_STATUS() ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select Range("F:F") As Range If Value = "P" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Personal").Cells(Rows.Cou nt, 1).End(xlUp)(2) End If If Value = "C" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Corporate").Cells(Rows.Co unt, 1).End(xlUp)(2) End If If Value = "D" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Disconnect").Cells(Rows.C ount, 1).End(xlUp) (2) End If ActiveWorkbook.Save End Sub Hi Frank To test the range try: Sub TestRange() Dim cell As Range For Each cell In Range("F:F") Select Case cell Case "F" 'DoThis Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub You might want to consider dynamically defining the actual range you're testing - are you really filling 65k+ rows? Regards Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro problem. How do I test the entire range
On May 4, 10:20 am, Scoops wrote:
On 4 May, 15:01, Frank wrote: In the macro below, I am attempting to test the value of the contents of an Column (F). Based on that value, the macro should copy the row contents to a new workbook. I suspect I am attempting to use the wrong command as I am a newbie. Compile error is: Statement invalid outside Type block Sub UPDATE_STATUS() ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select Range("F:F") As Range If Value = "P" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Personal").Cells(Rows.Cou nt, 1).End(xlUp)(2) End If If Value = "C" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Corporate").Cells(Rows.Co unt, 1).End(xlUp)(2) End If If Value = "D" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Disconnect").Cells(Rows.C ount, 1).End(xlUp) (2) End If ActiveWorkbook.Save End Sub Hi Frank To test the range try: Sub TestRange() Dim cell As Range For Each cell In Range("F:F") Select Case cell Case "F" 'DoThis Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub You might want to consider dynamically defining the actual range you're testing - are you really filling 65k+ rows? Regards Steve- Hide quoted text - - Show quoted text - Steve, I dont really need to test the entire worksheet, but the end can change and it may have a discontiguous data set, so I cannot define the precise end. I replaced that function. Seems to work properly. How about one more bit of assistance? Seems my paste statement contains an object defined error now. (it functioned previously). The goal is to paste to the next available row. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro problem. How do I test the entire range
On May 4, 10:29 am, Frank wrote:
On May 4, 10:20 am, Scoops wrote: On 4 May, 15:01, Frank wrote: In the macro below, I am attempting to test the value of the contents of an Column (F). Based on that value, the macro should copy the row contents to a new workbook. I suspect I am attempting to use the wrong command as I am a newbie. Compile error is: Statement invalid outside Type block Sub UPDATE_STATUS() ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select Range("F:F") As Range If Value = "P" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Personal").Cells(Rows.Cou nt, 1).End(xlUp)(2) End If If Value = "C" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Corporate").Cells(Rows.Co unt, 1).End(xlUp)(2) End If If Value = "D" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Disconnect").Cells(Rows.C ount, 1).End(xlUp) (2) End If ActiveWorkbook.Save End Sub Hi Frank To test the range try: Sub TestRange() Dim cell As Range For Each cell In Range("F:F") Select Case cell Case "F" 'DoThis Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub You might want to consider dynamically defining the actual range you're testing - are you really filling 65k+ rows? Regards Steve- Hide quoted text - - Show quoted text - Steve, I dont really need to test the entire worksheet, but the end can change and it may have a discontiguous data set, so I cannot define the precise end. I replaced that function. Seems to work properly. How about one more bit of assistance? Seems my paste statement contains an object defined error now. (it functioned previously). The goal is to paste to the next available row.- Hide quoted text - - Show quoted text - I am going to close this thread and open a new one since this problem was been resolved (just a minor issue remains) THANKS!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro problem. How do I test the entire range
On 4 May, 15:48, Frank wrote:
On May 4, 10:29 am, Frank wrote: On May 4, 10:20 am, Scoops wrote: On 4 May, 15:01, Frank wrote: In the macro below, I am attempting to test the value of the contents of an Column (F). Based on that value, the macro should copy the row contents to a new workbook. I suspect I am attempting to use the wrong command as I am a newbie. Compile error is: Statement invalid outside Type block Sub UPDATE_STATUS() ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select Range("F:F") As Range If Value = "P" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Personal").Cells(Rows.Cou nt, 1).End(xlUp)(2) End If If Value = "C" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Corporate").Cells(Rows.Co unt, 1).End(xlUp)(2) End If If Value = "D" Then Cells(Row, 1).Resize(1, 29).Copy Destination:=Worksheets("Disconnect").Cells(Rows.C ount, 1).End(xlUp) (2) End If ActiveWorkbook.Save End Sub Hi Frank To test the range try: Sub TestRange() Dim cell As Range For Each cell In Range("F:F") Select Case cell Case "F" 'DoThis Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub You might want to consider dynamically defining the actual range you're testing - are you really filling 65k+ rows? Regards Steve- Hide quoted text - - Show quoted text - Steve, I dont really need to test the entire worksheet, but the end can change and it may have a discontiguous data set, so I cannot define the precise end. I replaced that function. Seems to work properly. How about one more bit of assistance? Seems my paste statement contains an object defined error now. (it functioned previously). The goal is to paste to the next available row.- Hide quoted text - - Show quoted text - I am going to close this thread and open a new one since this problem was been resolved (just a minor issue remains) THANKS!!- Hide quoted text - - Show quoted text - Hi Frank Try: Sub TestRange() Dim cell As Range For Each cell In Range("F1", Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F")) Select Case cell Case "F" cell.Resize(1, 29).Copy _ Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub Regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Really simple macro problem - I bet! | New Users to Excel | |||
Run Macro through an entire range | Excel Programming | |||
Simple macro problem | Excel Programming | |||
simple range problem | Excel Discussion (Misc queries) |