I'm trying to write a basic macro that will copy a formula
down in column b to the same row that the data ends in
column a. There's nothing too complex about this, and I'm
sure I'm just overlooking a simple solution, but I can't
figure it out. My code up to this point (which I posted
below) copies the formula down to an arbitrary row.
Please help. Thanks.







'CHANGING ZIP CODE CELLS TO CORRECT FORMAT
Range("C3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+0"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C104"),
Type:=xlFillDefault
Range("C3:C104").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-4
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:A").Select
Selection.NumberFormat = "00000"
Columns("C:C").Select
Application.CutCopyMode = False
Selection.ClearContents
'VLOOKUPS
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1], ZIPS, 2,
FALSE)), ""INDEP."", VLOOKUP(RC[-1], ZIPS, 2, FALSE))"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B104")
Range("B3:B104").Select
Range("B3").Select
End Sub

assume you want to copy down the formula in B3

Sub Tester2()
Dim rng As Range
Set rng = Range(Cells(3, "A"), _
Cells(Rows.Count, "A").End(xlUp))
rng.Offset(0, 1).FillDown

End Sub

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
I'm trying to write a basic macro that will copy a formula
down in column b to the same row that the data ends in
column a. There's nothing too complex about this, and I'm
sure I'm just overlooking a simple solution, but I can't
figure it out. My code up to this point (which I posted
below) copies the formula down to an arbitrary row.
Please help. Thanks.







'CHANGING ZIP CODE CELLS TO CORRECT FORMAT
Range("C3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+0"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C104"),
Type:=xlFillDefault
Range("C3:C104").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-4
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:A").Select
Selection.NumberFormat = "00000"
Columns("C:C").Select
Application.CutCopyMode = False
Selection.ClearContents
'VLOOKUPS
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1], ZIPS, 2,
FALSE)), ""INDEP."", VLOOKUP(RC[-1], ZIPS, 2, FALSE))"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B104")
Range("B3:B104").Select
Range("B3").Select
End Sub

Hi,



This macro should do the work:



Sub CopyFormula()

Dim intR As Integer

Dim strAd As String

Dim intL As Integer

strAd = ActiveCell.Address

ActiveCell.Copy

intR = ActiveCell.Offset(0, -1).CurrentRegion.Rows.Count

'MsgBox strAd & " " & intR

For intL = 1 To intR

ActiveCell.Offset(1, 0).PasteSpecial

Next intL

End Sub


--
JP

http://www.solutionsvba.com


"Kevin" wrote in message
...
I'm trying to write a basic macro that will copy a formula
down in column b to the same row that the data ends in
column a. There's nothing too complex about this, and I'm
sure I'm just overlooking a simple solution, but I can't
figure it out. My code up to this point (which I posted
below) copies the formula down to an arbitrary row.
Please help. Thanks.







'CHANGING ZIP CODE CELLS TO CORRECT FORMAT
Range("C3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+0"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C104"),
Type:=xlFillDefault
Range("C3:C104").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-4
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:A").Select
Selection.NumberFormat = "00000"
Columns("C:C").Select
Application.CutCopyMode = False
Selection.ClearContents
'VLOOKUPS
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1], ZIPS, 2,
FALSE)), ""INDEP."", VLOOKUP(RC[-1], ZIPS, 2, FALSE))"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B104")
Range("B3:B104").Select
Range("B3").Select
End Sub