IF Condition with "For" and "Next" Statement

[AngryBeast]

Hello,

I am little confused from my first days in VBA Programming and I don't know, how to continue with my exercise...

My goal is to generate an XML file using VBA code that will consist of data that is contained in more than 3 Excel sheets(TcC, TcA, TcO). I managed to write simple lines, but I missing some knowledge to bring my code on the end.

I would like to use the "for" and "next" statements, but my thinking is against the logic of Excel.

The situation I described below is an illustration. Can anyone tell me how could I write the correct code? I cannot imagine to use all 3 "Next" Statement at once.

Dim a As Integer
Dim b As Integer

Dim fsT As Object

Dim TcC As Worksheet
Dim TcA As Worksheet
Dim TcO As Worksheet

Set TcC = Worksheets("TcClass")
Set TcA = Worksheets("TcAttribute")
Set TcO = Worksheets("TcOperation")

For a = 6 To 200
For b = 6 To 200
For c = 6 To 200


    If TcC.Cells(6, 2).Value = "" Then Exit For     'If Cell(6,6) has no Value, then Exit
 
    If TcC.Cells(6, 2).Value <> "" Then
        fsT.WriteText "TcC.Cells(6,2).Value"         'Write down Value from TcC  ("Class1")
       
    If TcC.Cells(6, 2).Value = TcA.Cells(b, 1).Value Then
        fsT.WriteText "TcA.Cells(6,2).Value" 'Write Down (Class_Attribute01-A)
       
    If TcA.Cells(b, 1).Value = TcA.Cells(b+1, 1).Value Then
        fsT.WriteText "TCA.Cells(7,2).Value" ' write down (Class_Attribute_01-B)
 
    If TcA.Cells(7, 1).Value <> TcA.Cells(8, 1) Then
        fsT.WriteText "TcC.Cells(7,2).Value" 'write down Value (Class2)
       
    If TcC.Cells(7, 2).Value = TcA.Cells(b, 1).Value Then
        fsT.WriteText "TcA.Cells(b,2).Value" 'Write Down (Class_Attribute02)
       
    If TcA.Cells(b, 1).Value <> TcA.Cells(b+1, 1) Then
        fsT.WriteText "TcC.Cells(a+1,2).Value" 'write down Value (Class3)
   
    If TcC.Cells(a, 2).Value <> TcA.Cells(b, 1).Value Then
        fsT.WriteText "TcA.Cells(a,2).Value" 'Write Down Class4 ... etc.

    If TcC.Cells(a, 2).Value = "" And TcC.Cells(b, 1).Value = "" Then
        fsT.WriteText "TcO.Cells(c,1).Value" ' If in TcC and TcA Cell has any value, then Go to Worksheet TcO and write down first value from cell "Operation1"


    Next a
    Next b
    Next c

Thank you so much.

 

[Zot]

For each If you need End If, unless it is in single line
This is okay

If TcC.Cells(6, 2).Value = "" Then Exit For     'If Cell(6,6) has no Value, then Exit

This is no okay. Need to add End If

If TcC.Cells(6, 2).Value <> "" Then
        fsT.WriteText "TcC.Cells(6,2).Value"         'Write down Value from TcC  ("Class1")
End If  

For the For loop, it will loop like this:
a=6
b=6
c=6 and continue to loop until 200
b=7
c=6 and continue to loop until 200
b=8
c=6 and continue to loop until 200
..... continue until b=200. Only then a increment starts.
a=7
b=6
c=6 and continue to loop until 200
b=7
c=6 and continue to loop until 200
..... continue until b=200. Then another a increment starts.

You can imagine the rest. The text sequence will follow the loop sequence as you imagine

 

[Ezguy4u]

Angry, Maybe this will help. Here is a basic 3 x 3 array. Step thru it using f8 to see how it works.

Sub Build_Array1()
Dim NewArray(1 To 3, 1 To 3, 1 To 3) As Integer
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
D = 1
For A = 1 To 3
    For B = 1 To 3
        For C = 1 To 3
        
NewArray(A, B, C) = D
D = 1 + D
            Next C
        Next B
    Next A
    
D = 1

For A = 1 To 3
For B = 1 To 3
For C = 1 To 3

ActiveSheet.Rows(A).Columns(D).Value = NewArray(A, B, C)
D = D + 1
Next C

Next B
D = 1

Next A
End Sub