R1C1 notation in VBA code

[Fred Holmes]

How can I modify the following code so that the response to the
InputBox can be entered in R1C1 notation? The workbook is set to use
R1C1 notation in formulae in cells.

Sub Input_Range_Select()
Dim SR As String
SR = InputBox(Prompt:="What is the Range to be Selected?")
Range(SR).Select
End Sub

A1:J400 works as a response.

I would like to be able to type in R1C1:R400C10, which does not work.

TIA

Fred Holmes

 

[ryguy7272]

Sub Macro1()
Range(Cells(1, 1), Cells(400, 10)).Select
End Sub


Ryan---

 

[Fred Holmes]

Thanks. This is a help, but I'll have to do a bunch of string
manipulation to process the string: "R1C1:R400C10" into the arguments
of the Cells() function. Doable, but I was hoping for something more
straightforward. I guess I could use four inputboxes to input the
four numbers.

 

[Tim Zych]

Sub Input_Range_Select()
Dim SR As String
SR = InputBox(Prompt:="What is the Range to be Selected?")
If SR = "" Then Exit Sub
SR = Application.ConvertFormula(SR, xlR1C1, xlA1)
Range(SR).Select
End Sub

There is also the Application.Inputbox which is more geared to ranges.

Sub Input_Range_Select2()
Dim SR As Range
On Error Resume Next
Set SR = Application.InputBox(Prompt:="What is the Range to be
Selected?", Type:=8)
On Error GoTo 0
If Not SR Is Nothing Then
SR.Select
End If
End Sub

 

[Dave Peterson]

I wouldn't use an inputbox to get the address of a range. As a user, I'd rather
point and click using the mouse.

If you think that's worth pursuing...

dim myRng as range
set myrng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select a range", type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel
'what should happen?
exit sub
end if

'in case it's on another sheet
application.goto myrng, scroll:=true

 

[Fred Holmes]

I wouldn't use an inputbox to get the address of a range. As a user, I'd rather
point and click using the mouse.

I would agree with you if the range is small, but if the range is
large, e.g. five screenfulls wide and tens of screenfulls tall, how do
you do it with a mouse in a manner that isn't very tedious? Scrolling
multiple screenfuls with a mouse quickly to arrive at a specific cell
destination doesn't work well for me. In my general case, both the
upper left and the lower right corners of the range to be selected are
way off of the current viewing screen.

Fred Holmes

 

[Dave Peterson]

If the data is contiguous, I can use the End and down/right/left/up keys. And I
can use the ctrl-end, ctrl-home keys, too.

I could even select the range first, then use that as the default:

Dim myRng As Range
Dim myAddr As String

If Application.ReferenceStyle = xlA1 Then
myAddr = Selection.Address(False, False)
Else
myAddr = Selection.Address(True, True, xlR1C1)
End If

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="hi", Default:=myAddr, Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "nothing"
Else
MsgBox myRng.Address(0, 0)
End If

Or just ask in two different application.inputbox's--topleft and botright
cells. Maybe even a small userform for that.

 

[Fred Holmes]

The data is not contiguous, and I'm not always looking for cells that
are "data boundaries." Thanks for the code you supply below. It
looks interesting.

One of the general applications of this process is setting the print
range in a "checkbook register" where the set of lines/rows to be
printed is entirely arbitrary, depending on exactly what the printout
is good for. While the columns to be printed are generally a standard
set, I'd like to keep them in the range address to be typed in, so
that the process remains general.

I use the R1C1 notation just because I happen to like it (holdover
from Multiplan), and because if I want to check whether the formulae
for calculating the book balance or the bank balance have become
corrupted, all of the formulae are literally identical. If I "forget"
and do a cut/paste operation, the formulas change. I suppose I could
try a locked worksheet and see if that would help.

A while back I was looking for keystrokes to go to (select) the last
non-empty cell in a specific column (column of the active cell) when
the data was not contiguous. I found that I had to write a macro to
do it. (I think that was asked in the misc forum, not programming.)

Many thanks for your help.

Fred Holmes