i to = column in Formula

Discussion in 'Excel Programming' started by L. Howard, Apr 16, 2014.

  1. L. Howard

    L. Howard Guest

    How do I get i to show in the formula as column 1, then 2 then 3 etc. for the five drop downs in A to E? Once the i is inside the formula " "'s it is just a plain letter i.

    Sub FiveDD()

    Dim i As Long
    Range("A1").Select
    For i = 1 To 5
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)"
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
    ActiveCell.Offset(0, 1).Select
    End With
    Next
    End Sub

    Thanks.
    Howard
     
    L. Howard, Apr 16, 2014
    #1
    1. Advertisements

  2. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Tue, 15 Apr 2014 21:33:56 -0700 (PDT) schrieb L. Howard:

    > How do I get i to show in the formula as column 1, then 2 then 3 etc. for the five drop downs in A to E? Once the i is inside the formula " "'s it is just a plain letter i.


    if your columns have the same number of values and you don't have to
    COUNTIF for each column this is all you need:

    With Range("A1:E1")
    With .Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
    Operator:= _
    xlBetween,
    Formula1:="=OFFSET($A$2,0,Column()-1,COUNTA($A$2:$A$200))"
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
    End With
    End With


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 16, 2014
    #2
    1. Advertisements

  3. L. Howard

    L. Howard Guest


    >
    >
    > if your columns have the same number of values and you don't have to
    >
    > COUNTIF for each column this is all you need:
    >
    >
    >
    > With Range("A1:E1")
    >
    > With .Validation
    >
    > .Delete
    >
    > .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
    >
    > Operator:= _
    >
    > xlBetween,
    >
    > Formula1:="=OFFSET($A$2,0,Column()-1,COUNTA($A$2:$A$200))"
    >
    > .IgnoreBlank = True
    >
    > .InCellDropdown = True
    >
    > .ShowInput = True
    >
    > .ShowError = True
    >
    > End With
    >
    > End With


    > Regards
    >
    > Claus B.
    >
    > --


    Another clever little gem.

    Thanks Claus.

    Howard
     
    L. Howard, Apr 16, 2014
    #3
  4. L. Howard

    L. Howard Guest


    >
    > if your columns have the same number of values and you don't have to
    >
    > COUNTIF for each column this is all you need:
    >
    >
    >
    > With Range("A1:E1")
    >
    > With .Validation
    >
    > .Delete
    >
    > .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
    >
    > Operator:= _
    >
    > xlBetween,
    >
    > Formula1:="=OFFSET($A$2,0,Column()-1,COUNTA($A$2:$A$200))"
    >
    > .IgnoreBlank = True
    >
    > .InCellDropdown = True
    >
    > .ShowInput = True
    >
    > .ShowError = True
    >
    > End With
    >
    > End With
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --


    Another clever little gem, thanks Claus.

    Howard
     
    L. Howard, Apr 16, 2014
    #4
  5. L. Howard

    GS Guest

    Howard,
    It appears that you are trying to use dynamic lists without defining
    the ranges with names. IMO, it's better to define the ranges as dynamic
    and just use the names in the DV dialog. OR am I missing something
    here?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 16, 2014
    #5
  6. L. Howard

    L. Howard Guest

    On Wednesday, April 16, 2014 7:14:42 AM UTC-7, GS wrote:
    > Howard,
    >
    > It appears that you are trying to use dynamic lists without defining
    >
    > the ranges with names. IMO, it's better to define the ranges as dynamic
    >
    > and just use the names in the DV dialog. OR am I missing something
    >
    > here?
    >
    >
    >
    > --
    >
    > Garry
    >


    You are correct, but how would I pass the named ranges to the desired column DV?

    Howard
     
    L. Howard, Apr 16, 2014
    #6
  7. L. Howard

    GS Guest

    > On Wednesday, April 16, 2014 7:14:42 AM UTC-7, GS wrote:
    >> Howard,
    >>
    >> It appears that you are trying to use dynamic lists without defining
    >>
    >> the ranges with names. IMO, it's better to define the ranges as
    >> dynamic
    >>
    >> and just use the names in the DV dialog. OR am I missing something
    >>
    >> here?
    >>
    >>
    >>
    >> --
    >>
    >> Garry
    >>

    >
    > You are correct, but how would I pass the named ranges to the desired
    > column DV?
    >
    > Howard


    Via the DV dialog? Or is there some reason you need to do this via
    code?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 16, 2014
    #7
  8. L. Howard

    L. Howard Guest

    On Wednesday, April 16, 2014 8:43:29 AM UTC-7, GS wrote:
    > > On Wednesday, April 16, 2014 7:14:42 AM UTC-7, GS wrote:

    >
    > >> Howard,

    >
    > >>

    >
    > >> It appears that you are trying to use dynamic lists without defining

    >
    > >>

    >
    > >> the ranges with names. IMO, it's better to define the ranges as

    >
    > >> dynamic

    >
    > >>

    >
    > >> and just use the names in the DV dialog. OR am I missing something

    >
    > >>

    >
    > >> here?

    >
    > >>

    >
    > >>

    >
    > >>

    >
    > >> --

    >
    > >>

    >
    > >> Garry

    >
    > >>

    >
    > >

    >
    > > You are correct, but how would I pass the named ranges to the desired

    >
    > > column DV?

    >
    > >

    >
    > > Howard

    >
    >
    >
    > Via the DV dialog? Or is there some reason you need to do this via
    >
    > code?
    >
    >
    >
    > --
    >
    > Garry
    >


    Yes, I wanted to use code to do something like this from Claus' suggestion.

    With Range("A1:E1,G1,J1")
    ** seven +/- DV's **
    End With

    Where I was running a loop 5 times offsetting the DV code to each column.

    Howard
     
    L. Howard, Apr 16, 2014
    #8
  9. L. Howard

    GS Guest

    > Yes, I wanted to use code to do something like this from Claus'
    > suggestion.
    >
    > With Range("A1:E1,G1,J1")
    > ** seven +/- DV's **
    > End With
    >
    > Where I was running a loop 5 times offsetting the DV code to each
    > column


    I see. The $ symbols need to be removed from the col labels, then, so
    the formula adjusts accordingly. However, IMO, if you're designing a
    solution worksheet I recommend defining those dynamic ranges and
    manually 'construct' the DV lists so no code is required. If the
    dynamic ranges are empty then so too will be the respective DV list.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 16, 2014
    #9
  10. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 16 Apr 2014 09:14:58 -0700 (PDT) schrieb L. Howard:

    > Yes, I wanted to use code to do something like this from Claus' suggestion.


    try:

    Sub DropDowns()
    Dim rngC As Range, myRng As Range
    Dim myCnt As Long

    With ActiveSheet
    For Each rngC In .Range("A1:E1,G1,J1")
    myCnt = WorksheetFunction.CountA(.Range(rngC, rngC.Offset(199)))
    .Names.Add Name:="Col" & Chr(rngC.Column + 64), _
    RefersTo:="=Offset(" & rngC.Address & ",1,," & myCnt & ")"
    With rngC
    With .Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
    Operator:= _
    xlBetween, Formula1:="=Col" & Chr(rngC.Column + 64)
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
    End With
    End With
    Next
    End With
    End Sub


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 16, 2014
    #10
  11. L. Howard

    L. Howard Guest


    > I see. The $ symbols need to be removed from the col labels, then, so
    > the formula adjusts accordingly.>
    > --


    That does not work as the formula is "protected" by the " "'s per this recorded and loop added macro. All the DV lists refer to column A's values.

    I agree the hand made solution is better. There is not a huge outcry for code generated DV's anyway.

    Howard
     
    L. Howard, Apr 16, 2014
    #11
  12. L. Howard

    L. Howard Guest

    > I see. The $ symbols need to be removed from the col labels, then, so
    >
    > the formula adjusts accordingly.


    That does not work as the list reference is 'protected' by the formulas " "'s and all lists refer to column A as in this recorded-and-loop-added macro.

    I agree, its probably best to just do them by hand. There is no huge outcry for code generated DV's anyway.

    Sub Macro4()
    '
    ' Macro4 Macro
    Dim i As Long
    Range("A1").Select
    For i = 1 To 5
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=A2:A9"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    ActiveCell.Offset(, 1).Select
    Next
    End Sub

    Howard
     
    L. Howard, Apr 16, 2014
    #12
  13. L. Howard

    GS Guest

    >
    >> I see. The $ symbols need to be removed from the col labels, then,
    >> so the formula adjusts accordingly.>
    >> --

    >
    > That does not work as the formula is "protected" by the " "'s per
    > this recorded and loop added macro. All the DV lists refer to column
    > A's values.


    Well that makes sense! I didn't realize this was what you wanted.
    >
    > I agree the hand made solution is better. There is not a huge outcry
    > for code generated DV's anyway.


    True, but still needs to be done when needed. For example, I have an
    Invoicing: Simple Bookkeeping app that allows users to customize the
    ChartOfAccounts as per the following design...

    The app uses dependant DVs on the Expenses/Income/Summary sheets that
    ref the account categories, which are dynamic named ranges local to a
    hidden sheet named "Lists". (All lists are stored here) This feature
    requires me to create dynamic named ranges for new account categories
    once they exceed the number of built-in spares. In this scenario I add
    a dynamic defined name range for new categories, otherwise I rename the
    spares to the user-specified value.

    The structure allows for Account/SubAccount categories only, and
    user-defined categories can only be added to the "Other" main category
    for expenses. (Thus 'Expenses' and 'Other Expenses')

    This is deliberate because the Summary sheet is constructed to conform
    to our Tax Agency e-file guidelines, and so the main list of expense
    categories is defined by the Revenue Agency form for filing personal
    tax returns. The Income categories are not restricted in any way, so
    this is the most used case for adding dynamic ranges for the DVs on the
    3 sheets that use them.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 16, 2014
    #13
  14. L. Howard

    L. Howard Guest

    On Wednesday, April 16, 2014 10:25:53 AM UTC-7, GS wrote:
    > >

    >
    > >> I see. The $ symbols need to be removed from the col labels, then,

    >
    > >> so the formula adjusts accordingly.>

    >
    > >> --

    >
    > >

    >
    > > That does not work as the formula is "protected" by the " "'s per

    >
    > > this recorded and loop added macro. All the DV lists refer to column

    >
    > > A's values.

    >
    >
    >
    > Well that makes sense! I didn't realize this was what you wanted.
    >
    > >

    >
    > > I agree the hand made solution is better. There is not a huge outcry

    >
    > > for code generated DV's anyway.

    >
    >
    >
    > True, but still needs to be done when needed. For example, I have an
    >
    > Invoicing: Simple Bookkeeping app that allows users to customize the
    >
    > ChartOfAccounts as per the following design...
    >


    So you use code to produce the DV's and then 'hand craft' the dynamic ranges to each DV?

    Howard
     
    L. Howard, Apr 16, 2014
    #14
  15. L. Howard

    L. Howard Guest

    >
    > try:
    >
    >
    >
    > Sub DropDowns()
    >
    > Dim rngC As Range, myRng As Range
    >
    > Dim myCnt As Long
    >
    >
    >
    > With ActiveSheet
    >
    > For Each rngC In .Range("A1:E1,G1,J1")
    >
    > myCnt = WorksheetFunction.CountA(.Range(rngC, rngC.Offset(199)))
    >
    > .Names.Add Name:="Col" & Chr(rngC.Column + 64), _
    >
    > RefersTo:="=Offset(" & rngC.Address & ",1,," & myCnt & ")"
    >
    > With rngC
    >
    > With .Validation
    >
    > .Delete
    >
    > .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
    >
    > Operator:= _
    >
    > xlBetween, Formula1:="=Col" & Chr(rngC.Column + 64)
    >
    > .IgnoreBlank = True
    >
    > .InCellDropdown = True
    >
    > .ShowInput = True
    >
    > .ShowError = True
    >
    > End With
    >
    > End With
    >
    > Next
    >
    > End With
    >
    > End Sub
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.


    Very nice, Claus. Adapts the source list to whatever in in the column, and if the list/s should change for whatever reason, just run the code again and it re-adapts to the new list/s either longer or shorter. (Or change it in the Name Manager)

    I'm impressed and thank you!

    Howard
     
    L. Howard, Apr 16, 2014
    #15
  16. L. Howard

    GS Guest

    > On Wednesday, April 16, 2014 10:25:53 AM UTC-7, GS wrote:
    >>>

    >>
    >>>> I see. The $ symbols need to be removed from the col labels, then,
    >>>> so the formula adjusts accordingly.>
    >>>> --

    >>
    >>>

    >>
    >>> That does not work as the formula is "protected" by the " "'s per
    >>> this recorded and loop added macro. All the DV lists refer to
    >>> column A's values.

    >>
    >>
    >>
    >> Well that makes sense! I didn't realize this was what you wanted.
    >>
    >>>

    >>
    >>> I agree the hand made solution is better. There is not a huge
    >>> outcry for code generated DV's anyway.

    >>
    >>
    >>
    >> True, but still needs to be done when needed. For example, I have an
    >>
    >> Invoicing: Simple Bookkeeping app that allows users to customize the
    >>
    >> ChartOfAccounts as per the following design...
    >>

    >
    > So you use code to produce the DV's and then 'hand craft' the dynamic
    > ranges to each DV?
    >
    > Howard


    No! I use code to define the dynamic ranges that are used by the
    existing DVs on the mentioned "Expenses" and "Income" sheets. That
    keeps the DVs as intended so only their source lists update physically
    and so the lists update accordingly.

    Adding new Categories/SubCategories to the "Summary" sheet is done by
    copying a manually constructed 'group' (as in 'Group and Outline')
    stored in hidden rows above the working area. This consists of the
    Categorie row (which contains the expander) and the default "Other?"
    subcategory row immediately below it. Additional subcategory rows are
    inserted as needed by copying just the default subcategory row. This
    allows summary/detail viewing of the sheet on a per-category basis or
    entire list. (FYI: The sheet is configured like a Profit&Loss statement
    that can show/hide sub account details, where each sub account displays
    its totals that make up the total for its parent account)

    I was using DVs on this sheet as I currently customize each app for the
    user and so having the DVs made this easier to pull accounts from the
    "Lists" sheet. But I want to get away from having to do this so the
    project is entirely user-customizable. Currently, I remove the DVs when
    I'm done the setup, leaving the list as constant values. (This is a
    'read-only' sheet)

    In order to modify this later the user needs to send me a copy of their
    working file and wait for me to make the revisions and return the file.
    (IMO it's a dumbass idea, but that's how the local ChamberOfCommerce
    suggested I do it! I've since (after 7 years) been able to change this
    thinking<g>) I'm in the midst of upgrading the app and so is why your
    topic caught my attention.

    My approach to what you're trying to do involves updating the dynamic
    lists only, resulting in the DVs auto-updating their contents. Your
    approach requires redefining the DV list criteria, which I do not
    recommend as the way to go for persistent DVs on a worksheet. I'm fine
    with doing that on-the-fly when needed, but not by entering the formula
    for defining the dynamic range. I prefer to define the range then add
    that name as the DV formula.

    For clarity:
    My "Lists" sheet starts with main categories in colA, and are
    configured as...

    REVENUE
    Income1
    Income2
    Income3
    Other Income
    EXPENSES
    Expense1
    Expense2
    Expense3
    Expense4
    Expense5
    Other Expense
    ....and so on as per the tax agency's e-return form
    OTHER_EXPENSES
    OtherExpense1
    OtherExpense2
    OtherExpense3
    ....and so on as per user defined need
    Miscellaneous

    The REVENUE section is a dynamic range that begins with the 1st account
    below the UCase header "REVENUE" and ends with "Other Income".

    The EXPENSES section is a dynamic range that begins with the 1st
    account below the UCase header "EXPENSES" and ends with "Other
    Expense".

    The OTHER_EXPENSES section is a dynamic range that begins with the 1st
    account below the UCase header "OTHER_EXPENSES" and ends with
    "Miscellaneous".

    Sub-Accounts run horizontally from their respective header, where each
    main account is defined as the header for its respective sub-accounts.

    The dynamic range is named by removing spaces in its header. So the
    "Bank Charges" Expense account is the header named "BankCharges" for
    defining its sub-account members. This may look like this...

    Bank Charges | Bank Fees | Overdraft Interest | Other Bank Charges

    ...where every sub-account list ends with "Other " prepended to the
    account name as its final detail sub category so all unclassified
    entries have a place to go.

    As mentioned, the EXPENSES section is preset to the tax return form and
    so I only need to update this if/when the Revenue Agency changes their
    form. Now you can appreciate my need to change the thinking about me
    having to manually update every user's working file for changes going
    forward. Revenue Agency form changes are automatic app update entries.
    This can use the same code as the user-defined customizations done
    through the UI. This will happen via an update file that gets read at
    startup (if it exists), processed, then deleted so it doesn't redo this
    at next startup.

    So on my "Expenses" and "Income" sheets the sub-category DVs are
    dependant on the Category DV selections, where each list item is the
    header for the dynamic sub-account list for that item. expect you can
    now more easily understand why I think it's better to use the name of
    the dynamic range for the DV list criteria. Your approach leaves way
    too much room for disater to strike for my liking<g>!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 16, 2014
    #16
  17. L. Howard

    L. Howard Guest


    > Your approach leaves way
    >
    > too much room for disater to strike for my liking<g>!
    >
    >
    >
    > --
    >
    > Garry


    That is some pretty heavy duty explanation for someone of my level of expertise to grasp fully.

    I kinda thought there would be some not to complex way to assign a column number to a variable and use the variable in the formula portion of the DV producing code.

    Claus' code does a good job of doing that up to a point. Seems you just cannot expect it to do DV's from A1:Z1. I need to study it more, seems to only go N1 then errors out. Not sure why. Upper case Chr are from 65 to 90 and I see where + 64 would make column 1 into column A. The N column limithas me stumped. Have not tested it on double letter columns.

    Howard
     
    L. Howard, Apr 16, 2014
    #17
  18. L. Howard

    GS Guest

    Sorry about the amount of detail but I didn't know how else to explain
    my point!<g>


    > I kinda thought there would be some not to complex way to assign a
    > column number to a variable and use the variable in the formula
    > portion of the DV producing code.


    Well there is the option of using a larger range to add the DV, then
    iterate the range to create the criteria for them same as you've done.
    >
    > Claus' code does a good job of doing that up to a point. Seems you
    > just cannot expect it to do DV's from A1:Z1.


    Claus' code can be easily expanded to do this. I think he just kept it
    in line with your posted criteria, leaving it up to you to modify to
    suit otherwise.

    > I need to study it
    > more, seems to only go N1 then errors out. Not sure why. Upper case
    > Chr are from 65 to 90 and I see where + 64 would make column 1 into
    > column A. The N column limit has me stumped. Have not tested it on
    > double letter columns.


    This is where my suggestion to use a larger range would be useful.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 17, 2014
    #18
  19. L. Howard

    isabelle Guest

    hi Howard,

    you can replace
    Chr(rngC.Column + 64)
    by
    Application.Substitute(rngC.Address(0, 0), "1", "")

    isabelle

    Le 2014-04-16 17:49, L. Howard a écrit :
    > Upper case Chr are from 65 to 90 and I see where + 64 would make column 1 into column A.
    > The N column limit has me stumped. Have not tested it on double letter columns.


    > Howard
     
    isabelle, Apr 17, 2014
    #19
  20. L. Howard

    L. Howard Guest

    On Wednesday, April 16, 2014 9:07:57 PM UTC-7, isabelle wrote:
    > hi Howard,
    >
    >
    >
    > you can replace
    >
    > Chr(rngC.Column + 64)
    >
    > by
    >
    > Application.Substitute(rngC.Address(0, 0), "1", "")
    >
    >
    >
    > isabelle
    >
    >
    >
    > Le 2014-04-16 17:49, L. Howard a écrit :
    >
    > > Upper case Chr are from 65 to 90 and I see where + 64 would make column1 into column A.

    >
    > > The N column limit has me stumped. Have not tested it on double lettercolumns.

    >
    >
    >
    > > Howard


    Hi isabelle,

    I'll give that a go.

    Thanks, Howard
     
    L. Howard, Apr 17, 2014
    #20
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. spolk
    Replies:
    1
    Views:
    136
    Cecilkumara Fernando
    Apr 30, 2004
  2. Steve
    Replies:
    4
    Views:
    101
    Tom Ogilvy
    Sep 28, 2004
  3. jeffbert
    Replies:
    6
    Views:
    125
    jeffbert
    Feb 10, 2005
  4. arshia22
    Replies:
    1
    Views:
    94
    Bob Phillips
    Mar 3, 2005
  5. basildon
    Replies:
    1
    Views:
    116
    Tom Ogilvy
    Dec 16, 2005
  6. Juan
    Replies:
    0
    Views:
    113
  7. minkokiss
    Replies:
    2
    Views:
    196
    JLGWhiz
    Apr 5, 2007
  8. S Himmelrich

    If data exists in column J, put formula in column K

    S Himmelrich, Jan 22, 2008, in forum: Excel Programming
    Replies:
    5
    Views:
    84
    S Himmelrich
    Jan 22, 2008
Loading...