Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing a range dynamically
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing a range dynamically
Cancel my request.
I got it to work. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
choosing a cell from a range | Excel Discussion (Misc queries) | |||
choosing the a cell from a range | Excel Discussion (Misc queries) | |||
Dynamically set a range? | Excel Worksheet Functions | |||
Dynamically obtaining a range from another range | Excel Programming | |||
Name a range dynamically | Excel Programming |