Doing a 'countif' but I just want the value not the formula
 

Hi There, can anybody help me with this one please:
I am no programmer but the macro below sticks a countif
formula in each cell as requested, but this makes the wb
slow to update (as it calcs of each cell) is there anyway
I can change it so it calcs the countif but just places the
amount it counts in each cell instead of the formulae?
Thanks for any help
seeya ste

Public Sub howmany()
Dim xlrow As Long

thedata = "DV3:EJ65000"
xlrow = 2150

Do While Not ActiveSheet.Cells(xlrow, 70).Value = ""

ActiveSheet.Cells(xlrow, 73).Formula = "=countif(" & thedata & ", " &
ActiveSheet.Cells(xlrow, 70).Value & ")"

xlrow = xlrow + 1
Application.StatusBar = xlrow
Loop
Application.StatusBar = False
End Sub

Doing a 'countif' but I just want the value not the formula
 

Hi
after the line
ActiveSheet.Cells(xlrow, 73).Formula = "=countif(" & thedata & ", " &
ActiveSheet.Cells(xlrow, 70).Value & ")"

add the line
ActiveSheet.Cells(xlrow, 73).value=ActiveSheet.Cells(xlrow, 73).value

--
Regards
Frank Kabel
Frankfurt, Germany

"ste mac" schrieb im Newsbeitrag
om...
Hi There, can anybody help me with this one please:
I am no programmer but the macro below sticks a countif
formula in each cell as requested, but this makes the wb
slow to update (as it calcs of each cell) is there anyway
I can change it so it calcs the countif but just places the
amount it counts in each cell instead of the formulae?
Thanks for any help
seeya ste

Public Sub howmany()
Dim xlrow As Long

thedata = "DV3:EJ65000"
xlrow = 2150

Do While Not ActiveSheet.Cells(xlrow, 70).Value = ""

ActiveSheet.Cells(xlrow, 73).Formula = "=countif(" & thedata & ", " &
ActiveSheet.Cells(xlrow, 70).Value & ")"

xlrow = xlrow + 1
Application.StatusBar = xlrow
Loop
Application.StatusBar = False
End Sub

Doing a 'countif' but I just want the value not the formula
 

or even

ActiveSheet.Cells(xlrow, 73).Value = Application.CountIf(Range(theData),
ActiveSheet.Cells(xlrow, 70).Value)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
after the line
ActiveSheet.Cells(xlrow, 73).Formula = "=countif(" & thedata & ", " &
ActiveSheet.Cells(xlrow, 70).Value & ")"

add the line
ActiveSheet.Cells(xlrow, 73).value=ActiveSheet.Cells(xlrow, 73).value

--
Regards
Frank Kabel
Frankfurt, Germany

"ste mac" schrieb im Newsbeitrag
om...
Hi There, can anybody help me with this one please:
I am no programmer but the macro below sticks a countif
formula in each cell as requested, but this makes the wb
slow to update (as it calcs of each cell) is there anyway
I can change it so it calcs the countif but just places the
amount it counts in each cell instead of the formulae?
Thanks for any help
seeya ste

Public Sub howmany()
Dim xlrow As Long

thedata = "DV3:EJ65000"
xlrow = 2150

Do While Not ActiveSheet.Cells(xlrow, 70).Value = ""

ActiveSheet.Cells(xlrow, 73).Formula = "=countif(" & thedata & ", " &
ActiveSheet.Cells(xlrow, 70).Value & ")"

xlrow = xlrow + 1
Application.StatusBar = xlrow
Loop
Application.StatusBar = False
End Sub

Doing a 'countif' but I just want the value not the formula
 

Frank, Bob, thankyou very much...

seeya ste