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

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

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