Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 limit has me stumped. Have not tested it on double letter columns. Howard |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
i to = column in Formula
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 column 1 into column A. The N column limit has me stumped. Have not tested it on double letter columns. Howard Hi isabelle, I'll give that a go. Thanks, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Permanently link formula references to Column Names and not Column Cell Numbers | Excel Discussion (Misc queries) | |||
conditional formatting formula to highlight items on column B inrelation to column A | Excel Discussion (Misc queries) | |||
A formula or macro that will place the date in an adjacent column Bany time something is typed in column A | Excel Programming | |||
Copying a formula in a blank column as far as data in previous column | Excel Programming | |||
Populating column N with a formula if column A is Null or Blank | Excel Programming |