VBA Code to either fill out a cell or leave it blank based on whether a value appears in 4 other cells

[SanFelippo]

Hello,

This one might be a bit difficult to explain but I'll do my best. I have cells B15, B16, B17, and B18 which all have a drop down list of options you can choose for each one. Then, cells D15, D16, D17, and D18 are all set up to be assigned a number based on the option that was selected in the corresponding B cell next to it. There are 24 options you can select in the Cell B drop down lists, and all of these have a corresponding number code attached to them. The numbers are 1,3,4,7,9, or 10.

So, if you haven't figured already, some of the options in the dropdown list for the B cells will share the same number code as other options in the list, but once one of those numbers is used in one of the cells, they cannot be used again in the other 3. I have already wrote some code to ensure that once an option is chosen in, for example, B15 that has a corresponding number code of 5 attached to it (which shows up in D15), you can no longer select any of the options in the dropdown list in B16, B17, and B18 that share that number code of 5.

Now, those Drop down options in the B cells that have a corresponding number code of 9 attached to them also then have a description associated with them.

I have these options and descriptions in a list on another tab.

My question is this. Cell G15 is the place where the Description of the option needs to show up if an option is chosen in any of the B cells that has a number code 9 attached to it. However, one of the options you can select in the B cells is "Other,", and in the case that "Other is selected, G15 needs to open up so that you can type a description into it that is under 50 characters.

So basically, I need G15 to automatically pull in the correct Description of the option if an option that has a corresponding number code of 9 is selected anywhere in cells B15,B16,B17, or B18, but if the Option "other" is selected in any of the cells B15,B16,B17, or B18, G15 needs to open up and allow Text to be entered up to 50 characters.

I know this code doesn't work in VBA, but I am going to post it here so give a better idea of what I am trying to accomplish.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Range("B15") <> "Other" And Range("B16") <> "Other" And Range("B17") <> "Other" And Range("B18") <> "Other" Then
        Range("G15") = "=IF(D15=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),IF(D16=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),IF(D17=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),IF(D18=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),""))))"
    End If

Application.EnableEvents = True
End Sub

So as you can see in the code, if "Other" doesn't show up in any of the Cells B15,B16,B17, and B18, then I want G15 to run through and check to see if there is a number code of 9 in D15, and if so, then use B15 to lookup the correct description into G15. If there is not a number code of 9 in D15, move on and check to see if there is one in D16, and if so, use B16 up look up the correct description into G15, and so on. If "other" does show up anywhere in cells B15,B16,B17, or B18, then G15 needs to simply be open for Text to be entered up to 50 characters.

I know you can't use lookup functions in VBA, but I am trying to give you an idea of what I am trying to do.

 

[Beyond_avarice]

If Range("B15").Value <> "Other" And Range("B16").Value <> "Other" And Range("B17").Value <> "Other" And Range("B18").Value <> "Other" Then
                  Range("G15").Value = "=IF(D15=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),IF(D16=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),IF(D17=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),IF(D18=9,VLOOKUP(B15,'(Lists)'!$AI$2:$AK$26,3,0),""))))"

 

[SanFelippo]

That doesn't work. I am pretty sure you can't put a lookup function into VBA. I only posted that as an example to show the kind of result I was looking for. Can anyone help?

 

[Beyond_avarice]

You can put a lookup into a VBA sub routine. (eg. Application.WorksheetFunction.VLookup)

Your formula doesn't put a lookup into VBA, It populates a worksheet with a lookup formula.

If "other" does show up anywhere in cells B15,B16,B17, or B18, then G15 needs to simply be open for Text to be entered up to 50 characters.

Are you trying to populate G15 with a formula or result value? I would start with your formula. If your formula isn't working on the worksheet, then it wont work when VBA tries to populate G15 with it.

 

[SanFelippo]

I went ahead and was able to make the sheet do what I wanted it to using a combination of Case Select and some other things. It's not pretty, but it works exactly how I need it to.