S
SteveDB1
Hi all.
Based on previous postings, I'm attempting to expand an existing macro that
worked great--- until I added the following as a precursor. It get's hung up
on this one, and stops.
Dim MyRng1 As Range
'-------------------
'This portion does not work, and kills the routine from continuing further.
'see if you can get it to do what you want (commenting to myself).
Set MyRng1 = Nothing
On Error Resume Next
Set MyRng1 = Application.InputBox(Prompt:="Select range for APN Tally
Header_ location", Type:=2).Areas(1).Columns(6)
'I interchaged the columns() with rows() and it didn't change anything.
'the _ is not a code break I input to separate two lines. I did it here to
show
'a continuation of the single line.
'I.e., I've already found that VBA doesn't like a break in text like this.
'MyRng1.formula does not work places nothing in the cell range chosen.
MyRng1.Formula = "APN Tally"
' activecell.formular1c1 enters the desired text
'ActiveCell.FormulaR1C1 = "APN Tally"
'this is to format the range chosen.
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
On Error GoTo 0
'MsgBox "" & MyRng1 & "is your output"
If MyRng1 Is Nothing Then
Exit Sub 'user hit cancel.
End If
I guess I have two questions-- I'm sure more could be asked, but I'll just
start with what appears obvious to me here.
1- this routine returns nothing, why? I get that I'm setting MyRng1 to =
nothing, and then re-setting it to be the range to be that of my choosing.
I'd read that the type can be a combination of values-- 1, 2, 4, 8, 16, 32,
64, or some variation by adding say, 2 + 8 to get a mixture of those two data
types.
While I'm only showing a type:=2 in my application.inputbox here, I just
tried 8 + 2, and 2 + 8 with no effect, or change.
2- I did the above code based on my understanding of previously working
code. I.e., the previous code worked exactly as written. It allowed me to
choose a range, and inset a specifically coded formula. Since the "formula" I
wanted to input was far less complicated, I thought it'd be a no-brainer.
i.e., MyRng1.formula = "APN Tally"
That however does not work. I did find that activecell.formulaR1C1 works.
The problems that I'm having are the following.
A- the routine will not merge the cell range chosen.
B- It will not actually choose the range I set in my application.inputbox()
(it inputs the APN Tally in a box previously picked by random-- I just
"parked" there).
C- the routine just ceases operation at this point with no continuation
beyond-- in spite of the fact there is a long series of elements yet to be
completed still.
D- I've commented out the error routine thinking that it was the cause of my
dilema but that didn't change anything.
Please help.
Thank you.
Based on previous postings, I'm attempting to expand an existing macro that
worked great--- until I added the following as a precursor. It get's hung up
on this one, and stops.
Dim MyRng1 As Range
'-------------------
'This portion does not work, and kills the routine from continuing further.
'see if you can get it to do what you want (commenting to myself).
Set MyRng1 = Nothing
On Error Resume Next
Set MyRng1 = Application.InputBox(Prompt:="Select range for APN Tally
Header_ location", Type:=2).Areas(1).Columns(6)
'I interchaged the columns() with rows() and it didn't change anything.
'the _ is not a code break I input to separate two lines. I did it here to
show
'a continuation of the single line.
'I.e., I've already found that VBA doesn't like a break in text like this.
'MyRng1.formula does not work places nothing in the cell range chosen.
MyRng1.Formula = "APN Tally"
' activecell.formular1c1 enters the desired text
'ActiveCell.FormulaR1C1 = "APN Tally"
'this is to format the range chosen.
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
On Error GoTo 0
'MsgBox "" & MyRng1 & "is your output"
If MyRng1 Is Nothing Then
Exit Sub 'user hit cancel.
End If
I guess I have two questions-- I'm sure more could be asked, but I'll just
start with what appears obvious to me here.
1- this routine returns nothing, why? I get that I'm setting MyRng1 to =
nothing, and then re-setting it to be the range to be that of my choosing.
I'd read that the type can be a combination of values-- 1, 2, 4, 8, 16, 32,
64, or some variation by adding say, 2 + 8 to get a mixture of those two data
types.
While I'm only showing a type:=2 in my application.inputbox here, I just
tried 8 + 2, and 2 + 8 with no effect, or change.
2- I did the above code based on my understanding of previously working
code. I.e., the previous code worked exactly as written. It allowed me to
choose a range, and inset a specifically coded formula. Since the "formula" I
wanted to input was far less complicated, I thought it'd be a no-brainer.
i.e., MyRng1.formula = "APN Tally"
That however does not work. I did find that activecell.formulaR1C1 works.
The problems that I'm having are the following.
A- the routine will not merge the cell range chosen.
B- It will not actually choose the range I set in my application.inputbox()
(it inputs the APN Tally in a box previously picked by random-- I just
"parked" there).
C- the routine just ceases operation at this point with no continuation
beyond-- in spite of the fact there is a long series of elements yet to be
completed still.
D- I've commented out the error routine thinking that it was the cause of my
dilema but that didn't change anything.
Please help.
Thank you.