Copy Formula with a Macro
 

What is a simple way (in a macro) to copy a formula down an empty column and
only paste the formula if data exists on that row?

I've used {Worksheets("Sheet1").Range("A1:A100").FillDown} when I know how
many rows have data, but in this case I don't have any idea how many rows
there may be with data.

Thanks for any help.

Copy Formula with a Macro
 

I guess that you mean

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
With Worksheets("Sheet1").Range("A1")
.Autofill .Resize(iLastRow)
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J" wrote in message
...
What is a simple way (in a macro) to copy a formula down an empty column
and
only paste the formula if data exists on that row?

I've used {Worksheets("Sheet1").Range("A1:A100").FillDown} when I know how
many rows have data, but in this case I don't have any idea how many rows
there may be with data.

Thanks for any help.

Copy Formula with a Macro
 

Thank you for your help.

When attempting to use this, I get
"Run-time error '449':
Argument not optional."

My ignorance is such that I don't know what argument I am missing.

Any suggestions or direction is appreciated.

"Bob Phillips" wrote:

I guess that you mean

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
With Worksheets("Sheet1").Range("A1")
.Autofill .Resize(iLastRow)
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J" wrote in message
...

What is a simple way (in a macro) to copy a formula down an empty column
and only paste the formula if data exists on that row?

I've used {Worksheets("Sheet1").Range("A1:A100").FillDown} when I know how
many rows have data, but in this case I don't have any idea how many rows
there may be with data.

Thanks for any help.

Copy Formula with a Macro
 

I don't get this error.

Can you explain what you did , and post the whole code that you used.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J" wrote in message
...
Thank you for your help.

When attempting to use this, I get
"Run-time error '449':
Argument not optional."

My ignorance is such that I don't know what argument I am missing.

Any suggestions or direction is appreciated.

"Bob Phillips" wrote:

I guess that you mean

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
With Worksheets("Sheet1").Range("A1")
.Autofill .Resize(iLastRow)
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J" wrote in message
...

What is a simple way (in a macro) to copy a formula down an empty
column
and only paste the formula if data exists on that row?

I've used {Worksheets("Sheet1").Range("A1:A100").FillDown} when I know
how
many rows have data, but in this case I don't have any idea how many
rows
there may be with data.

Thanks for any help.

Copy Formula with a Macro
 

Thank you for your help. This macro grabs a text file, does some simple
manipulation, and saves a file for a report to grab. I'm probably missing
something simple. Below is the entire macro up to the point of this step.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 8/22/2005 by JL Hege
'
Application.DisplayAlerts = False

ChDir "G:\quality\Monthly Quality Reports\Data - Do Not Change\ICAR
Data\Current"
Workbooks.OpenText Filename:="G:\quality\Monthly Quality Reports\Data -
Do Not Change\ICAR Data\Current\ICAR" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True

ChDir _
"G:\Quality\Monthly Quality Reports\Data - Do Not Change\ICAR
Data\Current"
ActiveWorkbook.SaveAs Filename:= _
"G:\Quality\Monthly Quality Reports\Data - Do Not Change\ICAR
Data\Current\Temp ICAR.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Windows("Temp ICAR.xls").Activate
Sheets("ICAR").Select

Columns("J:J").Select
Columns("J:J").Cut Destination:=Columns("C:C")
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[+2]-RC[+1]"

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("ICAR").Range("A1")
.AutoFill.Resize (iLastRow)
End With

"Bob Phillips" wrote:

I don't get this error.

Can you explain what you did , and post the whole code that you used.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J" wrote in message
...
Thank you for your help.

When attempting to use this, I get
"Run-time error '449':
Argument not optional."

My ignorance is such that I don't know what argument I am missing.

Any suggestions or direction is appreciated.

"Bob Phillips" wrote:

I guess that you mean

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
With Worksheets("Sheet1").Range("A1")
.Autofill .Resize(iLastRow)
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J" wrote in message
...

What is a simple way (in a macro) to copy a formula down an empty
column
and only paste the formula if data exists on that row?

I've used {Worksheets("Sheet1").Range("A1:A100").FillDown} when I know
how
many rows have data, but in this case I don't have any idea how many
rows
there may be with data.

Thanks for any help.