How to use a formula that references a column from an input box selection in VBA?-CodePudding

I have a file that comes in from new customers and different vendors sporadically. There is not set standard to the files. The number of columns vary per customer, but there is always a customer first name column, last name column, and ID column with leading zeros somewhere in the file. Often times, the leading zeros are dropped and so the file needs to have that column reformatted and I normally manually have to go through and create a separate column to do a "=TEXT(F2,"00000000")" if the field happens to be in column F for this customer.
Is there any way I can create a VBA script to get user input from an input box to point to the column letter/number and then set that range as a variable where I can then use the input in my formula? Or a better easier way? I haven't been able to find anyone doing this in my search so far (maybe because there is a better way I am not thinking about?).
Note: I understand I am doing the formula as a string, so it is reading the variable that way - not sure how else to bring that range or even a selected cell address in to reference in the formula. I would like it to be something like
First Name | Last Name | ID
John | Doe | 123
Then the script adds the column ID Re-Formatted:
First Name | Last Name | ID | ID Re-Formatted
John | Doe | 123 |
But I can't figure out how to Have the user select the ID column so it can be used to autofill and format with the leading zeros in the reformatted column to look like this after doing the "=TEXT(rng,"00000000")" :
First Name | Last Name | ID | ID Re-Formatted
John | Doe | 123 | 00000123
Thanks for any pointers and help!

Sub Test()
Dim rng As Range
Set ws = ActiveSheet

'Add a column for the ID to be reformatted with leading zeros
With ws
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Cells(1, LastCol   1).Value = "ID Re-Formatted"
    .Cells(LastCol   1).Select
    .Cells(LastCol   1).EntireColumn.AutoFit
End With

'Get A Column Address From The User for the Original ID Column
 On Error Resume Next
   Set rng = Application.InputBox( _
     Title:="ID Column Selection", _
    Prompt:="Select column containing ID", _
    Type:=8)
  On Error GoTo 0

 Range(Cells(2, LastCol   1), Cells(LastRow, LastCol   1)).Formula = "=TEXT(rng,""00000000000"")"

End Sub

CodePudding user response：

To get the Range object into your formula you just need to use concatenation. Stop the String at "=TEXT(", then do your concatenation of the Range object using an & either side. You then just need another " before the rest of the formula. It would look like the following.

Range(Cells(2, LastCol 1), Cells(LastRow, LastCol 1)).Formula = "=TEXT(" & rng & ",""00000000000"")"