how co i use select case to change cell location in this formula in vba code

[kevinu]

Im currently using this code to change where the formula looks for data depending on the value in a combobox. Is there a way to simply change the a, a2 and a3 to k, k2 and k3? I tried using an interger like i = a if case is 1 and i=k if case is 2 but cant get it to work.

Please Login or Register  to view this content.

[ptmuldoon]

Are you doing this with an ActiveX Control? I think your on the right track and here is a small sample that looks to the cells in columns A, B, or C to get the value for a formula and used on a change event to see it.

In worksheet module

Please Login or Register  to view this content.

And in normal module to make the combobox

Please Login or Register  to view this content.

[kevinu]

I'm using controlbox1_change() to trigger this code. I'm not sure how to implement your suggestion however. My code puts a formula in a cell. I want to change the formula and i dont know how to do that with what your suggesting. From what I understand if i make a case where x= range("a2"), the code is placed in the cell it will just read x in stead of a2. Unless im misunderstanding what your suggesting. How does the formula know im asking it to look for the range instead of assuming the variable is the range.

Example, in this code would i switch this

Please Login or Register  to view this content.

to this?

Please Login or Register  to view this content.

or do i need to put x in between quotes or something so it knows to look up x

Sorry, im just confused.

[jolivanes]

Example

Please Login or Register  to view this content.

I think it should be like

Please Login or Register  to view this content.

[ptmuldoon]

i was confused by your initial question and tweaked the code a little to place a formula in the cell based on the drop down value. And I look more tonight when back home on your actual formula.

Please Login or Register  to view this content.

[ptmuldoon]

kevinu

There is no need to duplicate your code within each Case condition. What I am/was doing was just setting what the cell address needs to be based on the combobox selection. Then after the select, you can just insert that variables value into your formula.

This changes the A3 to X3 in your formula. But note I took a quick shortcut with your additional MyCriteria.Address. If you could post a sample file, we could make this a little cleaner.

Please Login or Register  to view this content.

[kevinu]

the formula isnt duplicated. one is + and the other is * in the middle. im looking at a data set that has 2 variables and comparing it to a list of 2 variables. The first code looks to see if any variable from the data set is in the list. The second formula looks if the 2 variables appear anywhere on the list. So more focused. I'll place an example if i can tomorrow. Thx for the help

[kevinu]

PT, that worked like a charm thanks