Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
It takes 24 hrs to run: with counter 3 mins?
Hi there, l need a bit of help, l am no programmer but l do find VBA
facinating. My problem: l have code (borrowed, begged, and from this ng) that checks a 5 number set against a 5 cell x 400+(variable) row 'range', as it is now, it checks starting from row 1 thru' to the end of the 'range'... what l need is to be able to run the check only against new (added rows) to the 'range'...and add the hits to the total. I thought l just need a counter to tell the code which row it has already checked and just add the hits of the new rows to the total. I have had lots of goes at doing this, but l wonder if the logic is incorrect for an easy mod...but for the life of me l cannot get it to work... Any help would be greatly appreciated. ste 'It does all the sheets with this Sub beginprocess() Dim sheetname As String Dim sheetnumber As Long For sheetnumber = 1 To 56 sheetname = "S" & Format(sheetnumber, "##0") Sheets(sheetname).Select HistoryCheck Next End Sub 'Then finds the totals with this.. Option Explicit Public sSheetOne As String Public sSheetTwo As String Public hRow As Long Public hCol As Long Public sRow As Long Public sCol As Long Public lHits(5) As Long Public lThi**** As Long Public lx As Long Public Sub HistoryCheck() sSheetOne = "The History" Application.ScreenUpdating = False sRow = 2 Do While Not ActiveSheet.Cells(sRow, 1).Value = "" CheckThisLine UpdateTotals sRow = (sRow + 1) Loop End Sub Private Sub CheckThisLine() For lx = 0 To 5 lHits(lx) = 0 Next hRow = 2 Do While Not Sheets(sSheetOne).Cells(hRow, 1).Value = "" lThi**** = 0 For hCol = 2 To 6 For sCol = 3 To 7 If (Sheets(sSheetOne).Cells(hRow, hCol).Value = ActiveSheet.Cells(sRow, sCol).Value) Then lThi**** = (lThi**** + 1) End If Next Next lHits(lThi****) = (lHits(lThi****) + 1) hRow = (hRow + 1) Loop End Sub Private Sub UpdateTotals() For lx = 0 To 5 ActiveSheet.Cells(sRow, (8 + lx)).Value = lHits(lx) Cells(sRow, 14) = Cells(sRow, 11) + Cells(sRow, 12) + Cells(sRow, 13) Next End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening files takes over 2 mins within excel | Excel Discussion (Misc queries) | |||
how can i convert a value quoted in hrs,mins,secs to just mins | Excel Worksheet Functions | |||
Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins? | Excel Discussion (Misc queries) | |||
How to convert numeric value to mins and addimg mins to Hrs. | Excel Worksheet Functions | |||
Save takes seconds vs. SaveAs/Save As takes minutes | Excel Programming |