Macro Question

[khatley]

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.

 

[Ekim]

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:

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]

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]

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

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