Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simplified Help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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/

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 620
Default 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/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
jaf jaf is offline
external usenet poster
  
Posts: 300
Default 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/



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
jaf jaf is offline
external usenet poster
  
Posts: 300
Default 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/



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 77
Default 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/



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 77
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,071
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplified SUMPRODUCT formula ! wilchong via OfficeKB.com New Users to Excel 3 February 19th 09 01:42 PM
looking for simplified formulas philip Excel Worksheet Functions 6 June 7th 06 03:03 AM
Simplified Data Entry Spellbound Excel Discussion (Misc queries) 0 March 24th 06 12:18 PM
Can this be simplified Barb Reinhardt Excel Worksheet Functions 3 October 4th 05 02:35 PM
Is there a way to stop fractions from being simplified? John Wilkins Excel Worksheet Functions 3 September 27th 05 04:17 PM


All times are GMT +1. The time now is 09:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement