Posts by cscribner

    Re: Set Variable To Named Range

    Thank you, shg and daniel.c.

    I don't quite understand your instructions, and I'm pretty sure I've just been unclear. So here's a fuller view of what I'm trying to do.

    Say I want to have a drop down on my chart that lets you toggle between different students' grades. The chart is built to point to a static set of cells on a ChartData sheet, so when I change the drop-down, this static set should get replaced with a different set of data, based on what was selected in the drop-down.

    In this example, the drop-down is a named range called "student".

    If Not Intersect(Target, Range("student")) Is Nothing Then
            Dim chartScores As String, student as String
            dim Rng1 as Range
            student = Range("student")
            chartScores = student & "_Scores"
            Rng1 = Range(chartScores)
       End If

    My hope was that when I switched from "Craig" to "Scott" in the drop-down, Rng1 would switch from Range("Craig_Scores") to Range("Scott_Scores")

    Re: Range Copy & Paste Values Only

    THanks jamie, but I tried smacking that very text onto the destination range object, and I'm getting syntax errors:

    Rng1.Copy Rng2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Is it obvious to you what I'm doing wrong?

    if I have a variable vx = "abc", I want to call that range by its variable name:

    Range(vx) = Range("abc")

    But trying Range(vx) gives me fits in the code.

    This is because I have 50 different named ranges in the sheet, and based on user input, I'll know which one to use.

    Re: Test If Named Range Exists

    I'm sorry about that. I really did run a search for "Named Range Exists", but now that I look more carefully at the search results, the message you found showed up on the third page (number 53 out of 61 responses). I'm not used to something so clearly relevant being so far back in the results, but I promise to be more thorough next time.

    Thanks for your help--I really appreciate it![hr]*[/hr] Auto Merged Post;[dl]*[/dl]When I try the code you've given me, it only seems to work if the missing named ranges are looked for before the ones that exist. My macro looks like this:

    And even though Sections(3) and (4) don't exist, the checker tells me they do, and that their address is the same as the last one that was found: Sections(2), ie. Range("GHI"). But if I change the order and put the non-existent ranges first, the error code works like a charm:

    Dim Sections(4) As Variant
            Sections(4) = "ABC" 'This one exists
            Sections(3) = "DEF" 'This one exists
            Sections(2) = "GHI" 'This one exists
            Sections(1) = "JKL" 'This one DOES NOT exist
            Sections(0) = "MNO" 'This one DOES NOT exist

    I'm uploading the excel document for your reference. Does anybody know why this would be?

    Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.

    I've got a worksheet that I add a row to every day, right between the last date entry and the totals row at the bottom. On a similar worksheet, when I select a cell in the blank row between my last entry and the totals row, then Insert --> Row, all of my formulas on the row above are extended down to the new row. On this worksheet, they don't, and after I insert the row I have to drag down all of my formulas to the new row.

    Not a big deal, but it's a daily hassle that I didn't have to worry about on the other worksheet. Is there a format setting somewhere that controls this feature?

    Re: Automating Mail Merge

    Sorry Dave, I was confused by the forum names: Word vs. Excel & VBA. This is a VBA question in Word, so that's why I thought it was a toss up.

    In Word's VBA, I've recorded a macro of what I want to do: create a sheet of labels from a text document. When I try to rerun the macro, it tells me that one of the commands is not available, even though this command was generated by Word itself.

    Here is the line that it says can't work:


    And here is the context:

    Any insight/help would be appreciated!

    I'm trying to redim a multi-dimensional array, whilst preserving the values that are already there.

    This command works fine:

    Dim badEmails() As String

    and so does this:

    ReDim badEmails(0,1) As String

    but this one does not:

    ReDim Preserve badEmails(1,1) As String

    Do you know why it's telling me Subscript is Out of Range?

    I frequently use ubound to re-dimension dynamic arrays. But how do you find out the ubound of a specific dimension of an array?

    for example, with

    Dim Data(1 to 12, 1 to 2)

    what ubound command that would return 12, and what ubound command would return 2?