Jul 27 2021 05:25 PM - edited Jul 27 2021 05:32 PM
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
Jul 27 2021 08:07 PM
Jul 27 2021 08:14 PM
@mtarler Hi please find attached sample file. Thanks :)
Jul 27 2021 08:38 PM
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.
Jul 27 2021 08:38 PM
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.