Macro Question

khatley

Board Regular
Joined
Apr 4, 2003
Messages
62
I am trying to record a macro that will do an insert and add a COUNTIF formula to every row that has data in it...problem is when I record it only adds the numbe of rows specified. Is there a way to tell it to add it every row that has data or would I have to add it to all of the rows? When I am recording I hit shift and double click the bottom right corner to carry it down but it only records the actual number of cells it copies it to.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
One way is to pick a column that has data and use that to determine the number of rows to copy down your formula. For example, say that column A always has data from A5 down to some row and I want to put a formula in cell F5 and copy it down for the number of rows used in column A. This type of syntax will do the job:
Code:
Dim lastrow As Integer
lastrow = Range("A65536").End(xlUp).Row + 1

With Worksheets("Sheet1”)
    .Range("F5").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
    .Range("F5").AutoFill Destination:=.Range("A5", _
               .Range("A5").End(xlDown)).Offset(, 5)
       With ws2.Range("F5:F" & lastrow)
              .Value = .Value
       End With
End With
If you want precise help with what you are trying to do, post your recorded macro and advise a column that will always have data.

Regards,

Mike
 

khatley

Board Regular
Joined
Apr 4, 2003
Messages
62
I want =COUNTIF(B1:IV1,"PFassessment")
to copied to every cell in Column A if there is data in Column B, normally I put the above formul in A1 and shift double click to copy it to the last row...I would like to automate this.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Assumptions:

1. Your worksheet is Sheet1 (amend to suit)
2. Your data in column B starts in B5 (amend to suit)
3. Your formula should have absolute references i.e. =COUNTIF($B$1:$IV$1,"PFassessment")
Amend to suit
Code:
Sub copyFormula()
Dim lastrow As Integer
Dim ws As Worksheet

lastrow = Range("A65536").End(xlUp).Row + 1
Set ws = Worksheets("Sheet1")

With ws
  .Range("A5").Formula = "=COUNTIF($B$1:$IV$1,""PFassessment"")"
  .Range("A5").AutoFill Destination:=.Range("B5", _
        .Range("B5").End(xlDown)).Offset(, -1)
   ' With ws.Range("A5:A" & lastrow)
     '   .Value = .Value
    ' End With
End With

End Sub
The above sub will place your formula in column A5 downwards. If you want to see the result of the formula but not the formula itself, then uncomment the following lines (i.e. remove the starting apostrophe):

' With ws.Range("A5:A" & lastrow)
' .Value = .Value
' End With

HTH

Mike
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,871
Messages
5,766,857
Members
425,382
Latest member
IronM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top