VBA Multiple IF conditions - why doesn't mine work?

[Jossilyn]

Hi there,

While looking on your site I found this post of June 2008 by Anxoperez which ended with this formula:
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
Range("C4").Select
End If
End Sub

I am looking to do something similar on excel 07 as follows:
Range("O16").Select
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"

Compile error expected: Then or GoTo At the First value (which I have highlighted in red)

Why doesn't mine work?

Also would like the If formula to repeat for range O16:O36.

Please help.

 

[sykes]

I think you need a full stop (period) before the first "Value" where it's bugging out....

Range("O16").Select
IF ActiveCell.Offset(0,-1).Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"

 

[Richard Schollar]

You realise that you are testing for the presence of the text string "1" and not the numeric value 1? Assumign that is what you meant, you could achieve what you want with:

Dim cell As Range
For Each cell In Range("O16:O36")
  If cell.Offset(,-1).Value = "1" And cell.Offset(,-2).Value = "1" Then _
    cell.Value = "1"
Next cell

Depending on what values currently exist in these column O cells, you could also do this without looping:

With Range("O16:O36")
  .FormulaR1C1 = "=IF(AND(RC[-1]=""1"",RC[-2]=""1""),""1"","""")"
  .Value = .Value
End With

 

[sykes]

..............this should cover the range you asked for as well. as long as my understanding's correct, you shouldn't need to "select" and "activate" things either.

Finally, do you need the ones to be text strings, or are they just integers?

To keep closest to your original question:

Sub loop_through()
Dim rng As Range, cl As Range

Set rng = Sheets("Sheet1").Range("O16:O36")

    For Each cl In rng
        If cl.Offset(0, -1).Value = "1" And cl.Offset(0, -2).Value = "1" Then cl.Value = "1"
    Next
    Set rng = Nothing
End Sub

..........but if the columns just contain numbers, then we can drop the double quotation marks.....

Sub loop_through()
Dim rng As Range, cl As Range

Set rng = Sheets("Sheet1").Range("O16:O36")

    For Each cl In rng
        If cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 1 Then cl.Value = 1
    Next
    Set rng = Nothing
End Sub

 

[Jossilyn]

Thanks Skye and Richard,

I have implemented the follow code but it says the compile error is Else without If

Sub RiskScore()
    Dim rng As Range, cl As Range
Set rng = Sheets("Chemical Risk Assessment Form").Range("O16:O36")
    For Each cl In rng
        If cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 1 Then cl.Value = 1
        Else
            If cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 2 Then cl.Value = 3
        Else
            If cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 3 Then cl.Value = 6
        Else
            If cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 4 Then cl.Value = 10
        Else
            If cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 5 Then cl.Value = 15
   Next
    Set rng = Nothing
    End If
End Sub

Any help would be fantastic

 

[Blade Hunter]

Sub RiskScore()
    Dim rng As Range, cl As Range
Set rng = Sheets("Chemical Risk Assessment Form").Range("O16:O36")
    For Each cl In rng
        If cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 1 Then
            cl.Value = 1
        ElseIf cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 2 Then
            cl.Value = 3
        ElseIf cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 3 Then
            cl.Value = 6
        ElseIf cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 4 Then
            cl.Value = 10
        ElseIf cl.Offset(0, -1).Value = 1 And cl.Offset(0, -2).Value = 5 Then
            cl.Value = 15
        End If
   Next
    Set rng = Nothing
    End If
End Sub

Another way of doing it:

Sub RiskScore()
    Dim rng As Range, cl As Range
Set rng = Sheets("Chemical Risk Assessment Form").Range("O16:O36")
    For Each cl In rng
        If cl.Offset(0, -1).Value = 1 Then
            Select Case cl.Offset(0, -2).Value
            Case 1
                cl.Value = 1
            Case 2
                cl.Value = 3
            Case 3
                cl.Value = 6
            Case 4
                cl.Value = 10
            Case 5
                cl.Value = 15
            End Select
        End If
   Next
    Set rng = Nothing
    End If
End Sub

Cheers

Dan

 

[Jossilyn]

Thanks dan,

That seems to have corrected the first error.. now it highlights the Last "End If" as an error

Compile error: End If without block If

???

 

[Blade Hunter]

Sorry my bad, just take it out (I take it you are using the case method).

You can remove it because the IF and THEN are on the same line which tells VBA that it is a one line if statement so no need to close it.

Cheers

Dan

 

[Jossilyn]

I used the first example you provided.

Thanks Dan that fixed it

 

[Blade Hunter]

I used the first example you provided.

Thanks Dan that fixed it

Yeah I see it now, got two of them in there, one just before end sub aswell