ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Simplified Help (https://www.excelbanter.com/excel-programming/281227-simplified-help.html)

Michael168[_47_]
Simplified Help
 
How can I simplified the macro below like something using increament
method.
These are just a minor extract only. Please help

sh2.Cells(32, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 0)
sh2.Cells(33, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 1)
sh2.Cells(34, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 2)
sh2.Cells(35, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 3)

sh2.Cells(32, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 0)
sh2.Cells(33, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 1)
sh2.Cells(34, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 2)
sh2.Cells(35, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 3)

sh2.Cells(32, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 0)
sh2.Cells(33, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 1)
sh2.Cells(34, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 2)
sh2.Cells(35, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 3)

sh2.Cells(32, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 0)
sh2.Cells(33, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 1)
sh2.Cells(34, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 2)
sh2.Cells(35, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 3)

sh2.Cells(32, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 0)
sh2.Cells(33, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 1)
sh2.Cells(34, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 2)
sh2.Cells(35, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 3)

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Bob Phillips[_5_]
Simplified Help
 
Michael,

Is this a bit simpler for you?

With Application.WorksheetFunction

For i = 0 To 3
sh2.Cells(32 + i, 3).Value = .CountIf(Range("C1:C30"), i)
sh2.Cells(32 + i, 5).Value = .CountIf(Range("E1:E30"), i)
sh2.Cells(32 + i, 7).Value = .CountIf(Range("G1:G30"), i)
sh2.Cells(32 + i, 9).Value = .CountIf(Range("I1:I30"), i)
sh2.Cells(32 + i, 11).Value = .CountIf(Range("K1:K30"), i)
Next i

End With


--

HTH

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

"Michael168" wrote in message
...
How can I simplified the macro below like something using increament
method.
These are just a minor extract only. Please help

sh2.Cells(32, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 0)
sh2.Cells(33, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 1)
sh2.Cells(34, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 2)
sh2.Cells(35, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 3)

sh2.Cells(32, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 0)
sh2.Cells(33, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 1)
sh2.Cells(34, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 2)
sh2.Cells(35, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 3)

sh2.Cells(32, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 0)
sh2.Cells(33, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 1)
sh2.Cells(34, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 2)
sh2.Cells(35, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 3)

sh2.Cells(32, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 0)
sh2.Cells(33, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 1)
sh2.Cells(34, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 2)
sh2.Cells(35, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 3)

sh2.Cells(32, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 0)
sh2.Cells(33, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 1)
sh2.Cells(34, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 2)
sh2.Cells(35, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 3)

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

jaf
Simplified Help
 
Hi Michael,
One way.

Myron=32
MyIf=0
for i= MyRow to 35 'Or highest row value of however many rows
sh2.Cells(i, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), MyIf)--
MyIf=MyIf +1
next




John

johnf202 at hotmail dot com


"Michael168" wrote in message
...
How can I simplified the macro below like something using increament
method.
These are just a minor extract only. Please help

sh2.Cells(32, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 0)
sh2.Cells(33, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 1)
sh2.Cells(34, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 2)
sh2.Cells(35, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 3)

sh2.Cells(32, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 0)
sh2.Cells(33, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 1)
sh2.Cells(34, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 2)
sh2.Cells(35, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 3)

sh2.Cells(32, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 0)
sh2.Cells(33, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 1)
sh2.Cells(34, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 2)
sh2.Cells(35, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 3)

sh2.Cells(32, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 0)
sh2.Cells(33, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 1)
sh2.Cells(34, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 2)
sh2.Cells(35, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 3)

sh2.Cells(32, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 0)
sh2.Cells(33, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 1)
sh2.Cells(34, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 2)
sh2.Cells(35, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 3)

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

jaf
Simplified Help
 
Sorry my spellchecker got carried away.


MyRow=32
MyIf=0
for i= MyRow to 35 'Or highest row value of however many rows
sh2.Cells(i, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), MyIf)--
MyIf=MyIf +1
next


--

John

johnf202 at hotmail dot com


"Michael168" wrote in message
...
How can I simplified the macro below like something using increament
method.
These are just a minor extract only. Please help

sh2.Cells(32, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 0)
sh2.Cells(33, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 1)
sh2.Cells(34, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 2)
sh2.Cells(35, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 3)

sh2.Cells(32, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 0)
sh2.Cells(33, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 1)
sh2.Cells(34, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 2)
sh2.Cells(35, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 3)

sh2.Cells(32, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 0)
sh2.Cells(33, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 1)
sh2.Cells(34, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 2)
sh2.Cells(35, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 3)

sh2.Cells(32, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 0)
sh2.Cells(33, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 1)
sh2.Cells(34, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 2)
sh2.Cells(35, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 3)

sh2.Cells(32, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 0)
sh2.Cells(33, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 1)
sh2.Cells(34, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 2)
sh2.Cells(35, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 3)

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Dana DeLouis[_5_]
Simplified Help
 
Would something like this work for you?

Sub Demo()
'Dana DeLouis
With [C32:C35,E32:E35,G32:G35,I32:I35,K32:K35]
.FormulaR1C1 = "=COUNTIF(R1C:R30C,ROW()-32)"
End With
End Sub

Use Copy / PasteSpecial Values if you want just the values.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Michael168" wrote in message
...
How can I simplified the macro below like something using increament
method.
These are just a minor extract only. Please help

sh2.Cells(32, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 0)
sh2.Cells(33, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 1)
sh2.Cells(34, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 2)
sh2.Cells(35, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C3 0"), 3)

sh2.Cells(32, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 0)
sh2.Cells(33, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 1)
sh2.Cells(34, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 2)
sh2.Cells(35, 5).Value = _
Application.WorksheetFunction.CountIf(Range("E1:E3 0"), 3)

sh2.Cells(32, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 0)
sh2.Cells(33, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 1)
sh2.Cells(34, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 2)
sh2.Cells(35, 7).Value = _
Application.WorksheetFunction.CountIf(Range("G1:G3 0"), 3)

sh2.Cells(32, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 0)
sh2.Cells(33, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 1)
sh2.Cells(34, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 2)
sh2.Cells(35, 9).Value = _
Application.WorksheetFunction.CountIf(Range("I1:I3 0"), 3)

sh2.Cells(32, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 0)
sh2.Cells(33, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 1)
sh2.Cells(34, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 2)
sh2.Cells(35, 11).Value = _
Application.WorksheetFunction.CountIf(Range("K1:K3 0"), 3)

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Dana DeLouis[_5_]
Simplified Help
 
My personal preference is to use "Intersect" if the number of Columns is
large...

Sub Demo()
'Dana DeLouis
With Intersect([C:C,E:E,G:G,I:I,K:K], [32:35])
.FormulaR1C1 = "=COUNTIF(R1C:R30C,ROW()-32)"
End With
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


<snip

Tushar Mehta
Simplified Help
 
Hi Dana,

You continue to amaze me with how you think -- and your ability to
develop alternatives to what most people would implement with a loop!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
My personal preference is to use "Intersect" if the number of Columns is
large...

Sub Demo()
'Dana DeLouis
With Intersect([C:C,E:E,G:G,I:I,K:K], [32:35])
.FormulaR1C1 = "=COUNTIF(R1C:R30C,ROW()-32)"
End With
End Sub


All times are GMT +1. The time now is 01:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com