Stop a Do/ Loop at a variable column

yroc81

New Member
Joined
Jun 18, 2012
Messages
4
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi yroc81,

You want to always start from column F, right?

If so, see if this minor alteration works:


Code:
[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]
 
Last edited:
Upvote 0
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?
 
Upvote 0
Hi yroc81,

Maybe:


Code:
[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]
 
Upvote 0
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.
 
Upvote 0
Hi yroc81,

Maybe:

Code:
[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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,046
Messages
6,105,590
Members
447,972
Latest member
carrieann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top