Error When only 1 row or zero rows of data

[STEVEB]

Hi,

Does anyone have any suggestions for the following:

I have a spreadsheet that looks at data and then sorts the data based on certain text within the cell. The code works great when there are several rows of data. However, when there is only 1 row of data or no data for a particular day (This happens once or twice a month) the code does not work.

Here is an example of the code:

Sub Test()

Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(OR(ISNUMBER(SEARCH({""fee"",""inter""},RC[-1]))),""F"",IF(OR(ISNUMBER(SEARCH({""transf"",""direct pay"",""xf""},RC[-1]))),""T"",""O""))"
Range("C2").Select
Selection.End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "end"
Selection.End(xlUp).Select
Selection.Copy
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues

Range("A1").Select

Sheets("Sorted").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="T"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("transfers").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("Sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="O"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("other").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="F"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Fees-Interest").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False

End Sub


Any Help would be greatly appreciated!!

[Jim Cone]

S,
Check the selection for the number of rows and
exit the sub if less than two rows.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"STEVEB" wrote in message...

Hi,
Does anyone have any suggestions for the following:
I have a spreadsheet that looks at data and then sorts the data based
on certain text within the cell. The code works great when there are
several rows of data. However, when there is only 1 row of data or no
data for a particular day (This happens once or twice a month) the code
does not work.
Here is an example of the code:

Sub Test()

Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =
"=IF(OR(ISNUMBER(SEARCH({""fee"",""inter""},RC[-1]))),""F"",IF(OR(ISNUMBER(SEARCH({""transf"",""direct
pay"",""xf""},RC[-1]))),""T"",""O""))"
Range("C2").Select
Selection.End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "end"
Selection.End(xlUp).Select
Selection.Copy
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues

Range("A1").Select

Sheets("Sorted").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="T"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("transfers").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("Sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="O"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("other").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="F"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Fees-Interest").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False

End Sub

Any Help would be greatly appreciated!!
STEVEB

[Jim Thomlinson]

Here is a function that will tell you if there is anything in the cells after
Row 1.

Public Function IsPopulated(Optional ByVal wks As Worksheet) As Boolean
If wks Is Nothing Then Set wks = ActiveSheet
With wks
IsPopulated = CBool(Application.CountA(wks.Range(.Rows(2),
..Rows(Rows.Count))))
End With
End Function

So at the beginning of your code add...

Sub Test()
if ispopulated = false then exit sub
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1
--
HTH...

Jim Thomlinson


"STEVEB" wrote:

>
> Hi,
>
> Does anyone have any suggestions for the following:
>
> I have a spreadsheet that looks at data and then sorts the data based
> on certain text within the cell. The code works great when there are
> several rows of data. However, when there is only 1 row of data or no
> data for a particular day (This happens once or twice a month) the code
> does not work.
>
> Here is an example of the code:
>
> Sub Test()
>
> Range("D2").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 =
> "=IF(OR(ISNUMBER(SEARCH({""fee"",""inter""},RC[-1]))),""F"",IF(OR(ISNUMBER(SEARCH({""transf"",""direct
> pay"",""xf""},RC[-1]))),""T"",""O""))"
> Range("C2").Select
> Selection.End(xlDown).Offset(0, 1).Select
> ActiveCell.FormulaR1C1 = "end"
> Selection.End(xlUp).Select
> Selection.Copy
> Range("D3").Select
> Range(Selection, Selection.End(xlDown)).Select
> ActiveSheet.Paste
> Range("D2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Application.CutCopyMode = False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues
>
> Range("A1").Select
>
> Sheets("Sorted").Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=4, Criteria1:="T"
> Range("A2:d2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Copy
> Sheets("transfers").Select
> Range("A2").Select
> ActiveSheet.Paste
> Columns("A:d").Select
> Columns("A:d").EntireColumn.AutoFit
> Sheets("Sorted").Select
> Application.CutCopyMode = False
> Selection.AutoFilter Field:=4, Criteria1:="O"
> Range("A2:d2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Copy
> Sheets("other").Select
> Range("A2").Select
> ActiveSheet.Paste
> Columns("A:d").Select
> Columns("A:d").EntireColumn.AutoFit
> Sheets("sorted").Select
> Application.CutCopyMode = False
> Selection.AutoFilter Field:=4, Criteria1:="F"
> Range("A2:d2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Copy
> Sheets("Fees-Interest").Select
> Range("A2").Select
> ActiveSheet.Paste
> Columns("A:d").Select
> Columns("A:d").EntireColumn.AutoFit
> Sheets("sorted").Select
> Application.CutCopyMode = False
>
> End Sub
>
>
> Any Help would be greatly appreciated!!
>
>
> --
> STEVEB
> ------------------------------------------------------------------------
> STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
> View this thread: http://www.excelforum.com/showthread...hreadid=508310
>
>

[STEVEB]

Thanks Jim,

I appreciate your help!! When I try the code, I receive the following error mesage:

ActiveCell.FormulaR1C1 - Complie Error - Invalid use of property

Do you have any suggestions? Thanks