Simplified Help

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

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:C30"), 0)
sh2.Cells(33, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C30"), 1)
sh2.Cells(34, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C30"), 2)
sh2.Cells(35, 3).Value = _
Application.WorksheetFunction.CountIf(Range("C1:C30"), 3)

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

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

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

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

Thank you.
 
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)
 
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:C30"), MyIf)--
MyIf=MyIf +1
next




John

johnf202 at hotmail dot com
 
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:C30"), MyIf)--
MyIf=MyIf +1
next
 
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.
 
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>
 
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
 
Back
Top