Macro problem with more than 1 IF

[Xineq]

Hey Mr.Excel

I'm working on a macro that controlls worksheet. I want my macro to check for each cell if the cell.value is empty in the range from ("F4:F20") and if that is true then check the if the range from U4:AD4 dont have the value = "LB0.1"

Sub populateB()Dim rng1 As Range
Dim rng2 As Range


Set rng1 = Range("F4:F15")

For Each cel In rng1
    If cel.Value = "" Then cel.Offset(0, 25).Value = "LB0.1"
Next


End Sub

this is my code

Hope you can help me

 

[Stridhan]

when using IF you have to end the IF with "End if" after your "Then" statement.

 

[Eric Golf]

Hi Xineq,

Not certain what you are trying to do, but if it is check that both column F is blank and U is not = to "LB0.1" then the below should work

Sub populateB()Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("F4:F15")

For Each cel In rng1
    If cel.Value = "" AND cel.Offset(0, 25).Value = "LB0.1" Then
[I]' Your code here[/I]
    End If
Next

End Sub

Hope this helps

Eric

btw - Stridhan, you can do IFs on one line without End If, though I prefer to avoid it myself.

 

[L. Howard]

Hi Xineq,

Maybe start with this and adjust.

A bit vague on what you want.

Where if F column cell is blank and U column to AD column cells do NOT have "LBO.1" then nothing.
Else if "LBO.1" does occur in U to AD then a message box alert of the row it is in.

Howard

Option Explicit

Sub populateB()
Dim rng1 As Range, rng2 As Range, cel As Range
Dim cRow As Long

Set rng1 = Range("F4:F15")

For Each cel In rng1
 Set rng2 = cel.Offset(, 15).Resize(1, 10)

  If cel.Value = "" And Application.WorksheetFunction.CountIf(rng2, "LB0.1") > 0 Then
     cRow = cel.Row
     MsgBox "Row " & cRow & " = LB0.1"
  End If
  
Next

End Sub

 

[Rent23]

Give a try:

Sub testdbif()

For xx = 4 To 20
    If Range("F" & xx) = "" Then
        For zz = 21 To 30
            If Cells(xx, zz) <> "LB0.1" Then
                '''your code here in case of LB0.1 is present'''
            Else
            End If
        Next zz
                '''your code here in case of LB0.1 is not present'''
    End If
Next xx
                
End Sub

 

[Stridhan]

btw - Stridhan, you can do IFs on one line without End If, though I prefer to avoid it myself.

Thanks Eric Golf, didnt know (learning by doing)

 

[Teeroy]

There is a lot of confusion between your code and the problem statement. BTW offset gives a position relative to the parent object so a 25 column offset to column F gives column "AE".

Assuming your text is actually your requirement try:

Sub populateB()
Dim Cel

If Evaluate("=Countif(U4:AD4,""LB0.1"")") > 0 Then
    For Each Cel In Range("F4:F20")
        If Cel.Value = "" Then
    ' Your code here
        End If
    Next
End If
End Sub

 

[Xineq]

Wow thanks guys, really awesome with the help

working like a charm now