Need help with VBA to check if inSTR = cell.address

[Akbarov]

Hello Dear community,

I have string with list of ranges, I want to check each cell to use if statement. If cell.address within that string, then run code. Normally InStr could handle it, but problem is String is as following:

myRng.address = "$S$1,$S$14:$S$16,$S$18,$S$20,$S$25,$S$27,$S$32,$S$45,$S$48,$S$64:$S$65,$S$68,$S$70:$S$71,$S$79:$S$81,$S$83:$S$124,$S$126:$S$141,$S$143:$S$201,$S$205:$S$207,$S$209,$S$211:$S$261,$S$263:$S$265,$S$269:$S$284,$S$286:$S$289,$S$293:$S$305,$S$307:$S$317"

I tryed to:

If InStr(1, myRng.Address, "$S$" & calculatedDatarow & ":") then

Result is not correct, if even it would work, it can't find for example $S$15 if even that's within range.

Can anyone help me to figure out how to check that? S is only column within range.

 

[Akuini]

Try using Intersect instead of InStr:

If Not Intersect(Range("S" & calculatedDatarow), myrng) Is Nothing Then


End If

 

[Alex Blakenburg]

There may be other way of doing it but this should work.

Sub TestCellIsInRange()

Dim rng As Range
Dim arrAdd As Variant
Dim calculatedDatarow As Long
Dim i As Long

calculatedDatarow = 15              ' Test Value
arrAdd = Split("$S$1,$S$14:$S$16,$S$18,$S$20,$S$25,$S$27,$S$32,$S$45,$S$48" & _
                ",$S$64:$S$65,$S$68,$S$70:$S$71,$S$79:$S$81,$S$83:$S$124,$S$126:$S$141" & _
                ",$S$143:$S$201,$S$205:$S$207,$S$209,$S$211:$S$261,$S$263:$S$265" & _
                ",$S$269:$S$284,$S$286:$S$289,$S$293:$S$305,$S$307:$S$317", _
                ",")

Set rng = Range(arrAdd(0))
For i = 1 To UBound(arrAdd) - 1
    Set rng = Union(rng, Range(arrAdd(i)))
Next i

If Not Intersect(Range("S" & calculatedDatarow), rng) Is Nothing Then
    MsgBox "The row is in the nominated range"
Else
    MsgBox "The row is NOT in the nominated range"
End If
    
End Sub

 

[Akuini]

Wait, how did you set myRng initially?

 

[Alex Blakenburg]

@Akuini I thought I tried that but must have done something wrong but you will need.

    Dim myrng As Range
    
    Set myrng = Range("$S$1,$S$14:$S$16,$S$18,$S$20,$S$25,$S$27,$S$32,$S$45,$S$48,$S$64:$S$65,$S$68,$S$70:$S$71,$S$79:$S$81,$S$83:$S$124,$S$126:$S$141,$S$143:$S$201,$S$205:$S$207,$S$209,$S$211:$S$261,$S$263:$S$265,$S$269:$S$284,$S$286:$S$289,$S$293:$S$305,$S$307:$S$317")

 

[Akuini]

@Alex Blakenburg
Yes, I think it should be like this:

Set myRng = Range("$S$1,$S$14:$S$16,$S$18,$S$20,$S$25,$S$27,$S$32,$S$45,$S$48,$S$64:$S$65,$S$68,$S$70:$S$71,$S$79:$S$81,$S$83:$S$124,$S$126:$S$141,$S$143:$S$201,$S$205:$S$207,$S$209,$S$211:$S$261,$S$263:$S$265,$S$269:$S$284,$S$286:$S$289,$S$293:$S$305,$S$307:$S$317")

If Not Intersect(Range("S" & calculatedDatarow), myRng) Is Nothing Then


End If

 

[Rick Rothstein]

@Alex Blakenburg
Yes, I think it should be like this:

Set myRng = Range("$S$1,$S$14:$S$16,$S$18,$S$20,$S$25,$S$27,$S$32,$S$45,$S$48,$S$64:$S$65,$S$68,$S$70:$S$71,$S$79:$S$81,$S$83:$S$124,$S$126:$S$141,$S$143:$S$201,$S$205:$S$207,$S$209,$S$211:$S$261,$S$263:$S$265,$S$269:$S$284,$S$286:$S$289,$S$293:$S$305,$S$307:$S$317")

If Not Intersect(Range("S" & calculatedDatarow), myRng) Is Nothing Then


End If

There is a length limit to the string argument for the Range object and this string is very close to it, so if the OP were to add more to it in the future, the code would fail. My suggestion would be to remove the $ signs (they have no meaning inside a macro when setting a range) to insure the OP won't exceed the length limit in the future. To that end, I would assign the string to a variable so it is familiar to the OP and then remove the $ signs when setting the myRng variable. Something like this...

strRange = "$S$1,$S$14:$S$16,$S$18,$S$20,$S$25,$S$27,$S$32,$S$45,$S$48,$S$64:$S$65,$S$68,$S$70:$S$71,$S$79:$S$81,$S$83:$S$124,$S$126:$S$141,$S$143:$S$201,$S$205:$S$207,$S$209,$S$211:$S$261,$S$263:$S$265,$S$269:$S$284,$S$286:$S$289,$S$293:$S$305,$S$307:$S$317"
Set myRng = Range(Substitute(strRange, "$", ""))

 

[Akbarov]

Thank you all for your time. Tested Alex's solution worked, but yes probably I will need to remove $ signs.

 

[Akuini]

There is a length limit to the string argument for the Range object and this string is very close to it,

Good point @Rick Rothstein. And I believe the limit is 255 characters .