CODEWITHSUNDEEP
vbaexcel
MrJam
MrJam

Reputation: 3

vba change formula to value

I have wrote a sub that uses autofill to set a cell to a countifs formula over a specific range but I cant seem to get it to convert the range from a formula to a value afterwards.

I want to avoid using Copy, Paste Special to do this. I have tried to use a few different variations of .value = .value but don't seem to be able to get this to work properly, quite often it just sets all the values to 1 which I know is not correct, any help would be greatly appreciated

I have attached a copy of the working code below that sets the cells to the countifs formula to avoid any confusion with the .value = .value mistakes I have made

Thanks

'   wsWorksheet is the worksheet to be used
'   lngFirstRow is the first row of data
'   strSourceCol is the letter (e.g. A) of the column to be counted
'   strCountCol is the letter (e.g. I) of the column to add the count result to
Sub Concurrency(wsWorksheet As Worksheet, lngFirstRow As Long, strSourceCol As String, strCountCol As String)
Dim lngLastRow As Long
Dim strFormula As String
Const ConcFormula As String = "=(COUNTIFS(D$1:D1,"">""&C2,A$1:A1,A2))+1"

'Find the last row of data in the source column
With wsWorksheet
      lngLastRow = .Cells(.Rows.Count, strSourceCol).End(xlUp).Row
      If lngLastRow < lngFirstRow Then lngLastRow = lngFirstRow
End With


'With the first cell on the count column
With wsWorksheet.Cells(lngFirstRow, strCountCol)
    'Add the count formula
    .Formula = ConcFormula
    'If there are more rows autofill it down
    If lngLastRow > lngFirstRow Then
        .AutoFill Destination:=Range( _
            .Cells, _
            .Offset(rowoffset:=lngLastRow - lngFirstRow).Cells), Type:=xlFillDefault
    End If
End With

End Sub

Upvotes: 0

Views: 2389

Answers (1)

Rory
Rory

Reputation: 34075

You should be able to do this:

With wsWorksheet
   With .Range(.Cells(lngFirstRow, strCountCol), .Cells(lngLastRow, strCountCol))
    'Add the count formula
      .Formula = ConcFormula
      .Value2 = .Value2
   End With
End With

Upvotes: 3

Related Questions