| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Doing a 'countif' but I just want the value not the formula
Frank, Bob, thankyou very much...
seeya ste |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF FORMULA ISSUES - NEED FORMULA CORRECTED | Excel Worksheet Functions | |||
| Countif formula | Excel Discussion (Misc queries) | |||
| CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
| Countif formula and then some... | Excel Worksheet Functions | |||
| COUNTIF FORMULA | Excel Programming | |||
Linear Mode
