Stop a Do/ Loop at a variable column

[yroc81]

I am trying to use the following code to insert values into the input boxes on a page. I am trying to insert the values into the "input" cells as if I were reading a book. So from left to right. Right now I have hardcoded a column to the far right with "X". Is there any way to stop the Do and move to the next row and then repeat until all the input boxes within a range are filled? The range changes with each time I would run the Macro, so I don't always know the last column or last row. So I use LastCol, LastRow to figure those out. The code below will move me to F3 from F2 but I need to continue down column F until the LastRow variable is met.

Any help or guidance would greatly be appreciated.The red code is where I need the most help.

Dim LastCol As String
LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

Dim LastRow As Long
With Worksheets("Layout Numbering")
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With

Dim LastRowA As Long
With Worksheets("Layout Numbering")
LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Range("AZ1").Select
ActiveCell.FormulaR1C1 = "X"
Selection.AutoFill Destination:=Range("AZ1:AZ" & LastRow), Type:=xlFillDefault

Dim iCol As Integer 'Scratch- Column Number

Range("F2").Select

For iCol = 0 To LastRowA - 1
Do
Selection.Offset(0, 1).Select
Loop Until Selection.Style = "Input" Or ActiveCell = "X"
If Selection.Style = "Input" Then
ActiveCell.FormulaR1C1 = iCol
Else: Range("F" & Cells(Rows.Count, "F").End(xlUp).Row + 1).Offset(1, 0).Select
End If
Next iCol

End Sub

 

[xladept]

Hi yroc81,

You want to always start from column F, right?

If so, see if this minor alteration works:

[B][SIZE=2][FONT=arial][COLOR=#ff0000]For iCol = 0 To LastRowA - 1
        Do
            Selection.Offset(0, 1).Select
        Loop Until Selection.Style = "Input" Or ActiveCell = "X"
            If Selection.Style = "Input" Then
            ActiveCell.FormulaR1C1 = iCol
[/COLOR][/FONT][/SIZE][/B][B][SIZE=2][FONT=arial][COLOR=#ff0000]End If[/COLOR][/FONT][/SIZE][/B][B][SIZE=2][FONT=arial][COLOR=#ff0000]
Range("F" & Cells(Rows.Count, "F").End(xlUp).Row + 1).Offset(1, 0).Select
    Next iCol[/COLOR][/FONT][/SIZE][/B]

 

[yroc81]

Thanks for the quick response, I do need to always start from Column F, just change starting cells within that column. I tried what you suggested but I need the Range selection to stay inside the If because there are other "input"boxes within each row. Once it hits the first "input" box and fills it with the iCol value it jumps right back out to the same F cell it already was in. I think where it is getting hung up is the Cells(Rows.Count, "F") part of the syntax. Is there a way to do this without using a loop?

 

[xladept]

Hi yroc81,

Maybe:

[B][SIZE=2][FONT=arial][COLOR=#ff0000]For iCol = 0 To LastRowA - 1
         Do             Selection.Offset(0, 1).Select         Loop Until Selection.Style = "Input" Or ActiveCell = "X"
             If Selection.Style = "Input" Then             ActiveCell.FormulaR1C1 = iCol [/COLOR][/FONT][/SIZE][/B][B][SIZE=2][FONT=arial][COLOR=#ff0000]
End If[/COLOR][/FONT][/SIZE][/B][B][SIZE=2][FONT=arial][COLOR=#ff0000] 
Range("F" & ActiveCell.Row).Offset(1, 0).Select
     Next iCol[/COLOR][/FONT][/SIZE][/B]

 

[yroc81]

Yes that worked, thank you. Now I just have to figure out how to get it to recognize that its at the end of the range.

 

[xladept]

Hi yroc81,

Maybe:

[B][SIZE=2][FONT=arial][COLOR=#ff0000]For iCol = 0 To LastRowA - 1          
Do             Selection.Offset(0, 1).Select         Loop Until Selection.Style = "Input" Or ActiveCell = "X"              
If Selection.Style = "Input" Then             
ActiveCell.FormulaR1C1 = iCol [/COLOR][/FONT][/SIZE][/B][B][SIZE=2][FONT=arial][COLOR=#ff0000] 
End If[/COLOR][/FONT][/SIZE][/B][B][SIZE=2][FONT=arial][COLOR=#ff0000]  
Range("F" & ActiveCell.Row).Offset(1, 0).Select
If ActiveCell="" Then Exit sub
      Next iCol[/COLOR][/FONT][/SIZE][/B]

 

[yroc81]

Thank you for all your help so far. Here is my final code to fill the cells across the columns based on the way the cell is formatted.help complete the loop. The only problem I am running into now is that if the LastCol is not also an input box then my variable(iCol) increases by one. Can you think of any way to stay in the For/Next? When it hits the Next iCol is when the variable increases.


Dim iCol As Integer 'Scratch- Column Number
For iCol = 0 To LastRowA - 1
Do
Selection.Offset(0, 1).Select
Loop Until Selection.Style = "Input" Or ActiveCell.Column = LastCol
If Selection.Style = "Input" Then
ActiveCell.FormulaR1C1 = iCol
Else: Range("F" & ActiveCell.Row).Offset(1, 0).Select
End If
If ActiveCell.Column = LastCol Then
Range("F" & ActiveCell.Row).Offset(1, 0).Select
End If
If Range("G" & LastRow + 1) Then Exit Sub
Next iCol