CODEWITHSUNDEEP
vbaexcellogical-operators
phillipsK
phillipsK

Reputation: 1516

Dizzy with Excel VBA AND OR logical operators

I would like my procedure delete any row in which column a (cA) is Fund ID 6347 OR 6349 AND has a basis point impact of less than +/-100 AND less than +/-50% price change

The following code works to delete rows with: either fund and a basis point impact +/- 100 but I don't believe it is working according to my intentions, as amid the extremely confusing parentheses in combination with the poor 2007 excel IDE interface, when I remove the cells(i, cJ) >= -100) code in the first line my test fails, leading me to believe that in some way my code works in part but on accident.

Does anyone understand the logic I am trying to code? Is there some web GUI were I can code excel VBA and see parenthesis highlighted for a better experience in these situations? Thank you

        For i = LR To 2 Step -1

                     If (Left(CStr(cells(i, cA)), 4) = "6347" And (cells(i, cJ) <= 100 And cells(i, cJ) >= -100)) Or _
                     (Left(CStr(cells(i, cA)), 4) = "6349" And (cells(i, cJ) <= 100) And cells(i, cJ) >= -100) Or _
                     Left(CStr(cells(i, cC)), 4) = "Trs " Or _
                     Mid(CStr(cells(i, cC)), 15, 8) = "bcomf1t " Or _
                     Mid(CStr(cells(i, cC)), 15, 7) = "bcomtr " Then

Upvotes: 0

Views: 473

Answers (2)

Comintern
Comintern

Reputation: 22195

Pull the variables out of the spreadsheet before you build your if statement, then put them one on each line. This is much more efficient and makes it much, much easier to read. Following your specification statement:

I would like my procedure delete any row in which column a (cA) is Fund ID 6347 OR 6349 AND has a basis point impact of less than +/-100 AND less than +/-50% price change

It would look like this (note that I'm guessing on what values come from where, and your code has additional conditions you don't specify):

Dim FundId As String
FundId = Left$(Cells(i, cA).Value, 4)
Dim BasisImpact As Long
BasisImpact = Cells(i, cJ).Value
Dim PriceChange As Long
PriceChange = Cells(i, whereever).Value

If (FundId = "6347" Or FundId = "6349") And _
   (BasisImpact >= -100 And BasisImpact <= 100) And _
   (PriceChange >= -50 And PriceChange <= 50) Then

As pointed out by @Jeeped in the comments, VBA will perform all of the tests in an If statement (it doesn't short circuit). If this is in a performance critical part of your code or you have a huge data set, nesting the If statements and Worksheet accesses will give you better performance:

If FundId = "6347" Or FundId = "6349" Then
    Dim BasisImpact As Long
    BasisImpact = Cells(i, cJ).Value
    If BasisImpact >= -100 And BasisImpact <= 100 Then
        Dim PriceChange As Long
        PriceChange = Cells(i, whereever).Value
        If PriceChange >= -50 And PriceChange <= 50 Then
            'Do your thing
        End If
    End If
End If

Upvotes: 4

user3598756
user3598756

Reputation: 29421

If you only bother about the "fund" and "impact" check, then go this way:

Dim okFund As Boolean, okImpact As Boolean

For I = LR To 2 Step -1

    okFund= Left(CStr(Cells(I, cA)), 4) = "6347" Or (Left(CStr(Cells(I, cA)), 4) = "6349"
    okImpact = Cells(I, cJ) <= 100 And Cells(I, cJ) >= -100

    If okFund And okImpact Then

Upvotes: 1

Related Questions