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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
It takes 24 hrs to run: with counter 3 mins?
Ste,
I think you would be better off telling us what it is that you want to actually calculate. There may be a formula-based solution that would work. HTH, Bernie MS Excel MVP "ste mac" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
It takes 24 hrs to run: with counter 3 mins?
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ...
Ste, I think you would be better off telling us what it is that you want to actually calculate. There may be a formula-based solution that would work. HTH, Bernie MS Excel MVP Hi Bernie, believe it or not my son and l actually cracked it (after weeks of struggling)... This mod let it know whic=h row was last worked on... hRow = Sheets(sSheetOne).Cells(2, 9).Value + 2 This mod made it add the total found to the original total.. ActiveSheet.Cells(sRow, (8 + lx)).Value = ActiveSheet.Cells(sRow, (8 + lx)).Value + lHits(lx) Really glad its working now... but thankyou very much for your offer to help, very much appreciated..... seeya ste |
Reply |
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 |