Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Creating Named Ranges in VBA

  1. #1
    Join Date
    23rd October 2011
    Posts
    10

    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.

    VB:
    =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

    VB:
    =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

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,498

    Re: Creating Named Ranges in VBA

    What is this IS.ERROR ?

    VB:
    If(Not(IS.ERROR 
    
    
    Never seen that before.
    Ger
    _______________________________________________
    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

    _______________________________________________

  3. #3
    Join Date
    23rd October 2011
    Posts
    10

    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:


    VB:
    =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))
    
    
    VB:
    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....

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,498

    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
    _______________________________________________
    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

    _______________________________________________

  5. #5
    Join Date
    23rd October 2011
    Posts
    10

    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, Attachment 43487
    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd October 2011
    Posts
    10

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,498

    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...

    VB:
    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:

    VB:
    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
    _______________________________________________
    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

    _______________________________________________

  8. #8
    Join Date
    23rd October 2011
    Posts
    10

    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.

    VB:
    =If(Not(IF.ERROR(MATCH([B]1;[/B] [COLOR=#006400] 'A1F'!FLT_ALN_Idx;0)));OFFSET('A1F'!Pt1;MATCH([B]1[/B];'A1F'!FLT_ALN_Idx;0)-1;0;COUNT('A1F'!FLT_Idx);11);OFFSET('A1F'!Pt2;0;0;COUNT('A1F'!FLT_Idx);11)[/COLOR]
    
    
    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,498

    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
    _______________________________________________
    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

    _______________________________________________

  10. #10
    Join Date
    23rd October 2011
    Posts
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. using named Ranges.
    By Excel_Idiot in forum EXCEL HELP
    Replies: 7
    Last Post: July 17th, 2006, 21:52
  2. Named ranges and creating in Vlookup formula
    By tshafer333 in forum EXCEL HELP
    Replies: 2
    Last Post: July 13th, 2004, 03:01
  3. named ranges
    By Epidemic in forum EXCEL HELP
    Replies: 1
    Last Post: December 10th, 2003, 23:05
  4. Named Ranges
    By Smur in forum EXCEL HELP
    Replies: 3
    Last Post: May 16th, 2003, 07:17
  5. Creating View out of different Named Ranges
    By yjoshi in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: March 21st, 2003, 16:30

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno