CODEWITHSUNDEEP
vbaexcel
Se7sNator
Se7sNator

Reputation: 81

For loop to skip specific rows

I'm writing a vba code to generate Avaya CMS script that is assigining skills with specific priorities to each staff.

Column A has the skill numbers, Column B has the skill priorities.

Excel Sample:

A       |       B
999     |       1
163     |       3
164     |       16
394     |       1

And this is how CMS code should look like:

SetArr(1,1)= 999    
SetArr(1,2)= 1  
SetArr(1,3)= 0
SetArr(1,4)= 0  
SetArr(2,1)= 163    
SetArr(2,2)= 3
SetArr(2,3)= 0
SetArr(2,4)= 0  
SetArr(3,1)= 164    
SetArr(3,2)= 16
SetArr(3,3)= 0
SetArr(3,4)= 0  
SetArr(4,1)= 394    
SetArr(4,2)= 1  
SetArr(4,3)= 0

First line of each iteration has the skill number, while the second line has the priority. BUT..looking into SetArr(X,Y) X should increase every 4 iterations and Y should be counting from 1-4 and resetting.

my problem is the for loop stops after the first 4 iterations.

This is what I have so far..

Dim x As Integer
Dim y As Integer
y = 0
x = 0
For i = 1 To 500 Step 4
x = x + 1
y = y + 1
Range("B" & i).Value = "SetArr(" & x & ",1)" & "= " & Range("A" & y).Value
For t = 1 To 4
If Range("B" & t).Value <> "" Then
Else
Range("B" & t).Value = "SetArr(" & x & "," & t & ")= 0"
End If
Next t
Next i

Upvotes: 2

Views: 559

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

It is possible to achieve this result by using following formula for data posted above.

Assuming that data is in grid $A$2:$D$5 following formula can be put in any cell:

=CONCATENATE("SetArr(",CEILING(ROWS($A$1:A1)/4,1),",",MOD(ROWS($A$1:A1)-1,4)+1,")= ",INDEX($A$2:$D$5,CEILING(ROWS($A$1:A1)/4,1),MOD(ROWS($A$1:A1)-1,4)+1)+0)

And then copy down as much needed.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

In a matrix case like this one you simply need a nested loop for the columns and for the rows to make it really understandable:

Public Sub TestMe()

    Dim myCol   As Long
    Dim myRow   As Long

    For myRow = 1 To 10 '500
        For myCol = 1 To 4
            Dim strOutput As String
            strOutput = "SetArr(" & myCol & "," & myRow & ")= " & Cells(myRow, myCol)
            If Cells(myRow, myCol) = vbNullString Then strOutput = _
                                            "SetArr(" & myCol & "," & myRow & ")= 0"
            Debug.Print strOutput
        Next myCol
    Next myRow

End Sub

And this is the output in the immediate window:

SetArr(1,1)= 999
SetArr(2,1)= 1
SetArr(3,1)= 0
SetArr(4,1)= 0
SetArr(1,2)= 163
SetArr(2,2)= 3
SetArr(3,2)= 0
SetArr(4,2)= 0
SetArr(1,3)= 164

Upvotes: 2

Related Questions