Nested For Loop + If Statements Error

[cinarbe]

Hey Guys!
I am currently a new member to the forum, but long I have been getting help from the community. It feels finally very nice to be an official part of the group


I came across a problem today while I was trying to establish a VBA code to do the following:


I have 12 sheets of detailed records, each comprises of some SAP outputs (13 columns: year/month/exact date/x/x/x/definition/x/x/process definition/financial value/x/x; with x's being irrelevant).
I am trying to filter them through their "month" and its financial value if it is above a limit stated at the first (homepage) worksheet.


I tried to establish a nested for loop (dual) and implement a conditionality within to check if "month" and "financial value>Blabla": and search for all existing worksheets in the workbook. Simultaneously, through the use of a row counter, the suitable data will be posted on the homepage worksheet with relevant headlines.


I am already getting some nicely sorted headlines but no relevant data are being published in the homepage worksheet.

Sub ShowRecords()
Dim month, i, a, b As Integer
Dim finValue As Double


'following state the minimum fin. value limit and the month to be queried.
month = Worksheets(1).Cells(5, 4)
finValue= Worksheets(1).Cells(7, 8)


'homepage worksheet row counter
i = 10


Worksheets(1).Select
'navigating through all possible wsheets
For a = 1 To (ActiveWorkbook.Worksheets.Count - 1)
        
            'publishing the wsheet headline in homepage
            Worksheets(1).Cells(i, 3) = Worksheets(a + 1).Cells(1, 7)
            i = i + 1
            'publishing the wsheet table headers (relevants) in homepage
            Worksheets(1).Cells(i, 3) = Worksheets(a + 1).Cells(3, 1)
            Worksheets(1).Cells(i, 4) = Worksheets(a + 1).Cells(3, 2)
            Worksheets(1).Cells(i, 5) = Worksheets(a + 1).Cells(3, 3)
            Worksheets(1).Cells(i, 6) = Worksheets(a + 1).Cells(3, 7)
            Worksheets(1).Cells(i, 7) = Worksheets(a + 1).Cells(3, 10)
            Worksheets(1).Cells(i, 8) = Worksheets(a + 1).Cells(3, 11)
            i = i + 1
                       
'tried to establish a loop consisting of step equal to nonblank cells in active wsheet with the conditionality
            
For b = 4 To ((Worksheets(a + 1).Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count) + 2) 
    If Cells(b, 2) = month And Cells(b, 11) >= finValue Then
        'publishing the relevant records' values in homepage wsheet
        Worksheets(1).Cells(i, 3) = Worksheets(a + 1).Cells(b, 1)
        Worksheets(1).Cells(i, 4) = Worksheets(a + 1).Cells(b, 2)
        Worksheets(1).Cells(i, 5) = Worksheets(a + 1).Cells(b, 3)
        Worksheets(1).Cells(i, 6) = Worksheets(a + 1).Cells(b, 7)
        Worksheets(1).Cells(i, 7) = Worksheets(a + 1).Cells(b, 10)
        Worksheets(1).Cells(i, 8) = Worksheets(a + 1).Cells(b, 11)
        i = i + 1
        End If
Next b


i = i + 1


Next a


Worksheets(1).Select


End Sub

As I have stated before, the code above results with properly placed headlines and table headers (thanks to i=i+1), but no relevant data from wsheets. Also the wsheets contain records compliant with the finValue and month variables.
I have tried every possible nested loop structure (do-while etc.) but had no better results. I guess the problem lies within my noobness :D
I am looking forward to getting needed help from you experts !


Thank you sincerely,
Cinarbe

Edit: The question is also posted on:
http://answers.microsoft.com/en-us/o...7-196cfb0f63dd
http://www.mrexcel.com/forum/excel-q...ml#post3307086

 

[Rosen]

I am a huge proponent of explicit referencing for cases such as these. Your reference of Cells(b, 2) and Cells(b, 11) are refences to the active worksheet, regardess of where you wish to pull data from. While I personally would use the Sheets reference and not the Worksheets reference as your code does not validate what workbook's Sheets collection you are looking at (Worksheets grabs the active workbooks Sheets collection, not necessarily ThisWorkbook's Sheets collection), adjusting the reference to Worksheets(a + 1).Cells(b, 2) and Worksheets(a + 1).Cells(b, 11) respectively should solve your immediate problem.

 

[cinarbe]

Dear Rosen,
First of all I would like to thank you for your kind help.
I have also tried fixing the references all the way to addresses starting with Workbook("name") but yet could not find a solution. The code still published the headlines and table headers to worksheet 1.

I am a huge proponent of explicit referencing for cases such as these. Your reference of Cells(b, 2) and Cells(b, 11) are refences to the active worksheet, regardess of where you wish to pull data from. While I personally would use the Sheets reference and not the Worksheets reference as your code does not validate what workbook's Sheets collection you are looking at (Worksheets grabs the active workbooks Sheets collection, not necessarily ThisWorkbook's Sheets collection), adjusting the reference to Worksheets(a + 1).Cells(b, 2) and Worksheets(a + 1).Cells(b, 11) respectively should solve your immediate problem.

 

[Rosen]

I have reviewed the code and the only reason I can see why you aren't seeing data being filled in is if the conditions of the if statement are not met. You might want to try outputting your conditions and review manually like so:

Sub ShowRecords()
    
    Dim month, i, a, b As Integer
    Dim finValue As Double
    Dim review As String
    'following state the minimum fin. value limit and the month to be queried.
    month = Sheets(1).Cells(5, 4).Value
    finValue = Sheets(1).Cells(7, 8).Value
    'homepage worksheet row counter
    i = 10
    Sheets(1).Select
    'navigating through all possible wsheets
    For a = 1 To (ThisWorkbook.Sheets.Count - 1)
        'publishing the wsheet headline in homepage
        Sheets(1).Cells(i, 3).Value = Sheets(a + 1).Cells(1, 7).Value
        i = i + 1
        'publishing the wsheet table headers (relevants) in homepage
        Sheets(1).Cells(i, 3).Value = Sheets(a + 1).Cells(3, 1).Value
        Sheets(1).Cells(i, 4).Value = Sheets(a + 1).Cells(3, 2).Value
        Sheets(1).Cells(i, 5).Value = Sheets(a + 1).Cells(3, 3).Value
        Sheets(1).Cells(i, 6).Value = Sheets(a + 1).Cells(3, 7).Value
        Sheets(1).Cells(i, 7).Value = Sheets(a + 1).Cells(3, 10).Value
        Sheets(1).Cells(i, 8).Value = Sheets(a + 1).Cells(3, 11).Value
        i = i + 1
            
        'tried to establish a loop consisting of step equal to nonblank cells in active wsheet with the conditionality
        For b = 4 To ((Sheets(a + 1).Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count) + 2) Step 1
            review = review & "Sheets(" & a + 1 & ").Cells(" & b & ", 2) is " & Sheets(a + 1).Cells(b, 2).Value _
                     & " ?= " & month & " && " & "Sheets(" & a + 1 & ").Cells(" & b & ", 11) is " & _
                     Sheets(a + 1).Cells(b, 11).Value & " ?>= " & finValue & vbNewLine
            If Sheets(a + 1).Cells(b, 2).Value = month And Sheets(a + 1).Cells(b, 11).Value >= finValue Then
                'publishing the relevant records' values in homepage wsheet
                Sheets(1).Cells(i, 3).Value = Sheets(a + 1).Cells(b, 1).Value
                Sheets(1).Cells(i, 4).Value = Sheets(a + 1).Cells(b, 2).Value
                Sheets(1).Cells(i, 5).Value = Sheets(a + 1).Cells(b, 3).Value
                Sheets(1).Cells(i, 6).Value = Sheets(a + 1).Cells(b, 7).Value
                Sheets(1).Cells(i, 7).Value = Sheets(a + 1).Cells(b, 10).Value
                Sheets(1).Cells(i, 8).Value = Sheets(a + 1).Cells(b, 11).Value
                i = i + 1
            End If
        Next b
        i = i + 1
    Next a
    
    ' write out the review.
    Dim oFS As Object, oTS As Object
    Set oFS = CreateObject("FileSystemObject")
    Set oTS = oFS.OpenTextFile("C:\Review.txt", 2, True)
    oTS.Write review
    oTS.Close
    Set oFS = Nothing
    Set oTS = Nothing
    
    Sheets(1).Select
End Sub

Note: I also made some reference changes which you can feel free to ignore.

 

[cinarbe]

Mr. Rosen,
I would like to thank you sincerely for your kind help.
I have solved my problem, which was just an annoying formatting error:
I had already formatted the relevant columns as Numbers but the problem was prevailing. So, as me being a noob , I coded something to create an additional column which multiplies the values and pastes over it without any format problems. As soon as I noticed this "cheap" problem, I was actually a bit pissed
Thank you again!