Loop with columns in vba

[kelly mort]

Private Sub CmbName_Change()
    With Sheets("Sheet1")
        Dim i As Long, j As Long
            For i = 5 To 9
            Select Case .CmbQuest.ListIndex
            Case 0: .Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .[I5:I15], i - 4) / Application.CountIf(.[D5:D15], .CmbName)
            Case 1: .Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .[J5:J15], i - 4) / Application.CountIf(.[D5:D15], .CmbName)
            Case 2: .Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .[K5:K15], i - 4) / Application.CountIf(.[D5:D15], .CmbName)
            Case 3: .Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .[L5:L15], i - 4) / Application.CountIf(.[D5:D15], .CmbName)
            Case 4: .Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .[M5:M15], i - 4) / Application.CountIf(.[D5:D15], .CmbName)
            Case 5: .Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .[N5:N15], i - 4) / Application.CountIf(.[D5:D15], .CmbName)
            Case 6: .Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .[O5:O15], i - 4) / Application.CountIf(.[D5:D15], .CmbName)
            End Select
            Next i
    End With
End Sub

I wanted to avoid the case statement and use a loop to switch the columns but it keeps telling me type mismatch .




Example

.Cells(2, i + 4) = Application.CountIfs(.[D5:D15], .CmbName, .Range(.Cells(5, .CmbQuest.ListIndex + 9), .Cells(15, .CmbQuest.ListIndex + 9)), i - 4) / Application.CountIf(.[D5:D15], .CmbName)

How can I bypass that error?

 

[Yongle]

Perhaps (untested) ...

Private Sub CmbName_Change()
With Sheets("Sheet1")
    Dim i As Long, j As Long
        For i = 5 To 9
            .Cells(2, i + 4) = Application.CountIfs(.Range("D5:D15"), .CmbName, .Range("I5:I15").Offset(, .CmbQuest.ListIndex), i - 4) / Application.CountIf(.Range("D5:D15"), .CmbName)
        Next i
    End With
End Sub

 

[kelly mort]

Perhaps (untested) ...

Private Sub CmbName_Change()
With Sheets("Sheet1")
    Dim i As Long, j As Long
        For i = 5 To 9
            .Cells(2, i + 4) = Application.CountIfs(.Range("D5:D15"), .CmbName, .Range("I5:I15").Offset(, .CmbQuest.ListIndex), i - 4) / Application.CountIf(.Range("D5:D15"), .CmbName)
        Next i
    End With
End Sub

Wow! !!


Unbelievable.



Edit:


I have a problem with this part:

.... i  - 4 .....

I want it switch like this :

If i is 5 the output is 5
If i is 6 the output is 4

Down till i is 9 where out put it 1

So I used another variable j

Like

For j = 0 To 8 Step 2


Then that " i - 4 " became "i +j "
Hoping to achieve results but it does not seem to work as expected


I am very grateful

 

[Yongle]

You should be able to work this out for yourself
- it is a logic problem, not VBA or Excel
- I noticed in post#1 the pattern "column increased by 1 when ListIndex increased by 1"

Write out what ranges should be lin full (as in post#1) and look for a pattern and use Debug.Print to understand what VBA is doing when testing your code

Debug.Print
Learn to use Debug.Print and test the ranges etc in the code
Debug.Print prints the results to the immendiate window in VBA
If you cannot see the iimmediate window, make it visible with {CTRL} G

Try running the code I gave you with this minor adjustment and see what happens to the range

Private Sub CmbName_Change()
With Sheets("Sheet1")
    Dim i As Long, j As Long
        For i = 5 To 9
            .Cells(2, i + 4) = Application.CountIfs(.Range("D5:D15"), .CmbName, .Range("I5:I15").Offset(, .CmbQuest.ListIndex), i - 4) / Application.CountIf(.Range("D5:D15"), .CmbName)
[COLOR=#ff0000]Debug.Print [COLOR=#ff0000].CmbQuest.ListIndex, [/COLOR].Range("I5:I15").Offset(, .CmbQuest.ListIndex).Adress(0, 0)[/COLOR]
        Next i
    End With
End Sub

If you need further help please provide:
- details similar to post#1 (the ranges you want and what makes them change)
- and thelatest code you have tried but is not working

Someone can then explain why your method is not working

 

[kelly mort]

Okay cool.