I've been racking my brain trying to figure how to solve this for the last week and a half. The core of my formula works in Excel but not in VBA.
The basic concept I'm trying to accomplish is to make one drop-down dependent on the result of another drop-down.
The first drop-down has a list of parent categories. I wrote a macro that cycles through all of the parent categories and creates a named range for each parent category that contains the relevant subsidiary categories choices for the second drop-down. I tested this in Excel and it works beautifully with indirect and offset and match but when I tried it in VBA it errors out. I've tried a lot of different iterations but let me show you the latest:
Here is the data from which the drop downs are created (sheet name is "Colors"). The range names column is something that my previous macro already created. The upgrade column contains the drop-down choices for the first drop-down. The columns with color as their title contain the subsidiary drop-down choices. The range name refers to these subsidiary choices.
<tbody>
</tbody>
Here is the code that I'm using:
ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= xlBetween, Formula1:="=if(isblank(activecell.offset(0,-1))," & Chr(34) & "N/A" & Chr(34) & ",indirect(offset(colors!a1,match(activecell.offset(0,-1),colors!b:b,0)-1,0,1,1) ) )"
I have tried a whole variety of different combinations. I tried hard coding the reference cell, adjusting the error-checking in the beginning of the if statement, changing the alert style. I have not tried many different formulas in terms of indirect, match, offset but I did try that exact text directly in Excel (with of course the appropriate adjustments for non-VBA) and it worked perfectly.
Seeing as how I've spent about 15 to 20 hours on this one line of code, any help would be unbelievably appreciated.
The basic concept I'm trying to accomplish is to make one drop-down dependent on the result of another drop-down.
The first drop-down has a list of parent categories. I wrote a macro that cycles through all of the parent categories and creates a named range for each parent category that contains the relevant subsidiary categories choices for the second drop-down. I tested this in Excel and it works beautifully with indirect and offset and match but when I tried it in VBA it errors out. I've tried a lot of different iterations but let me show you the latest:
Here is the data from which the drop downs are created (sheet name is "Colors"). The range names column is something that my previous macro already created. The upgrade column contains the drop-down choices for the first drop-down. The columns with color as their title contain the subsidiary drop-down choices. The range name refers to these subsidiary choices.
| A | B | C | D | E | F | G |
| Range names | Upgrade | Color 1 | Color 2 | Color 3 | Color 4 | Color 5 |
| _ | ||||||
| _StandardCarpet | Standard Carpet | Brown | Green | Blue | ||
| _1stUpgradeCarpet | 1st Upgrade Carpet | Black | Brown | Green | Blue | |
| _2ndUpgradeCarpet | 2nd Upgrade Carpet | Red | ||||
| _3rdUpgradeCarpet | 3rd Upgrade Carpet | Brown | Green | Blue | ||
| _4thUpgradeCarpet | 4th Upgrade Carpet | Yellow | Purple |
<tbody>
</tbody>
Here is the code that I'm using:
ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= xlBetween, Formula1:="=if(isblank(activecell.offset(0,-1))," & Chr(34) & "N/A" & Chr(34) & ",indirect(offset(colors!a1,match(activecell.offset(0,-1),colors!b:b,0)-1,0,1,1) ) )"
I have tried a whole variety of different combinations. I tried hard coding the reference cell, adjusting the error-checking in the beginning of the if statement, changing the alert style. I have not tried many different formulas in terms of indirect, match, offset but I did try that exact text directly in Excel (with of course the appropriate adjustments for non-VBA) and it worked perfectly.
Seeing as how I've spent about 15 to 20 hours on this one line of code, any help would be unbelievably appreciated.
