i to = column in Formula

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

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.

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
 
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.
Click to expand...

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.
 
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
Click to expand...
Regards

Claus B.

--
Click to expand...

Another clever little gem.

Thanks Claus.

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

--
Click to expand...

Another clever little gem, thanks Claus.

Howard
 
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
 
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?
Click to expand...

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?
Click to expand...

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
 
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
Click to expand...

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
 
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.
Click to expand...

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.
 
I see. The $ symbols need to be removed from the col labels, then, so
the formula adjusts accordingly.>
--
Click to expand...

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
 
I see. The $ symbols need to be removed from the col labels, then, so
the formula adjusts accordingly.
Click to expand...

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
 
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.
Click to expand...

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.
Click to expand...

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
 
Well that makes sense! I didn't realize this was what you wanted.






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...
Click to expand...

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

Howard
 
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.
Click to expand...

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
 
So you use code to produce the DV's and then 'hand craft' the dynamic
ranges to each DV?

Howard
Click to expand...

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
 
Your approach leaves way

too much room for disater to strike for my liking<g>!
Click to expand...

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
 
Sorry about the amount of detail but I didn't know how else to explain
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
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 :
 
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 :
Click to expand...

Hi isabelle,

I'll give that a go.

Thanks, Howard
 
Back
Top