Using InputBox to input cell reference

[Kay Gee]

Hi all,

Pretty new to VBA here. I have a Macro (below) that has a hardcoded cell reference ('BB'!A3), but I want to delete that and be able to input whatever cell reference needed when I'm running the Macro for different worksheets. This is the hardcoded version:

Sub Test1()

Dim i As Integer
i = Application.InputBox("Confirm the number of days the resource worked in this period")
For i = 1 To i
        
        Cells(i, 2).Select
        Selection.Formula = "=[B]'BB'!A3[/B]"
        Cells(i, 3).Select
        Selection.Value = "8"
        Cells(i, 6).Select
        Selection.Value = Format(Cells(i, 1).Value, "mm/dd/yyyy")
        Selection.NumberFormat = "dd\/mm\/yyyy"
        Cells(i, 15).Select
        Selection.Formula = "=TRIM(RIGHT(SUBSTITUTE([B]'BB'!A3[/B],"" "", REPT("" "",100)),100))"
        Cells(i, 1).Select
        Selection.Value = "ApBiBo" & Cells(i, 15).Value & Cells(i, 1).Value
        Cells(i, 15).Value = ""
Next i

End Sub

1. I've been experimenting with InputBox, but apparently because the cell reference is used in a couple of formulae as you can see above, it doesn't work.
2. I was getting an error that went away when I declared the cell variable as a Variant. Is that the correct type?
3. Please note that the above code doesn't show my attempts with InputBox for the cell reference (the "i" variable is separate).

I'd appreciate any insight!

 

[Micron]

You can use the input box and pass the selected range to a variable, then use the variable wherever your hard coded range is.

Dim InputRng As Range

On Error GoTo errHandler
Application.DisplayAlerts = False
Set InputRng = Application.InputBox("COPY RANGE:", Type:=8)

InputRng.Address would be in the format of $F$4:$F$5 so no idea if that matters to your code. Methinks not.
BTW, you don't have to select cells or ranges in order to set values or formula in them.

EDIT - apply as in
InputRng.Formula = "=" & rng.Address (I hope - will have to test but am running out of time to edit)

EDIT 2 - it works

 

[Michael M]

In addition to comments by @Micron your code could be shortened

Sub Test1()
Dim i As Integer
i = Application.InputBox("Confirm the number of days the resource worked in this period")
For i = 1 To i
        Cells(i, 2).Formula = "='BB'!A3"
        Cells(i, 3).Value = 8
        Cells(i, 6).Value = Format(Cells(i, 1).Value, "dd\/mm\/yyyy")
        Cells(i, 15).Formula = "=TRIM(RIGHT(SUBSTITUTE('BB'!A3,"""" """", REPT("""" """",100)),100))"
        Cells(i, 1).Value = "ApBiBo" & Cells(i, 15).Value & Cells(i, 1).Value
Next i
End Sub

 

[Kay Gee]

Thanks. Re: EDIT 2: do you mean I should replace

Cells(i, 15).Formula = "=TRIM(RIGHT(SUBSTITUTE(InputRng,"" "", REPT("" "",100)),100))"

with

InputRang.Formula = "=TRIM(RIGHT(SUBSTITUTE(InputRng,"" "", REPT("" "",100)),100))" & Rng.Address

I'm getting a type mismatch error. But I'm pretty sure my interpretation of your EDIT 2 is not correct.

 

[Micron]

I meant to substitute the .Address property value of the chosen range (which I passed to InputRng variable). So more like
Formula = "=TRIM(RIGHT(SUBSTITUTE(InputRng.Address,"" "", REPT("" "",100)),100))"

Bold is where you had hard coded 'BB'!A3. Gotta tell you though that my grasp of most formula functions is very limited, so I have little clue about what that does, thus I might be out to lunch.
EDIT - I also don't see how you can select a whole column (BB) and A3 cell with an input box. Is it possible? Or is BB the sheet name perhaps?

 

[Kay Gee]

I meant to substitute the .Address property value of the chosen range (which I passed to InputRng variable). So more like
Formula = "=TRIM(RIGHT(SUBSTITUTE(InputRng.Address,"" "", REPT("" "",100)),100))"

Bold is where you had hard coded 'BB'!A3. Gotta tell you though that my grasp of most formula functions is very limited, so I have little clue about what that does, thus I might be out to lunch.
EDIT - I also don't see how you can select a whole column (BB) and A3 cell with an input box. Is it possible? Or is BB the sheet name perhaps?

Thanks. I get a type mismatch error with your formulation.

Yes, that's the sheet name.