# Macro Question

#### khatley

##### Board Regular
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Ekim

##### Well-known Member
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
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
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

Replies
5
Views
343
Replies
1
Views
505
Replies
0
Views
875
Replies
8
Views
249
Replies
12
Views
318

1,186,362
Messages
5,957,410
Members
438,304
Latest member
duck90

### 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.

### Which adblocker are you using?

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

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