how to change For i - to refer to specific columns instead of rows

[Corey]

Without getting in the reasons and specifics the following part of the below
code "For i = 2 To 22" refered to data in rows 2 to 22.

But i have changed the way the data is stored, and it is now stored on a
single row, but not in adjacent columns.

The data is now in column 11,20,29 etc(every 9 columns)

Is there a way i could modify the "For i = " to refer to all values in the
single row specified columns instead of in the rows 2-22 as previously set?

Corey....


******************************************
Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False

If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(, 3).Text = ComboBox28.Text And
..Cells(myrow, 1).Offset(, 5).Text = ComboBox1.Text And .Cells(myrow,
1).Offset(0, 0).Value = ComboBox5.Text Then
For i = 2 To 22
If Cells(myrow, 1).Offset(, 11).Font.Strikethrough =
False And Cells(myrow, 1).Offset(, 11).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 1).Offset(, 11)
ComboBox6.List(ComboBox6.ListCount - 1, 1) = Cells(myrow, 13).Offset(,
11).Address
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
****************************************

 

[JLGWhiz]

Dim lastColumn As Long
lastColumn = '?
For i = 11 To lastColumn Step 9
'Code
Next

 

[JLGWhiz]

Of course i then is used as the column indicator instead of the row.

Cells(2, i)

 

[Corey]

Thanks for the reply, i now have:

Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False

If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim LastCell As Long
Dim lastColumn As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(, 3).Text = ComboBox28.Text And
..Cells(myrow, 1).Offset(, 5).Text = ComboBox1.Text And .Cells(myrow,
1).Value = ComboBox5.Value Then
For i = 11 To lastColumn Step 9
If Cells(myrow, 1).Offset(, i).Font.Strikethrough =
False And Cells(myrow, 1).Offset(, i).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 1).Offset(, i)
ComboBox6.List(ComboBox6.ListCount - 1, 1) = Cells(myrow, 1).Offset(,
i).Address
End If

Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub



But i get no values?

Did i miss something?

 

[JLGWhiz]

I think you missed the question mark in my psuedo code. You will need to
put a value for lastColumn. I used an arbitrary variable to show you how to
set or the For...Next statement because you did not supply that info in your
first posting and I cannot see your worksheet. I am surprised you did not
get a compile error or runtime error message. The variable lastColumn
should equate to the column number that contains your last data element for
the loop. You do not have to use a variable, you could just use the number
if it will be static.

 

[JLGWhiz]

Once you get the lastColumn fixed, then you probably want to change this:

For i = 11 To lastColumn Step 9
If Cells(myrow, 1).Offset(, i).Font.Strikethrough =
False And Cells(myrow, 1).Offset(, i).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 1).Offset(, i)
ComboBox6.List(ComboBox6.ListCount - 1, 1) = Cells(myrow, 1).Offset(,
i).Address
End If
Next i


To This:

For i = 11 To lastColumn Step 9
If Cells(myrow, 1).Offset(, i).Font.Strikethrough = _
False And Cells(myrow, 1).Offset(, i).Value <> "" Then
ComboBox6.AddItem Cells(myrow, i)
ComboBox6.List(ComboBox6.ListCount - 1, 1) = _
Cells(myrow,i).Address
End If

Next i

 

[Corey]

Thank you for the assistance JLGWhiz.

Perfect !

Ended up with the below.

******************************
Application.ScreenUpdating = False
If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim LastCell As Long
Dim lastColumn As Long
Dim myrow As Long
On Error Resume Next
lastColumn = Worksheets("InspectionData").Cells(Columns.Count,
"GJ").End(xlLeftToRight).Column
LastCell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(, 3).Value = ComboBox28.Value And
..Cells(myrow, 1).Offset(, 5).Value = ComboBox1.Value And Val(.Cells(myrow,
1).Value) = Val(ComboBox5.Value) Then
For i = 12 To lastColumn Step 9
If Cells(myrow, i).Font.Strikethrough = False And Cells(myrow, i).Value
<> "" Then
ComboBox6.AddItem .Cells(myrow, i)
ComboBox6.List(ComboBox6.ListCount - 1, 1) = Cells(myrow, i).Address
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True

******************************


Regards

Corey....