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
****************************************

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

"Corey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> ****************************************
>

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

Cells(2, i)


"Corey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> ****************************************
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Of course i then is used as the column indicator instead of the row.
>
> Cells(2, i)
>
>
> "Corey" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> ****************************************
>>
>
>

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.


"Corey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Of course i then is used as the column indicator instead of the row.
>>
>> Cells(2, i)
>>
>>
>> "Corey" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>> ****************************************
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Of course i then is used as the column indicator instead of the row.
>>
>> Cells(2, i)
>>
>>
>> "Corey" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>> ****************************************
>>>
>>
>>
>
>

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....

"JLGWhiz" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> Of course i then is used as the column indicator instead of the row.
>>>
>>> Cells(2, i)
>>>
>>>
>>> "Corey" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>> ****************************************
>>>>
>>>
>>>
>>
>>
>
>