formula not showing up

G

Guest

I ask a question about this code earlier and that question was answered. Now
I have a new one:
Why doesn't the formula autofill?

Here is the code:
Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
Range("H2").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Range("H3").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Range("H2:H3").Select
Selection.Copy
Range("H4:H" & LastCell).Select
Selection.AutoFill Destination:=Range("H4:H" & LastCell),
Type:=xlFillDefault

Thanks,
Nicole
 
G

Gary Keramidas

sorry, there was an inadvertent space in the original reply

Range("H4").AutoFill Range("h4:h" & LastCell)
 
G

Guest

Sub abc()
Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
Range("H2").Select
ActiveCell.FormulaR1C1 =
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Selection.AutoFill Destination:=Range("H2:H" & LastCell),
Type:=xlFillDefault

End Sub

worked for me.
 
G

Guest

I think I am in love with Tom... mentally of course.

Tom Ogilvy said:
Sub abc()
Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Selection.AutoFill Destination:=Range("H2:H" & LastCell),
Type:=xlFillDefault

End Sub

worked for me.

--
Regards,
Tom Ogilvy


Nicole Seibert said:
I ask a question about this code earlier and that question was answered. Now
I have a new one:
Why doesn't the formula autofill?

Here is the code:
Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
Range("H2").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Range("H3").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Range("H2:H3").Select
Selection.Copy
Range("H4:H" & LastCell).Select
Selection.AutoFill Destination:=Range("H4:H" & LastCell),
Type:=xlFillDefault

Thanks,
Nicole
Click to expand...
Click to expand...
 
G

Gary Keramidas

this is all you need

Sub test()

Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row

Range("H2").FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"

Range("H3").FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"

Range("H2:H3").AutoFill Range("H2:H" & LastCell)

End Sub

--


Gary


Nicole Seibert said:
I think I am in love with Tom... mentally of course.

Tom Ogilvy said:
Sub abc()
Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Selection.AutoFill Destination:=Range("H2:H" & LastCell),
Type:=xlFillDefault

End Sub

worked for me.

--
Regards,
Tom Ogilvy


Nicole Seibert said:
I ask a question about this code earlier and that question was answered.
Now
I have a new one:
Why doesn't the formula autofill?

Here is the code:
Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
Range("H2").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Range("H3").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Range("H2:H3").Select
Selection.Copy
Range("H4:H" & LastCell).Select
Selection.AutoFill Destination:=Range("H4:H" & LastCell),
Type:=xlFillDefault

Thanks,
Nicole
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Actually all she needs is

Sub test()

Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row

Range("H2:H" & LastCell).FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN
THO*"",""*NELSONBECKY*""})*{1,2,3,4})"
End Sub

--
Regards,
Tom Ogilvy

Gary Keramidas said:
this is all you need

Sub test()

Dim LastCell As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row

Range("H2").FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN
THO*"",""*NELSONBECKY*""})*{1,2,3,4})"

Range("H3").FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN
THO*"",""*NELSONBECKY*""})*{1,2,3,4})"

Range("H2:H3").AutoFill Range("H2:H" & LastCell)

End Sub

--


Gary


I think I am in love with Tom... mentally of course.
Click to expand...
"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN
THO*"",""*NELSONBECKY*""})*{1,2,3,4})" "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN
THO*"",""*NELSONBECKY*""})*{1,2,3,4})" "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN
THO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

autofill down until reach variable range 3
"=TEXT(RC[-1],"MMM")" 4
macro to copy rows that contain a cell with a certain number 2
Help with my VBA and formula 8
Modify range in VBA 9
Macro to insert formula failing...need help 3
R1:C1 format 2
remove the row value in the pivot table creation code. 1

Top