Simple Macro problem. How do I test the entire range

[Frank]

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.Count, 1).End(xlUp)(2)
End If

If Value = "C" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Corporate").Cells(Rows.Count, 1).End(xlUp)(2)
End If

If Value = "D" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Disconnect").Cells(Rows.Count, 1).End(xlUp)
(2)
End If

ActiveWorkbook.Save





End Sub

 

[merjet]

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

 

[Frank]

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.Count, 1).End(xlUp)(2)
End If

If Value = "C" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Corporate").Cells(Rows.Count, 1).End(xlUp)(2)
End If

If Value = "D" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Disconnect").Cells(Rows.Count, 1).End(xlUp)
(2)
End If

Next x

ActiveWorkbook.Save





End Sub

 

[Scoops]

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.Count, 1).End(xlUp)(2)
End If

If Value = "C" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Corporate").Cells(Rows.Count, 1).End(xlUp)(2)
End If

If Value = "D" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Disconnect").Cells(Rows.Count, 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

 

[Frank]

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.

 

[Frank]

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!!

 

[Scoops]

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