VBA If a cell contains a value in a range of cells ensure that another cell in a different column in the same row also has a value

[DavyJones90]

Hello I have a Macro which automatically creates a PDF of an Analysis sheet, now the problem I am facing is that I want to build in a check step to ensure the full analysis was completed fully,

So my idea is something like this

'(FAKE CODE)
IF a cell in Range("N4:N14") <> 0 OR "" check the the same Row in Column("I")  and ensure that it's value is more than 0 OR "" Then

MsgBox "PDF Could not be created because a Value in Coloumn I is missing "

Else
ThisWorkbook.Worksheets("AUTO ENTRY POINTS").Range("O4").NumberFormat = "dd/mm/yy"
    ThisWorkbook.Worksheets("AUTO ENTRY POINTS").Range("O4").Value = Date
    ThisWorkbook.Worksheets("AUTO ENTRY POINTS").Cells.Copy
    newWorkbook.Worksheets(1).Cells.PasteSpecial Paste:=xlPasteFormats
    newWorkbook.Worksheets(1).Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    newWorkbook.Worksheets(1).Range("A:A").ClearContents
    newWorkbook.Worksheets(1).Name = "Values"
    
    Application.CutCopyMode = False
    Application.CutCopyMode = True
    newWorkbook.Sheets.Add.Name = "XL FILE"
    newWorkbook.Sheets("XL FILE").Move After:=Sheets("Values")
    ThisWorkbook.Worksheets("XL FILE").Cells.Copy
    newWorkbook.Worksheets("XL FILE").Cells.PasteSpecial Paste:=xlPasteFormats
    newWorkbook.Worksheets("XL FILE").Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Application.CutCopyMode = False

    newWorkbook.SaveAs newWbPath
End if

Thanks in advance!

 

[Siddharth Rout]

OK I dont understand how or why it works, but this is what I changed and it works, Thanks for your help!

            If Val(aCell.Value2) <> Empty Then
                If Not .Range("I" & aCell.Row).Value2 * 10 > 0 Then

What you are doing is checking if the cell is not empty. That will not check if the value is greater than 0. What if you typed a string instead of a number? Also if there is a space in your cell, Empty will return False. If you are 100% sure that the cell will always have numeric values then you can use

If Len(Trim(aCell.Value2)) <> 0 Then

instead of

If Val(aCell.Value2) <> Empty Then

 

[Siddharth Rout]

Returns False

That means the cell doesn't have a numeric value greater than 0 (0.3). Try this as well

?Val(.Range("I" & aCell.Row).Value2) = 0.3

 

[DavyJones90]

That means the cell doesn't have a numeric value greater than 0 (0.3). Try this as well

?Val(.Range("I" & aCell.Row).Value2) = 0.3

I think a spent the entire day trial and erroring every possible variation of this sentence and I am just glad is works now, so I leave it, but I am replacing the Empty part as you said. Although it is not possible to make spaces or anything but a numeric value in this field, as the values can only be selected by a drop down menu

Thanks again, I wouldn't have managed without your help