Compile error in code

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
With this code I get a compile error
"function call on left hand side of assignment must return variant or object"

MsgBox= is highlighted

Code:
If [A16].Value = "B/Order" Then
    Application.DisplayAlerts = False
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ActiveSheet.Range("A17:A35")

For Each rng2 In rng1
If rng2 = "" Then
MsgBox = "hello"
Next
Else
ActiveSheet.Delete

End If
    Application.DisplayAlerts = True

What I'm trying to do is this:
for each cell in [A17:A35] if the adjacent cell (same row column B) is empty then show a msggbox

Thank you
 
B16 is empty.
I've switched things around abit and now I get to see the msgbox BUT when I introduce the "Else" I'm in trouble.(Else without IF)
This is what it looks like:
Code:
Sub test()

        
        For Each cell In Range("A17:A35")
            If cell.Value <> "" And cell.Offset(0, 1).Value = "" Then
                MsgBox cell.Address & " is not blank." & vbCr & _
                       cell.Offset(0, 1).Address & " is blank."
                Exit Sub
            End If
        Next cell
        Else
    If Range("A6").Value = "B/Order" Then
        ActiveSheet.Delete
    End If
End Sub


My bad I have A6 instead of A16 ALSO took the Else out
Now I just have to suppress the prompt to delete the sheet and s/b OK
 
Last edited:
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
not sure what "deleted" means in your last post ... does this mean you answered and then deleted the answer?
anyhow this is what I have now and I still get the prompt to delete or not the sheet.
Code:
Sub test()

        
        For Each cell In Range("A17:A35")
            If cell.Value <> "" And cell.Offset(0, 1).Value = "" Then
                MsgBox cell.Address & " is not blank." & vbCr & _
                       cell.Offset(0, 1).Address & " is blank."
                Exit Sub
            End If
        Next cell
    Application.EnableEvents = False
    If Range("A16").Value = "B/Order" Then
        ActiveSheet.Delete
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
I'm really tired tonight ... s/b Application.DisplayAlerts = False/True
Thank you AlphaFrog & have a great evening
 
Upvote 0

Forum statistics

Threads
1,224,613
Messages
6,179,903
Members
452,948
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top