VBA if and multiple statements

Question

So I am trying to ensure that all fields are filled on a form that i am creating however I am having some trouble with multiple if statements.

If Not IsEmpty(barcode) And Not IsEmpty(takenstock) And Not IsEmpty(staff) And Not IsEmpty(jobnumber) Then

    Sheet2.Cells(emptyrow, 1).Value = staff
    Sheet2.Cells(emptyrow, 2).Value = Product
    Sheet2.Cells(emptyrow, 3).Value = ProductCode
    Sheet2.Cells(emptyrow, 4).Value = takenstock
    Sheet2.Cells(emptyrow, 5).Value = jobnumber
    Sheet2.Cells(emptyrow, 6).Value = Mydate
    Sheet2.Cells(emptyrow, 7).Value = Mytime
    'removes stock from list of products
    Sheets("List of products").Range(findproduct).Value = selectproduct - takenstock
    'clears sheet
    Sheet1.Range("b1").Value = ""
    Sheet1.Range("b6").Value = ""
    Sheet1.Range("b5").Value = ""
    Sheet1.Range("b7").Value = ""
Else
    MsgBox "Please fill Barcode, stock to be taken,staff field and the job number" & vbNewLine & "Thank you", vbOKOnly, "Missing fields"

End If

This was my first attempt which didn't work, so i tried

If Not IsEmpty(barcode) And Not IsEmpty(takenstock) Then
    if Not IsEmpty(staff) And Not IsEmpty(jobnumber) Then

I have also tried removing the second Not's

Feels like I have tried everything but the code just isn't interested

Thank you in advance for your help

xpofer · Accepted Answer · 2017-10-12 11:00:42Z

2

try

If (Not IsEmpty(barcode)) And (Not IsEmpty(takenstock)) And 
    (Not IsEmpty(staff)) And (Not IsEmpty(jobnumber)) Then

Always err on the side of more parenthesis...

Also, it depends on what Type barcode, takenstock, staff, and jobnumber are. I can't see your declarations.

If you declared them as Range and set them with something like

set barcode = Range("B7")

or something to that effect, your code should work fine. But if you declared them as String or Integer types and set them with

barcode = Range("B7").value

then your expression will always evaluate FALSE. isEmpty() is only for objects, not primitive types.

edited Oct 12, 2017 at 11:00

answered Oct 12, 2017 at 9:54

xpofer

1721 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jakob Busk Sørensen Over a year ago

When in doubt: Add more parenthesis

Kingbuttmunch Over a year ago

I have tried playing around using parenthesis, still no luck :(

xpofer Over a year ago

In that case, what type are barcode, takenstock, staff, and jobnumber? Are they cell ranges, strings, integers,...?

Kingbuttmunch Over a year ago

barcode, staff and jobnumber are As String and takenstock As Integer, do you think this could be affecting it?

Jakob Busk Sørensen Over a year ago

Integers are 0 (zero) as default. So I don't think that IsEmpty() will work very well on those.

|

Collectives™ on Stack Overflow

VBA if and multiple statements

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related