SOLVED

Refactoring code - vba

Contributor

Hi everyone, could someone help me out to refactor the code below, please. So basically, I made a clickable in-cell wherein it selects either X, O, or P. It is in the uncontiguous range so problem is that if there's an update on the cell, I manually edit the below range one by one. What I am thinking is that, have a column with an xxxx value in it and use it as a range. Not sure how to do it though. Cheers!

Thanks in advance. 

 

    If Not Intersect(Range("I14:I20,I22:I22,I25:I26,I28:I32,I34:I38,I40:I44,I46:I50,I52:I53,I55:I56,I61:I67,I67:I73,I75:I80,I82:I85,I87:I94,I96:I98,I100:I111,I122:I128,I30:I130,I134:I143,I145:I156,I158:I163,I65:I168,I170:I175,I177:I179,I181:I183,I185:I186,I188:I195,I197:I198"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If

 

 

3 Replies
I think the better way to think about it is how do YOU know what the right cells are? For example if column H has information in it that says:
H12: First Name
H13: Last Name
H14: Breakfast is P/X/O?
H15: Lunch is P/X/O?
H16: Snack is P/X/O?
etc..
and any time H has "P/X/O" as the request then you want that clickable feature then
make the range all of I:I and then just add another IF statement to check if the corresponding cell in H has "P/X/O" in it (e.g something like IF InStr(1,Target.offset(0,-1),"P/X/O") THEN )

@mtarler Hi please find attached sample file. Thanks

best response confirmed by rbalza (Contributor)
Solution

@rbalza  So I don't know how 'real' that sample is but I give you 1 example of how it could work.  1st I fixed your nested IF statements that shouldn't have been nested and then changed the IF statement with that big non-continuous range to this:

    If Not Intersect(Range("H14:H56"), Target) Is Nothing And _
      Not IsEmpty(Target.Offset(0, -5)) And _
      IsEmpty(Target.Offset(0, -6)) Then

You could nest the if statements or use AND like I did here and I just check the whole H range and that there is a value in the Col C and Col B is empty (i.e. no "-" mark). 

Again you need to look at your actual data/sheet and see if this or other logic would work.  Not only does it make you life easier in the macro but also in updating and maintaining the sheet itself.