InputBox for Cell range

maguirej

New Member
Joined
Sep 9, 2002
Messages
13
:rolleyes:
This is probably easy, but I am not strong in VBA. I want to set up an InputBox for the user to enter a range of cells. I want to automate a copy / paste / value function for columns (Jan - Dec) to replace array formulas with the actuals once calculated to stop our excel 2000 from zeroing these out at times.

What is the correct syntax? I have this and it's not working:

C = InputBox("Which range to copy paste value?", "", "C24:I134")

Range.Select = C


Any advice is greatly appreciated!!!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Example:

Code:
Sub Test()
    Dim Rng As Range
    On Error GoTo Cancelled
    Set Rng = Application.InputBox(Prompt:="Which range to copy paste value", Title:="Remove Formulas", Type:=8)
    Rng.Copy
    Rng.PasteSpecial xlPasteValues
Cancelled:
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,501
Members
412,670
Latest member
Khin Zaw Htwe
Top