Help to ask user to input a starting number in a column and another question to ask for all the same number for another column.

[rickadams]

Can I Please have help with a 2 part VBA question.
The macro I have already checks for how many rows and columns that are being used on the open sheet.
The last 2 things I need help with are.

After the macro moves the data around we need to Ask the user to input a starting number so it can autofill a column starting at A2 to the last known row.
They are asked to input at the prompt a number Lets say then number 400,
the macro will then fill 400,401,402,403,etc all the way down to the last used row.

Then prompt the user to input a number to fill in starting at B2 with the same number in column B starting at B2 all the way down to the last known row.

Then continue with the existing macro which is already saving the updated sheet.
Thank you in advance

 

[My Aswer Is This]

You said:

Lets say then number 400,
the macro will then fill 400,401,402,403,etc all the way down to the last used row.

So in this case you want to enter 400 in A2 and 401 in A3

But you said to the last used row.
The last used row in what column?
Do you mean last used row in column A?

So you already have data in column A and you want to over write this data?
If not I would think the last used row in column A would be A2

Please explain more.

I'm normally am not a fan of providing one little tidbit of code to add to a script but if this is what you want then I will try to help

It may be more helpful if you told me what your ultimate project is trying to do.
But it seems you only want this one little piece of code.

Try this:

Sub My_Script()
'Modified 9/15/2018 9:57 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim ans As Long
ans = InputBox("Enter number to start with for column A") - 2
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
        Cells(i, 1).Value = ans + i
    Next
ans = InputBox("Enter number to start with for column B") - 2
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To Lastrow
        Cells(i, 2).Value = ans + i
    Next
Application.ScreenUpdating = True
End Sub

​

 

[Peter_SSs]

The macro I have already checks for how many rows ... are being used

Assuming that is held in a variable called LastRow then try this with a copy of your data.

Dim StartNum As Variant

StartNum = InputBox("Enter start number for column A")
If IsNumeric(StartNum) Then
  Range("A2").Value = Val(StartNum)
  Range("A2:A" & LastRow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
End If
StartNum = InputBox("Enter start number for column B")
If IsNumeric(StartNum) Then
  Range("B2").Value = Val(StartNum)
  Range("B2:B" & LastRow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
End If

 

[rickadams]

Assuming that is held in a variable called LastRow then try this with a copy of your data.

Dim StartNum As Variant

StartNum = InputBox("Enter start number for column A")
If IsNumeric(StartNum) Then
  Range("A2").Value = Val(StartNum)
  Range("A2:A" & LastRow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
End If
StartNum = InputBox("Enter start number for column B")
If IsNumeric(StartNum) Then
  Range("B2").Value = Val(StartNum)
  Range("B2:B" & LastRow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=0    ' <------ 
End If

Peter,

Thank you !!!!
This worked perfectly except I changed the Step:=1 to Step:=0 (Because in column B I needed all the same number)
Now it does exactly what I needed...
Thank you Again!

 

[Peter_SSs]

You're welcome.

This worked perfectly except I changed the Step:=1 to Step:=0 (Because in column B I needed all the same number)

In that case there is a much simpler solution for the second part.

StartNum = InputBox("Enter start number for column B")
<del>If IsNumeric(StartNum) Then
  Range("B2").Value = Val(StartNum)
  Range("B2:B" & LastRow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
End If</del>
Range("B2:B" & LastRow).Value = StartNum