Using an array loop.

[lauren_wing]

hi

I am attaching a text file in which a part of the code is being repeated.Can you please help me how to write the repeated code using an array loop.


Please see the attached text file.and please attach a text file which has the modified code using an array.

Thank you.

[protonLeah]

Please Login or Register  to view this content.

[lauren_wing]

hi i am sorry to reply back but when i put the above code it is giving me application defined object error.Please attach a text file as how to rectify this using an array and loop thru those columns.

Thank you.

[TMS]

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Regards

[lauren_wing]

Thank you so much protonLeah and TM shucks.Thank you.

[lauren_wing]

hi

i need little help in tuning the below code.Can anyone please help me with the code in reducing the preformance.Please help me for a better perfomance of the code below.






<code></code>

Private Sub format()

Dim startrow As Long
Dim rownumber As Long
Dim lastrownumber As Long
Dim endrow As Long
Dim LoopCtrl As Variant
Dim strRange As String
Dim rng As Range



<code>
lastrownumber = ReturnLastRow(AllEntriesFlagged)
'lastrownumber = AllEntriesFlagged.UsedRange.Rows.count
For rownumber = 43 To lastrownumber
If Cells(rownumber, 2).Value = "Entry" Then
rownumber = rownumber + 1
startrow = rownumber
While Cells(rownumber, 2).Value <> "Entry" And rownumber <= lastrownumber
rownumber = rownumber + 1
Wend
rownumber = rownumber - 1
endrow = rownumber

For Each LoopCtrl In Array("M", "N", "P", "Q")
With Application.WorksheetFunction
Set rng = Range(Cells(startrow, LoopCtrl), Cells(endrow, LoopCtrl))
If .CountA(rng) = .Countif(rng, Range(LoopCtrl & startrow)) Then
rng.Font.Color = vbWhite
End If
End With
Next LoopCtrl

End If
Next

End Sub

</code>




Can it be done without using CountA as it is counting all the rows one by one.
Instead of that can we user endrow- startrow+1 as we know the rownumber.


Can anyone please fine tune the code so as to reduce performance or by using any functions if so possible.

Now it is taking 40 secs for 90,000 rows and( for 4 columns).This time has to be reduced instead of using CountA function,endrow- startrow+1 should be used so that it reduces the performance.

I am attaching the template as a sample which is comparitively having less number of rows in the All Entries Flagged sheet.

Please help me at the earliest.