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]

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim aCell As Range
    Dim ws As Worksheet
  
    '~~> Change this to relevant sheet
    Set ws = Sheet1
  
    With ws
        '~~> Loop through the range and check if the range has value > 0
        For Each aCell In .Range("N4:N14")
            If Val(aCell.Value2) > 0 Then
                If Not Val(.Range("I" & aCell.Row).Value2) > 0 Then
                    MsgBox "PDF Could not be created because a Value in cell I" & aCell.Row & " is missing "
                    Exit Sub
                End If
            End If
        Next aCell
      
        '
        '~~> Rest of the code to create the pdf
        '
    End With
End Sub

 

[DavyJones90]

This would have worked perfectly, except for one small detail. I forgot to mention that the Value is a percentage value. It doesn't take > 0.1 for some reason

 

[Siddharth Rout]

This would have worked perfectly, except for one small detail. I forgot to mention that the Value is a percentage value. It doesn't take > 0.1 for some reason

Slightly confused. Why > 0.1?

Val(aCell.Value2) > 0 will catch decimal values as well.

 

[DavyJones90]

OK, it's probably me, but don't get what I am doing wrong.

So this is part of the sheet

MASTER CURRENCIES.xlsm
	I	J	K	L	M	N
1
2		10 M EXTREMES		BREAKOUT LEVELS
3	M.M	ROC7(+)	ROC7(-)	LEVEL	STR/SFT	RATING
4	30%	0.55	0.25	1.052	SOFT	1(+)
5	30%	0.40	0.5	0.7	STRONG	1(-)
6	30%	0.4	0.3	0	0
7	0%	0.30	0.30	0.854	SOFT
AUTO ENTRY POINTS

Cell Formulas
Range	Formula
I4:I7	I4	=OFFSET(Hourly!F$48,$A4,0)
J4:J7	J4	=OFFSET(Hourly!Q$50,A4,0)
K4:K7	K4	=OFFSET(Hourly!U$50,A4,0)
L4:L7	L4	=OFFSET(Hourly!K$49,A4,0)
M4:M7	M4	=OFFSET(Hourly!M$49,$A4,0)
N4	N4	=IF('USD PM H'!$Y$14="","",'USD PM H'!$Y$14)
N5	N5	=IF('USD PM H'!$Y$17="","",'USD PM H'!$Y$17)
N6	N6	=IF('USD PM H'!$Y$20="","",'USD PM H'!$Y$20)
N7	N7	=IF('USD PM H'!$Y$23="","",'USD PM H'!$Y$23)

and this is my code

Dim aCell As Range
    Dim ws As Worksheet
    Set newWorkbook = Workbooks.Add
    Set ws = ThisWorkbook.Sheets("AUTO ENTRY POINTS")
    
  With ws
        '~~> Loop through the range and check if the range has value > 0
        For Each aCell In .Range("N4:N14")
            If Val(aCell.Value2) > 0 Then
                If Not Val(.Range("I" & aCell.Row).Value2) > 0 Then
                    MsgBox "PDF Could not be created because a Value in cell I" & aCell.Row & " is missing "
                    Exit Sub
                End If
            End If
        Next aCell

It only runs correctly when I set the Value of the cell in I to 100%

 

[Siddharth Rout]

Can you debug the For Each loop and tell me what values are you getting for aCell and .Range("I" & aCell.Row).Value2.

 

[DavyJones90]

aCell is "1(+)" and .Range("I" & aCell.Row).Value2. is 0.3

 

[Siddharth Rout]

Great. Now what do you get when you type

?Val(.Range("I" & aCell.Row).Value2) > 0

in Immediate Window at this point of time?

 

[DavyJones90]

Great. Now what do you get when you type

?Val(.Range("I" & aCell.Row).Value2) > 0

in Immediate Window at this point of time?

Returns False

 

[DavyJones90]

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