Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Creating Named Ranges in VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating Named Ranges in VBA

    hello everyone,
    I have sucessefuly tryed to create a dynamic name range that searching in another name range, FLT_ALN_Idx, for a given number, gives me a range with a calculated rows and columns.

    Code:
    =IF(
     NOT(IS.ERROR(MATCH(1;'A1F'!FLT_ALN_Idx;0)));
     OFFSET('A1F'!Pt1;MATCH(1;'A1F'!FLT_ALN_Idx;0)-1;0;COUNT('A1F'!FLT_Idx);11);
     OFFSET('A1F'!Pt2;0;0;COUNT('A1F'!FLT_Idx);11)
    )
    The formula its breaken into the IF statement so it can be better visible.
    the FLT_Idx its another named range
    Pt1 and Pt2 are names to cell references, starting point of the cell ranges

    i need 30 cell ranges like this.. being the last one like this

    Code:
    =IF(
     NOT(IS.ERROR(MATCH(30;'A1F'!FLT_ALN_Idx;0)));
     OFFSET('A1F'!Pt1;MATCH(30;'A1F'!FLT_ALN_Idx;0)-1;0;COUNT('A1F'!FLT_Idx);11);
     OFFSET('A1F'!Pt2;0;0;COUNT('A1F'!FLT_Idx);11)
    )
    and I need it in 3 diferent spreadsheets 'A1F', 'A2F' and 'A3F'
    so I made this macro

    Code:
    Sub CreateNames()
        Dim Cnt As Integer
        Dim sh As String
        Dim ONome As Variant
        On Error GoTo CreateNames_Erro
        sh = ActiveWorkbook.ActiveSheet.Name
        For Cnt = 1 To 30
            ONome = "IF(NOT(IS.ERROR(MATCH(" & CStr(Cnt) & ";'" & sh & "'!FLT_ALN_Idx;0)));OFFSET('" & sh & "'!Ponto1;MATCH(" & CStr(Cnt) & ";'" & sh & "'!FLT_ALN_Idx;0)-1;0;COUNT('" & sh & "'!FLT_Idx);11);OFFSET('" & sh & "'!Ponto2;0;0;COUNT('" & sh & "'!FLT_Idx);11))"
            ActiveWorkbook.ActiveSheet.Names.Add Name:="FLT_AL" & CStr(Format(Cnt, "00")), RefersTo:=ONome
        Next
        On Error GoTo 0
        MsgBox "All names were created"
        Exit Sub
    CreateNames_Erro:
        MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") on procedure CreateNames"
    End Sub
    That i run in each spreadsheet with a button and makes the 30 named ranges for each sheet

    Problems are
    First: i cant put a equal sign like this.. ONome = "=IF.... gives me an error in the RefersTo:=ONome
    Second : even if i sucessefuly make this work.. why cant i use the INDIRECT function to get his value

    example : if i use this formula : =INDEX(INDIRECT("A1F!FLT_AL"&TEXT($C10;"00"));$F10;I$211)+INDEX(INDIRECT("A2F!FLT_AL"&TEXT($C10;"00"));$F10;I$211)+INDEX(INDIRECT("A3F!FLT_AL"&TEXT($C10;"00"));$F10;I$211)
    gives me a #REF error
    BUT if i use well defined ranges to the named ranges like 'A1F'!$F$10:$P$18, 'A2F'!$F$10:$P$18, 'A3F'!$F$10:$P$18 ... it works fine.

    i need a macro that creates this names ranges.. but looking at FLT_ALN_Idx (or the range of it). Any ideas? tyvm

  • #2
    Re: Creating Named Ranges in VBA

    What is this IS.ERROR ?

    Code:
    IF(NOT(IS.ERROR
    Never seen that before.
    Ger

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Re: Creating Named Ranges in VBA

      oh sorry Ger Palante i made a "almost" literal translattion of the name functions from my language to english....
      the original formulas are:


      Code:
      =SE(NO(.ERRO(CORRESP(1;'A1F'!FLT_ALN_Idx;0)));DESLOCAMENTO('A1F'!Pt1;CORRESP(1;'A1F'!FLT_ALN_Idx;0)-1;0;CONTAR('A1F'!FLT_Idx);11);DESLOCAMENTO('A1F'!Pt2;0;0;CONTAR('A1F'!FLT_Idx);11))
      Code:
      Sub CriarNomesFaltas()
      
          Dim Cnt As Integer
          Dim sh As String
          Dim ONome As Variant
      
          On Error GoTo CriarNomesFaltas_Erro
          sh = ActiveWorkbook.ActiveSheet.Name
      
          For Cnt = 1 To 30
              ONome = "SE(NO(.ERRO(CORRESP(" & CStr(Cnt) & ";'" & sh & "'!FLT_ALN_Idx;0)));DESLOCAMENTO('" & sh & "'!Ponto1;CORRESP(" & CStr(Cnt) & ";'" & sh & "'!FLT_ALN_Idx;0)-1;0;CONTAR('" & sh & "'!FLT_Idx);11);DESLOCAMENTO('" & sh & "'!Ponto2;0;0;CONTAR('" & sh & "'!FLT_Idx);11))"
              ActiveWorkbook.ActiveSheet.Names.Add Name:="FLT_AL" & CStr(Format(Cnt, "00")), RefersToR1C1:=ONome
          Next
      
          On Error GoTo 0
          MsgBox "Todos os nomes foram criados"
          Exit Sub
      
      CriarNomesFaltas_Erro:
      
          MsgBox "Error " & Err.Number & " (" & Err.Description & _
          ") no procedimento CriarNomesFaltas"
      
      End Sub
      if u understand them better....

      Comment


      • #4
        Re: Creating Named Ranges in VBA

        sorry I asked LOL

        Well, does ".ERRO" work if used correctly in an Excel formula? What version of Excel are you using anyway?

        Ger

        Check out our new reputation system. Click on the "star" under the post!
        _______________________________________________

        There are 10 types of people in the world. Those that understand Binary and those that dont.

        Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

        The BEST Lookup function of all time

        Dynamic Named Ranges are your bestest friend

        _______________________________________________

        Comment


        • #5
          Re: Creating Named Ranges in VBA

          hi again, well since i really need this solved, i tryed to make a model of what i have untill now,
          the office is 2010

          The objective is that in GAF show the sum of the same are names in the other sheets...
          So if the formulas r used as they r right now.. i have an error !REF, but if i try to delete them and rebuild them again in VBA i cant put the "=" sign or it gives an error, but i dont do that.. it wont make it as a formula. its so annoing

          Comment


          • #6
            Re: Creating Named Ranges in VBA

            plz can some one give me a hint a soluttion? or at least tell me how to make the named ranges in a simple way like A1F!$F$4:$P$11 for example.. but depending on the number in the FLT_ALN_Idx range... ty

            Comment


            • #7
              Re: Creating Named Ranges in VBA

              Hi Mitocas. OK, I honestly dont fully understand the named range you trying to create - its rather complicated and your attachment above doesnt seem to open. I was simply trying to explain that if you cant successfully create a formula for the reference in the name manager in Excel to do (or refer to) the region/range that you need, then I cant honestly see how creating in VBA will be any different. I suspect - that formula you are constructing is INVALID as a reference as an Excel formula. So that result of the reference that you construct in VBA MUST be a valid Excel formula. I dont think that your attempt above is a valid excel Formula. That is why I focused on "if.error", or ".ERRO"... that doesnt look like valid syntax, regardless of which language we talk about I'll get back to that in a second.

              So in answer to your question - how to create a named range with an "Equals sign", which includes references to sheets, which also includes a variable name... Well, this fairly basic code should get you started...

              Code:
              Public Sub add_name()
              
              Dim iloop As Integer
              Dim reference As String
              
              For iloop = 1 To 4
                  reference = "=sheet" & iloop & "!$A$1:$D$" & iloop
                  Names.Add "Gers_Sheet" & iloop, reference
              Next iloop
              
              End Sub
              This code constructs 4 named ranges called Gers_Sheet1, Gers_Sheet3, Gers_Sheet4, Gers_Sheet4. These reference the range Sheet1!$A$1:$D1, Sheet2!$A$1:$D2, Sheet3$A$1:$D3, Sheet4!$A$1:$D4 respectively.

              I construct the reference in a string - there's no need to do this by the way, it just helps with debugging complex Excel formulas/references. My point here is that if your Synthax is incorrect / invalid for the excel formula / reference being built, the compiler wont compile it. You should Debug by adding a watch to the string that you are constructing... if necessary, copy/paste the reference into the Name Manager or copy /paste the reference into an excel cell.... it should NOT return #name or #ref errors. If it does, then the string you are constructing in VBA is invalid. I hope that makes sense, I apologise if I have insulted your intelligence.

              Given that your reference for named range is rather long and complex, I'm hedging my bet on a Excel Syntax error or an invalid reference. Instead of going for Gold on the first shot with a huge lengthy reference in the named range, start with a string variable and build up the reference in the string... start at the very basics in the reference and build up the reference one element at a time making sure the compiler can compile it. Its tedious but effective. Watch how the string is "Constructed" in the watch window - and occassionally add them to the name manager or an excel cell to ensure the string is still valid.

              When I have been adding named ranges before there are two things that "caught" me for which you should watch out for:
              1) The name of the range needs to be valid. The name must begin with a letter or underscore, it can NOT contain spaces, the name can not contain invalid characters, the name can not be duplicate of any other Excel function/method etc.
              2) When referencing Worksheets with spaces, it can be tricky to code because the worksheet name must be surrounded by single quote marks ('). So say i have a sheet called "Sheet Blah 1" then my code above for consturcting the reference would be:

              Code:
              reference = "='sheet blah " & iloop & "'!$A$1:$D$" & iloop
              I hope this helps get you started.

              Lastly - you could of course found a legitimate bug in Excel. Never rule that out either

              Ger

              Check out our new reputation system. Click on the "star" under the post!
              _______________________________________________

              There are 10 types of people in the world. Those that understand Binary and those that dont.

              Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

              The BEST Lookup function of all time

              Dynamic Named Ranges are your bestest friend

              _______________________________________________

              Comment


              • #8
                Re: Creating Named Ranges in VBA

                Hi ger, thank you so much for anwser, i hope that you can see this file now: Exemplo.xlsm

                First of all, no by all means, u never did insulted me in any way I welcome every help that i can get.
                Second the problem, it self:
                the way i saw it its like this: i did the hard work of finding a formula that did exactly what i wanted: Find a number in a variable location of a named range, and use that location as a start of a area of a variable high (of rows) but constant height (of 11 columns) and give that area to a named range... thats what this formula does.

                Code:
                =IF(NOT(IF.ERROR(MATCH(1; 'A1F'!FLT_ALN_Idx;0)));OFFSET('A1F'!Pt1;MATCH(1;'A1F'!FLT_ALN_Idx;0)-1;0;COUNT('A1F'!FLT_Idx);11);OFFSET('A1F'!Pt2;0;0;COUNT('A1F'!FLT_Idx);11)
                but as u can see in the attachment (i hope u can see... ) using the name range in a formula gives-me a !REF error.. everytime i use INDIRECT function with a calculated range.. gives me that... so basicaly.. i was tryting go back to basics.. and find a vba code that does exactly what the formula does... create named ranges depending on a position of a specific, non consecutive, number (1 to 30...) in a column.
                But im not versed in VBA thats why i needed help to create the code, serching the net and the forum i could make one that created the named ranges.. but i cant make one that does what the formula does.

                Your aproch, i understood what it does.. but the areas have to be diferent.. in the next table the areas have to be height E:J same hight as the same number in colum C (the hight its allways the same), so Area 1 = $E$2:$J$6, Area 2 = $E$12:$J16, Area 3 = $E$7:$J$11
                C D E F G H I J
                1
                1
                1
                1
                1
                3
                3
                3
                3
                3
                2
                2
                2
                2
                2
                plz.. a simple vba code to make this ty

                Comment


                • #9
                  Re: Creating Named Ranges in VBA

                  Hi Mitocas,

                  I had a look at your workbook. Nice

                  Well - would it be fair to say that the only reason you need a VBA solution, is because you are currently getting a #REF error in the formulas in the cells where you are using your current named ranges?

                  And equally fair to say that if you got the named ranges to work in Excel, then you wouldnt need a VBA solution.

                  Because writing a VBA solution for something that you cant get to work in Excel is going to be tricky.

                  The reason (The ONLY reason, so far as I can see) that you are getting #REF errors with your current excel formula's is that the scope of the named ranges that you have created is defined for specific sheets. NONE of the named ranges are "visible" to the "GAF" sheet.

                  Again, sorry if you already knew this, but I cant take any chances here

                  So in terms of trying to write VBA for the named ranges that you have (which are currently technically correct), you will STILL get the same problem you currently have - the scope of the named ranges, is (currently) only for certain sheets.

                  What I will say is that when your formulas and/or named ranges become THAT complicated, you should reconsider your approach to the solution to the problem. Sometimes problems are just too complicated to use Named Ranges for. You might be able to piece together an Excel formula or named range, but it becomes so monstrously complicated, that it becomes impossible to maintain and will start to slow your workbook down. In cases like that you need to disregard Excel formulas / named ranges and come up with a entire VBA solution.

                  Thats what I recommend you do in this situation - I cant really fix your current VBA as the formula really is that complex, and I am discouraged by the fact that your Excel formulas dont work. Like I say, when an Excel formula wont work, it will be harder to code. Sometimes we become myopic and biased towards a solution that may not be a best fit. Sometimes its useful to stand back and consider alternatives.

                  Ger

                  Check out our new reputation system. Click on the "star" under the post!
                  _______________________________________________

                  There are 10 types of people in the world. Those that understand Binary and those that dont.

                  Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                  The BEST Lookup function of all time

                  Dynamic Named Ranges are your bestest friend

                  _______________________________________________

                  Comment


                  • #10
                    Re: Creating Named Ranges in VBA

                    Oh Ger
                    well sorry to disagree with you but.. i see two good reasons to do that vba macro...
                    first if u change in my map the named ranges with formulas with the actual ranges.. u will see that the formulas work...
                    second.. the calculating time decreases dramaticaly specially in a previous version that i used before.. with this formulas in the named ranges and the SELECT function in the "GAF" sheet wich was a bit complicated.. and my computer freesed for like 1 minut everytime i wanted to insert/delete cels in any of the other sheets were the named ranges r...
                    last one.. its the simplest way to do it... u see the numbers in the A1F .. A2F and A3F changes... they arent always the same.. in the same place.. thats why i need to get a more dynamic version then just put one by one the ranges in the corresponding named range. thats why i need the VBA code... to have much less work...
                    i hope that some one plz help me with it. ty

                    Comment


                    • #11
                      Re: Creating Named Ranges in VBA

                      I just want to reiterate this, because you may have misunderstood it.

                      first if u change in my map the named ranges with formulas with the actual ranges.. u will see that the formulas work...
                      This is only because the existing named ranges that you have defined are NOT visible on the GAF sheet with the formulas. So yes, if you change the named ranges in the formulas to actual references, of course it will work, however if you increase the SCOPE of the named range, to "Workbook" they (the named ranges) will become visible on every worksheet and become available to all formulas in all cells in each sheet. You can see this if you go into the name manager and look under column "Scope".

                      I think you may also have misunderstood my comments on VBA, I was simply saying that if you create a formula in Excel and it gives a #REF error, then trying to recreate that exact same formula in VBA will result in the exact same error.

                      I dont believe that the VBA code is the issue, I believe it yours underlying defined named ranges not being visible to your formulas in the GAF sheet.

                      I was also hinting, though its your call of course, to disregard the named ranges completely (I have nothing against named ranges by the way), and just write a pure VBA macro to do whatever it is you are trying to achieve with Named Ranges....

                      Regards,
                      Ger

                      Check out our new reputation system. Click on the "star" under the post!
                      _______________________________________________

                      There are 10 types of people in the world. Those that understand Binary and those that dont.

                      Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                      The BEST Lookup function of all time

                      Dynamic Named Ranges are your bestest friend

                      _______________________________________________

                      Comment


                      • #12
                        Re: Creating Named Ranges in VBA

                        INDIRECT won't work with calculated ranges.
                        In the attachment, I see no named ranges so the INDIRECT is failing universaly.

                        Can you describe, in words, what range you want FLT_AL100 to refer to?
                        Similarly for 200-600?

                        Comment


                        • #13
                          Re: Creating Named Ranges in VBA

                          Hi Mike, well i reviwed my file.. had some mistakes there .. this is the correct file: Exemplo.xlsm the "names manager" has all the names i need.

                          And .. there r no 100 max its 30.. or FLT_AL30, and all "A#H" sheets, have to have FLT_AL01 up to FLT_AL30.. and the ranges have allways the same area, i mean.. rows x columns .. as u can see in my Example the ranges are in folowing areas ones above the others IF the number is found in the FLT_ALN_Idx named range, if its not found a dummy area is selected (one with out any data on it).
                          any other question plz ask.
                          Last edited by mitocas; January 30th, 2012, 10:31. Reason: some errors in examole file

                          Comment


                          • #14
                            Re: Creating Named Ranges in VBA

                            Looking deeper at your worksheet. It looks like (on sheet A1F)
                            You have working areas in columns E:O and a Free Area in Q4:AA11

                            You want FLT_AL01 to refer to the rows of E:O that are marked by a 1 in B:B.
                            If no such rows exists, you want FLT_AL01 to refer to the Free Area.

                            And similarly for FLT_AL02 through FLT_AL06

                            If that is the case and IF the markers in column B are always sorted ascending, even though some may be missing, you could use a Names structure like this for each sheet.

                            Name:FreeArea RefersTo: =A1F!$Q$4:$AA$11
                            Name:IndexColumn RefersTo: =A1F!$B:$B
                            Name:MainArea RefersTo: =A1F!$E:$O

                            Name:WorkArea1
                            RefersTo: =INDEX(A1F!MainArea,MATCH(1,A1F!IndexColumn,0),1):INDEX(A1F!MainArea,MATCH(1,A1F!IndexColumn),COLUMNS(A1F!MainArea))

                            Name:FLT_AL01
                            RefersTo: =IFERROR(A1F!WorkArea1,A1F!FreeArea)

                            ...

                            Name:WorkArea6
                            RefersTo: =INDEX(A1F!MainArea,MATCH(6,A1F!IndexColumn,0),1):INDEX(A1F!MainArea,MATCH(6,A1F!IndexColumn),COLUMNS(A1F!MainArea))

                            Name:FLT_AL06
                            RefersTo: =IFERROR(A1F!WorkArea6,A1F!FreeArea)

                            But your problem is that INDIRECT doesn't work with calculated Names like these.

                            To get around that you can select a cell in row4 of GAF and define 3 more names. (Note the relative reference in GAF!$B4)

                            Name: oneRange
                            RefersTo: =CHOOSE(GAF!$B4,A1F!FLT_AL01, A1F!FLT_AL02, A1F!FLT_AL03, A1F!FLT_AL04, A1F!FLT_AL05, A1F!FLT_AL06)

                            Name: twoRange
                            RefersTo: =CHOOSE(GAF!$B4,A2F!FLT_AL01, A2F!FLT_AL02, A2F!FLT_AL03, A2F!FLT_AL04, A2F!FLT_AL05, A2F!FLT_AL06)

                            Name: threeRange
                            RefersTo: =CHOOSE(GAF!$B4,A3F!FLT_AL01, A3F!FLT_AL02, A3F!FLT_AL03, A3F!FLT_AL04, A3F!FLT_AL05, A3F!FLT_AL06)

                            oneRange looks to column B to see which range it should return from sheet A1F
                            twoRange returns the range from A2F which has the same name, etc.

                            Then your formula in GAF!E4 becomes =INDEX(oneRange,$D4,E$1)+INDEX(twoRange,$D4,E$1)+INDEX(threeRange,$D4,E$1)

                            I've adjusted the formulas etc. in the attached.
                            The sub MakeNames makes the sheet scoped names, but not the names oneRange, twoRange, threeRange.
                            The sub ClearAllNames clears all the names in all the sheets.
                            Attached Files

                            Comment


                            • #15
                              Re: Creating Named Ranges in VBA

                              Here's code that makes the names OneRange, TwoRange and ThreeRange

                              Code:
                              With ThisWorkbook
                                  .Names.Add Name:="OneRange", RefersToR1C1:="=CHOOSE(GAF!RC2, A1F!FLT_AL01, A1F!FLT_AL02, A1F!FLT_AL03, A1F!FLT_AL04, A1F!FLT_AL05, A1F!FLT_AL06)"
                                  .Names.Add Name:="TwoRange", RefersToR1C1:="=CHOOSE(GAF!RC2, A2F!FLT_AL01, A2F!FLT_AL02, A2F!FLT_AL03, A2F!FLT_AL04, A2F!FLT_AL05, A2F!FLT_AL06)"
                                  .Names.Add Name:="ThreeRange", RefersToR1C1:="=CHOOSE(GAF!RC2, A3F!FLT_AL01, A3F!FLT_AL02, A3F!FLT_AL03, A3F!FLT_AL04, A3F!FLT_AL05, A3F!FLT_AL06)"
                              End With

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X