How to enter a variable value decided by the user on vba?

Question

I have to do a calculation on using several excel sheets and I need the user to enter the number of lines except that this number varies each year so I want this variable to be variable and entered by the user who will use the macro. Unfortunately I can't find a way to do it.

Range("B2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[7],RIGHT(RC[-1]))"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B95145"), Type:=xlFillDefault
Range("B2:B95145").Select
Range("J1").Select

for example, when I want to perform a calculation I want the B2:B95145 to be variable, the numbers that must be entered before the macro is launched.

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, May 15 '19 at 08:58
Okay thank you very much! I'll read it. Do you have any idea about my problem? — Guimove, May 15 '19 at 09:12
So your first row in the range will always be `B2`? and the last row is the one the user will provide? Where is this row number? is it provided in a sheet or do you have an `Input` dialog box capturing the number? — Zac, May 15 '19 at 09:15
Yes, my first line will always be B2 but the last line I filled it in myself and I change myself every time in the code. I don't know how to do an Input dialog box and I don't see how to do it because I have several calculation lines with the lines to fill in. — Guimove, May 15 '19 at 09:21

Pᴇʜ · Accepted Answer · 2019-05-15T09:25:40.527

You can automatically find the last used row in column A for example using

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

So this code should fill as many rows in column B as there is data in column A

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") 'define your sheet name

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row    

ws.Range("B2").FormulaR1C1 = "=CONCATENATE(RC[7],RIGHT(RC[-1]))"
ws.Range("B2").AutoFill Destination:=ws.Range("B2:B" & LastRow), Type:=xlFillDefault

or even just write the formula without using Autofill into all cells directly:

ws.Range("B2:B" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],RIGHT(RC[-1]))"

If you have another formula eg in column C then just add it like

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") 'define your sheet name

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row    

ws.Range("B2:B" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],RIGHT(RC[-1]))"
ws.Range("C2:C" & LastRow).FormulaR1C1 = 'your formula here

Or even, `ws.Range("B2:B" & LastRow).FormulaR1C1 = "=CONCATENATE(RC[7],RIGHT(RC[-1]))"` right? — Damian, May 15 '19 at 09:22
Thank you Peh and Damian! I'll do that, it's a very good idea. When there are several calculation lines to be filled in, I do this for all the lines? — Guimove, May 15 '19 at 09:23

How to enter a variable value decided by the user on vba?

1 Answers1