I've got a report that is run in crystal and exported into excel. Once it's
in excel I have writen a macro to clean it up by removing blank columns, and
adding a formula to three specific rows. After the cleanup process the data
will always start at row 16 but will vary greatly in the total number of rows
which actually contain data. I have modified the macro to insert the proper
formulas into columns q,r, and s. I can make the macro fill down to the last
row of data if there are more than one row, but I get an error if there is
only the single row of data. In a different incarnation I can get the macro
to fill the first row with the formulas but it will not fill down to the last
row. I am trying to get it to do both.

I've been banging my head for days now... any help is greatly appreciated.
Below is the code starting from the end of the cleanup process where the
formulas are inserted, to the fill that I've been working on.

Range("Q16").Select
ActiveCell.Value = "=((D16+H16+P16)-I16)/E16"
Range("R16").Select
ActiveCell.Value = "=SUM(P16/N16)"
Range("S16").Select
ActiveCell.Value = "=O16"
Columns("Q:Q").ColumnWidth = 8
Columns("R:R").ColumnWidth = 8
Columns("S:S").ColumnWidth = 8

Call FillRange
End Sub

Sub FillRange()

Dim Llastrow As Long
Llastrow = Range("D65536").End(xlUp).Row
If Range("Q17") < " " Then
Range("Q16" & Llastrow).FillDown
Range("R16" & Llastrow).FillDown
Range("S16" & Llastrow).FillDown
' Range("Q16").AutoFill _
' Destination:=Range("Q16:Q" & Llastrow)
' Range("R16").AutoFill _
' Destination:=Range("R16:R" & Llastrow)
' Range("S16").AutoFill _
' Destination:=Range("S16:S" & Llastrow)
Else
End If
Range("A1").Select

End Sub

remove the "no"...."spam" for replys.

Thanks.

Hi,
Change your "Filldown" to:


Range("Q16:Q" & Llastrow).FillDown
Range("R16:R" & Llastrow).FillDown
Range("S16:S" & Llastrow).FillDown

You determine Llastrow using column D but test column Q(17) for blank. Would
it not be better to check D17 OR test if Llastrow16 then Filldown?


HTH

"tourless" wrote:

I've got a report that is run in crystal and exported into excel. Once it's
in excel I have writen a macro to clean it up by removing blank columns, and
adding a formula to three specific rows. After the cleanup process the data
will always start at row 16 but will vary greatly in the total number of rows
which actually contain data. I have modified the macro to insert the proper
formulas into columns q,r, and s. I can make the macro fill down to the last
row of data if there are more than one row, but I get an error if there is
only the single row of data. In a different incarnation I can get the macro
to fill the first row with the formulas but it will not fill down to the last
row. I am trying to get it to do both.

I've been banging my head for days now... any help is greatly appreciated.
Below is the code starting from the end of the cleanup process where the
formulas are inserted, to the fill that I've been working on.

Range("Q16").Select
ActiveCell.Value = "=((D16+H16+P16)-I16)/E16"
Range("R16").Select
ActiveCell.Value = "=SUM(P16/N16)"
Range("S16").Select
ActiveCell.Value = "=O16"
Columns("Q:Q").ColumnWidth = 8
Columns("R:R").ColumnWidth = 8
Columns("S:S").ColumnWidth = 8

Call FillRange
End Sub

Sub FillRange()

Dim Llastrow As Long
Llastrow = Range("D65536").End(xlUp).Row
If Range("Q17") < " " Then
Range("Q16" & Llastrow).FillDown
Range("R16" & Llastrow).FillDown
Range("S16" & Llastrow).FillDown
' Range("Q16").AutoFill _
' Destination:=Range("Q16:Q" & Llastrow)
' Range("R16").AutoFill _
' Destination:=Range("R16:R" & Llastrow)
' Range("S16").AutoFill _
' Destination:=Range("S16:S" & Llastrow)
Else
End If
Range("A1").Select

End Sub

remove the "no"...."spam" for replys.

Thanks.

Toppers!

Fantastic! I changed the Filldowns and your advise on testing LlastRow16
worked like a charm. Now I am able to fill reports with both a sngle row and
multiple rows with the appropriate formulas.

THANKS MUCH!!!

~tourless

"Toppers" wrote:

Hi,
Change your "Filldown" to:


Range("Q16:Q" & Llastrow).FillDown
Range("R16:R" & Llastrow).FillDown
Range("S16:S" & Llastrow).FillDown

You determine Llastrow using column D but test column Q(17) for blank. Would
it not be better to check D17 OR test if Llastrow16 then Filldown?


HTH

"tourless" wrote:

I've got a report that is run in crystal and exported into excel. Once it's
in excel I have writen a macro to clean it up by removing blank columns, and
adding a formula to three specific rows. After the cleanup process the data
will always start at row 16 but will vary greatly in the total number of rows
which actually contain data. I have modified the macro to insert the proper
formulas into columns q,r, and s. I can make the macro fill down to the last
row of data if there are more than one row, but I get an error if there is
only the single row of data. In a different incarnation I can get the macro
to fill the first row with the formulas but it will not fill down to the last
row. I am trying to get it to do both.

I've been banging my head for days now... any help is greatly appreciated.
Below is the code starting from the end of the cleanup process where the
formulas are inserted, to the fill that I've been working on.

Range("Q16").Select
ActiveCell.Value = "=((D16+H16+P16)-I16)/E16"
Range("R16").Select
ActiveCell.Value = "=SUM(P16/N16)"
Range("S16").Select
ActiveCell.Value = "=O16"
Columns("Q:Q").ColumnWidth = 8
Columns("R:R").ColumnWidth = 8
Columns("S:S").ColumnWidth = 8

Call FillRange
End Sub

Sub FillRange()

Dim Llastrow As Long
Llastrow = Range("D65536").End(xlUp).Row
If Range("Q17") < " " Then
Range("Q16" & Llastrow).FillDown
Range("R16" & Llastrow).FillDown
Range("S16" & Llastrow).FillDown
' Range("Q16").AutoFill _
' Destination:=Range("Q16:Q" & Llastrow)
' Range("R16").AutoFill _
' Destination:=Range("R16:R" & Llastrow)
' Range("S16").AutoFill _
' Destination:=Range("S16:S" & Llastrow)
Else
End If
Range("A1").Select

End Sub

remove the "no"...."spam" for replys.

Thanks.