Solved How can I repeat a potion of a macro?

June 28, 2018 at 14:37:31
Specs: Windows 7
I need the numbering part of this macro to repeat until it finds an empty cell to the left of the active column.


Public Sub SelectRangeAndNumber()
Dim aRange As Range, i As Long, j As Long

Set aRange = Application.InputBox(prompt:="Enter Column", Type:=8)
    If aRange Is Nothing Then
    MsgBox "Operation Cancelled"
    Else
    aRange.Select
    End If

i = InputBox("Enter initial")
j = InputBox("Ticket number")
    
    With aRange
        .Value = i
        .AutoFill .Resize(j, 1), xlFillSeries
    End With
End Sub


Any and all help is appreciated.

Thank you,
JR

message edited by Herky13


See More: How can I repeat a potion of a macro?

Reply ↓  Report •

✔ Best Answer
June 29, 2018 at 10:36:23
re: "I'm not sure why it ran into an error when you tested it. It never runs more than once when I test it."

The code ran into an error because the first InputBox says "Enter Column" so that is what I did.

Since the InputBox instructions said "Enter Column" and the Type is 8 (a Range) I selected an entire Column. The code accepted that Input but then proceeded to fill the entire Column and wanted to keep going. That produced a Run-Time error once the code reached Row 1,048,576.

I'm not sure how a user (me) would know that "Enter Column" really means "Select A Single Cell".

Another problem with the code is that it does not allow to the user to gracefully Cancel out of the process. The code below shows how to handle the "Cancel" button for all 3 input boxes. It also deals with invalid entries, such as more than one cell being selected, text entries, etc.

As written, the code determines the last row with data based on the Column directly to the left of the cell chosen by the user. If that won't work, I'll need some more specifics. For now, test the code using that criteria.

Public Sub SelectRangeAndNumber()
Dim aRange As Range, i As Variant, j As Variant
On Error Resume Next

'Get cell from user, handle Cancel and invalid entries
get_Cell:
   Set aRange = Application.InputBox(prompt:="Select A Single Cell", Type:=8)

'Cancel clicked
     If Err.Number = 424 Then Exit Sub

'Too many cells selected
    If aRange.Cells.Count > 1 Then
      MsgBox "You Must Select a Single Cell or Click Cancel"
      GoTo get_Cell
    End If

'Get values from user, handle Cancel and invalid entries

get_i:
    i = Application.InputBox("Enter initial")
      If i = False Then Exit Sub
        If Not IsNumeric(i) Then
          MsgBox "You Must Enter A Number or Click Cancel"
          GoTo get_i
        End If

get_j:
    j = Application.InputBox("Enter initial")
      If j = False Then Exit Sub
        If Not IsNumeric(j) Then
          MsgBox "You Must Enter A Number or Click Cancel"
          GoTo get_j
      End If
      
'Determine last row with data in adjacent column (to left)
    lastRw = Cells(Rows.Count, aRange.Column - 1).End(xlUp).Row

'Set initial Start Row
    startRw = aRange.Row

'Insert groups of numbers
nxtGrp:
   NxtNum = i
    For nxtRw = startRw To startRw + j - 1
      If nxtRw >= lastRw + 1 Then
       MsgBox "Last Row Reached"
       Exit Sub
      End If
        Cells(nxtRw, aRange.Column) = NxtNum
        NxtNum = NxtNum + 1
    Next
    
'Set start row for next group
     startRw = startRw + j
     GoTo nxtGrp

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
June 28, 2018 at 14:51:45
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
June 28, 2018 at 14:59:42
Sorry about that, I hope the edited message is better.

Reply ↓  Report •

#3
June 28, 2018 at 18:58:28
Thanks for the edit.

Could you explain what this code is supposed to do? Keep in mind that we have no idea what your process is or what you are trying to accomplish.

What are you expecting the user to enter in the "Enter Initial" and "Ticket Number" InputBoxes?

I entered numbers such as Initial 5 and Ticket Number 9. All I got was a column full of 5's until the code errored out because it ran out of rows.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
June 28, 2018 at 19:19:44
My apologies, I should have been more specific in my original post.

The user first clicks on the cell where they will start the numbering.

Then the user enters the initial starting number of the tickets and the number of total tickets. The code increases the number by 1 in each cell of that column for X number of tickets.

What I am hoping to accomplish is to repeat that numbering sequence until it reaches the last row with data in the worksheet. So if I have 100 rows of data the number sequence of say 1-10 would repeat 10 times, stopping when it reached the 100th row.

Right now it only runs it one time and stops. I'm not sure why it ran into an error when you tested it. It never runs more than once when I test it.

message edited by Herky13


Reply ↓  Report •

#5
June 29, 2018 at 10:36:23
✔ Best Answer
re: "I'm not sure why it ran into an error when you tested it. It never runs more than once when I test it."

The code ran into an error because the first InputBox says "Enter Column" so that is what I did.

Since the InputBox instructions said "Enter Column" and the Type is 8 (a Range) I selected an entire Column. The code accepted that Input but then proceeded to fill the entire Column and wanted to keep going. That produced a Run-Time error once the code reached Row 1,048,576.

I'm not sure how a user (me) would know that "Enter Column" really means "Select A Single Cell".

Another problem with the code is that it does not allow to the user to gracefully Cancel out of the process. The code below shows how to handle the "Cancel" button for all 3 input boxes. It also deals with invalid entries, such as more than one cell being selected, text entries, etc.

As written, the code determines the last row with data based on the Column directly to the left of the cell chosen by the user. If that won't work, I'll need some more specifics. For now, test the code using that criteria.

Public Sub SelectRangeAndNumber()
Dim aRange As Range, i As Variant, j As Variant
On Error Resume Next

'Get cell from user, handle Cancel and invalid entries
get_Cell:
   Set aRange = Application.InputBox(prompt:="Select A Single Cell", Type:=8)

'Cancel clicked
     If Err.Number = 424 Then Exit Sub

'Too many cells selected
    If aRange.Cells.Count > 1 Then
      MsgBox "You Must Select a Single Cell or Click Cancel"
      GoTo get_Cell
    End If

'Get values from user, handle Cancel and invalid entries

get_i:
    i = Application.InputBox("Enter initial")
      If i = False Then Exit Sub
        If Not IsNumeric(i) Then
          MsgBox "You Must Enter A Number or Click Cancel"
          GoTo get_i
        End If

get_j:
    j = Application.InputBox("Enter initial")
      If j = False Then Exit Sub
        If Not IsNumeric(j) Then
          MsgBox "You Must Enter A Number or Click Cancel"
          GoTo get_j
      End If
      
'Determine last row with data in adjacent column (to left)
    lastRw = Cells(Rows.Count, aRange.Column - 1).End(xlUp).Row

'Set initial Start Row
    startRw = aRange.Row

'Insert groups of numbers
nxtGrp:
   NxtNum = i
    For nxtRw = startRw To startRw + j - 1
      If nxtRw >= lastRw + 1 Then
       MsgBox "Last Row Reached"
       Exit Sub
      End If
        Cells(nxtRw, aRange.Column) = NxtNum
        NxtNum = NxtNum + 1
    Next
    
'Set start row for next group
     startRw = startRw + j
     GoTo nxtGrp

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
July 2, 2018 at 12:50:49
This works perfectly! Thanks for all your help DerbyDad!

Reply ↓  Report •

Ask Question