Vba not responding with nested loop (priority sheet)

[nu_miniy]

Hi all, I need help with vba, I want to make vba to:

1. Search day and month from 4 sheet: Sheet1, Sheet2, Sheet3, Sheet4 from 1 January to 31 December
2. There's priority sheet sequence sheet1 ->sheet2 -> sheet3 -> sheet4, it can't be mix or the calculation will all wrong
3. So if it at 1 January the sheet1 will calculate some formula, and then I can calculate sheet2, then sheet3 and then sheet4
4. If it finish in sheet4, move the search to 2 January, and so on..

Everytime I run it, my excel and vba always not responding, but If I try only with sheet1 it works fine..
This what I make:

Sub LookingFor()
Dim i As Integer, j As Integer
Dim rcell1 As Range, rcell2 As Range, rcell3 As Range, rcell4 As Range
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range


Set rng1 = Sheets("Sheet1").Range("C3:C700")
Set rng2 = Sheets("Sheet2").Range("C3:C700")
Set rng3 = Sheets("Sheet3").Range("C3:C700")
Set rng4 = Sheets("Sheet4").Range("C3:C700")


For i = 1 To 12 'For month'
    For j = 1 To 31 'For day'
        For Each rcell1 In rng1
            If Day(rcell1.Value) = i And Month(rcell1.Value) = j Then
                'Do something'    
            End If
'---------------------------------------------------------------------------------------------------------------------------------------'            
[INDENT]        For Each rcell2 In rng2[/INDENT]
[INDENT]            If Day(rcell2.Value) = i And Month(rcell2.Value) = j Then[/INDENT]
[INDENT]                'Do Something'[/INDENT]
[INDENT]            End If[/INDENT]
'---------------------------------------------------------------------------------------------------------------------------------------'        
[INDENT=2]        For Each rcell3 In rng3[/INDENT]
[INDENT=2]            If Day(rcell3.Value) = i And Month(rcell3.Value) = j Then[/INDENT]
[INDENT=2]                'Do Something'[/INDENT]
[INDENT=2]            End If[/INDENT]
'---------------------------------------------------------------------------------------------------------------------------------------'        
[INDENT=3]        For Each rcell4 In rng4[/INDENT]
[INDENT=3]            If Day(rcell4.Value) = i And Month(rcell4.Value) = j Then[/INDENT]
[INDENT=3]                'Do something'[/INDENT]
[INDENT=3]            End If[/INDENT]
                                Next
                        Next
                Next
        Next
    Next
Next


End Sub

 

[wideboydixon]

I think you should terminate your loops a little quicker! It will probably start not responding because you're running the loop this many times:

12 x 31 x 698 x 698 x 698 x 698 = 1,012,043,534,592 which is more than 1 trillion!

I'm not sure why you need to run the loop like this but at least you can bring it down to manageable values by completing the search on each sheet:

Sub LookingFor()


Dim i As Integer, j As Integer
Dim rcell1 As Range, rcell2 As Range, rcell3 As Range, rcell4 As Range
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range


Set rng1 = Sheets("Sheet1").Range("C3:C700")
Set rng2 = Sheets("Sheet2").Range("C3:C700")
Set rng3 = Sheets("Sheet3").Range("C3:C700")
Set rng4 = Sheets("Sheet4").Range("C3:C700")


For i = 1 To 12 'For month'
    For j = 1 To 31 'For day'
        For Each rcell1 In rng1
            If Day(rcell1.Value) = i And Month(rcell1.Value) = j Then
                'Do something'    
            End If
        Next
'---------------------------------------------------------------------------------------------------------------------------------------'            
        For Each rcell2 In rng2
            If Day(rcell2.Value) = i And Month(rcell2.Value) = j Then
                'Do Something'
            End If
        Next
'---------------------------------------------------------------------------------------------------------------------------------------'        
        For Each rcell3 In rng3
            If Day(rcell3.Value) = i And Month(rcell3.Value) = j Then
                'Do Something'
            End If
        Next
'---------------------------------------------------------------------------------------------------------------------------------------'        
        For Each rcell4 In rng4
            If Day(rcell4.Value) = i And Month(rcell4.Value) = j Then
                'Do something'
            End If
        Next
    Next
Next


End Sub

Now you're running 12 x 31 x 698 x 4 = 1,038,624 which is still more than one million checks but it should perform better. Even better might be to just run through the ranges and get the day/month from each cell and process accordingly (2,792 iterations).

WBD

 

[ukmikeb]

Hi

Why not look at the frequency of the dates on each sheet?

Do they all relate to the same year?

It could be that you would be better served by filtering the data.

hth

 

[nu_miniy]

Doesn't it mean if I put next after each "for each" it will moving to the next cell in range? And do you mean 2,792 iteration is by delete 2 for (day and month) and just manually separate day and month in excel?

 

[wideboydixon]

Yes but you're working on 4 different ranges anyway. The best solution relies on knowing what you're doing in the "'Do Something" pieces of code. You could even cycle through each sheet and test the value there:

Sub LookingFor()


Dim i As Integer, j As Integer, k As Integer, s As Integer
Dim sht As WorkSheet


For i = 1 To 12 'For month'
    For j = 1 To 31 'For day'
        For s = 1 To 4 'For sheet'
            Set sht = Sheets("Sheet" & CStr(s))
            For k = 3 To 700 'For range'
                If Day(sht.Cells(k, 3)) = i And Month(sht.Cells(k, 3)) = j Then
                    'Do something
                End If
            Next
        Next
    Next
Next


End Sub

WBD

 

[nu_miniy]

Can you explain with your meaning "frequency of the dates on each sheet"? Yes, they all in same year.. If filtering the data and check for one day it can be work but I don't want to filtering each day and month..

 

[nu_miniy]

Yes but you're working on 4 different ranges anyway. The best solution relies on knowing what you're doing in the "'Do Something" pieces of code. You could even cycle through each sheet and test the value there:

Sub LookingFor()


Dim i As Integer, j As Integer, k As Integer, s As Integer
Dim sht As WorkSheet


For i = 1 To 12 'For month'
    For j = 1 To 31 'For day'
        For s = 1 To 4 'For sheet'
            Set sht = Sheets("Sheet" & CStr(s))
            For k = 3 To 700 'For range'
                If Day(sht.Cells(k, 3)) = i And Month(sht.Cells(k, 3)) = j Then
                    'Do something
                End If
            Next
        Next
    Next
Next


End Sub

WBD

With this code doesn't it 'do something' which is same? But I need do different formula in each sheet, for example in Sheet1 -> do add, Sheet2 -> do minus, Sheet3 -> do square root, and Sheet4 -> do divide..

 

[ukmikeb]

Hi

Can you explain with your meaning "frequency of the dates on each sheet"? Yes, they all in same year..

Yes, how many instances are there of a specific day?

As they are all in the same year you can just cycle through the days of the year.

If filtering the data and check for one day it can be work but I don't want to filtering each day and month..

You can filter for each day and if there is nothing found you can test for that.
And you can only work on the filtered cells for the days where data is present - It will save you looking at all 698 cells every time.

but I don't want to filtering each day and month..

IMHO I don't think you have any alternative but to filter every day of the year unless you use advanced filters to determine which days are present in each of the sheets!

I'm still not clear what processing occurs if a date is on the first sheet but is missing on one or more of the following sheets or is missing on the first sheet and is on one or more of the other sheets.
Your OP implies there could be some hierarchy.

hth

 

[nu_miniy]

Hi

Yes, how many instances are there of a specific day?

Every sheet have multiple data for each date 1 January to 31 December 2015

As they are all in the same year you can just cycle through the days of the year.

That's why in my code I put

For j = 1 To 31 'For looping day each month'

You can filter for each day and if there is nothing found you can test for that.
And you can only work on the filtered cells for the days where data is present - It will save you looking at all 698 cells every time.

I'll try your suggestion about filtering

I'm still not clear what processing occurs if a date is on the first sheet but is missing on one or more of the following sheets or is missing on the first sheet and is on one or more of the other sheets.

The flow actually based on priority sheet, in date 01-01-2015 Sheet1 I need to calculate items, update Stock Sheet, then move to Sheet2 with same date do calculate items, update Stock Sheet, so on until Sheet4 calculate for date 01-01-2015..
After Sheet4 calculate at date 01-01-2015, I'll move to date 02-01-2015 and I'll loop the sheet again from Sheet1 to Sheet 4 like what I explained before..
If I don't use nested loop the stock will get wrong calculation because I already try it manually..

Your OP implies there could be some hierarchy.

Emm what do you mean with OP and hierarchy?

 

[ukmikeb]

Hi

Thanks, it's a bit clearer now.

Perhaps you could adapt this block of code :-

 Dim dtestart As Date, dteend As Date
 Dim I As Date
 Dim NoofFiltrdRows As Long
 Dim rng As Range
 Dim Thisday As String
 
 dtestart = DateSerial(2015, 1, 1)
 dteend = DateSerial(2015, 12, 31)
 
 For I = dtestart To dteend Step 1
 Thisday = Format(I, "dd/mm/yyyy")
  Sheets("Sheet1").Select 
 Set rng = ActiveSheet.Range("C2:C100")
 rng.AutoFilter Field:=1, Criteria1:=I, Visibledropdown:=False
 NoofFiltrdRows = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
 
 ' Your Processing for Sheet1 goes here
 
 rng.AutoFilter
Next I

to suit your objectives.

You would need to copy the lines between

Sheets("Sheet1").Select

and

rng.AutoFilter

for the other sheets changing the sheet reference accordingly

hth