Change If statement so it considers the value 0 ($0).

[gheyman]

I need to adjust this so that the user can enter "0" as the value in H
( If Sheets("3 Enter Quote Data").Range("H" & Z).Value > 0 Then)

If I change it to " > -1" instead of "> 0" then its looking at blanks as if they are "0".

How do I adjust this code so the user can enter 0 in column H? column H is the Unit cost so they are entering a dollar amount. Sometime it will be zero.

Thanks!

Sub AddToCostSourceDetailsTemplate()

Dim SumExcess As Double
Dim SumNRE As Double
Dim SumTariff As Double
Dim SourceType As Variant
Dim SourceTable As ListObject
Dim PATH As Variant
Dim VendorTable As ListObject


  SumExcess = Application.WorksheetFunction.Sum(Sheet4.Range("I24:I56"))
  SumNRE = Application.WorksheetFunction.Sum(Sheet4.Range("J24:J56"))
  SumTariff = Application.WorksheetFunction.Sum(Sheet4.Range("K24:K56"))
  
  Set SourceTable = Sheet6.ListObjects("Source_Type")
  Set VendorTable = Sheet3.ListObjects("Selected_Vendors")
  
  'Identify Current User
  CurrentUser = Environ("UserName")


Dim CSDLR As Long

For Z = 24 To 56

    If Sheets("3 Enter Quote Data").Range("H" & Z).Value > 0 Then

        CSDLR = Sheets("Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row + 1

        'Material
            Sheets("Cost Source Details").Range("A" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("F18").Value
        'Material Type
            Sheets("Cost Source Details").Range("B" & CSDLR).Value = "Part"
        'Type
            SourceType = Application.VLookup(Sheets("3 Enter Quote Data").Range("I12").Value, SourceTable.Range, 2, False)
            Sheets("Cost Source Details").Range("C" & CSDLR).Value = SourceType
        'PP ID
            Sheets("Cost Source Details").Range("D" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L5").Value
        'PP Revision
            Sheets("Cost Source Details").Range("E" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("P5").Value

'*********************************************************************************

        'From Qty
            Sheets("Cost Source Details").Range("F" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("F" & Z).Value
            Sheets("Cost Source Details").Range("G" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("G" & Z).Value
            Sheets("Cost Source Details").Range("H" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("H" & Z).Value

        If Sheets("3 Enter Quote Data").Range("I" & Z).Value > 0 And Sheets("3 Enter Quote Data").Range("J" & Z).Value > 0 And Sheets("3 Enter Quote Data").Range("K" & Z).Value > 0 Then
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I" & Z).Value & "} " & "{TARIFF: $" & Sheets("3 Enter Quote Data").Range("K" & Z).Value & "}" & " {NRE: $" & Sheets("3 Enter Quote Data").Range("J" & Z).Value & "}"

        Else
    
        If Sheets("3 Enter Quote Data").Range("J" & Z).Value > 0 And Sheets("3 Enter Quote Data").Range("I" & Z).Value > 0 Then
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I" & Z).Value & "} " & "{NRE: $" & Sheets("3 Enter Quote Data").Range("J" & Z).Value & "}"
        Else

        If Sheets("3 Enter Quote Data").Range("J" & Z).Value > 0 And Sheets("3 Enter Quote Data").Range("K" & Z).Value > 0 Then
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I" & Z).Value & "} " & "{NRE: $" & Sheets("3 Enter Quote Data").Range("J" & Z).Value & "}"
        Else
    
        If Sheets("3 Enter Quote Data").Range("I" & Z).Value > 0 And Sheets("3 Enter Quote Data").Range("K" & Z).Value > 0 Then
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I" & Z).Value & "} " & "{TARIFF: $" & Sheets("3 Enter Quote Data").Range("K" & Z).Value & "}"

        Else
    
        If Sheets("3 Enter Quote Data").Range("I" & Z).Value > 0 Then
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I" & Z).Value & "}"

        Else
    
        If Sheets("3 Enter Quote Data").Range("K" & Z).Value > 0 Then
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value & " " & "{NRE: $" & Sheets("3 Enter Quote Data").Range("K" & Z).Value & "}"

        Else
    
        If Sheets("3 Enter Quote Data").Range("J" & Z).Value > 0 Then
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value & " " & "{TARIFF: $" & Sheets("3 Enter Quote Data").Range("K" & Z).Value & "}"

        Else
            Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L" & Z).Value


        End If
        End If
        End If
        End If
        End If
        End If
        End If

    End If

Next Z

End Sub

 

[JGordon11]

If you only care that the cell is not blank then

If Len(Sheets("3 Enter Quote Data").Range("H" & Z).Value) > 0 Then

or

If Sheets("3 Enter Quote Data").Range("H" & Z).Value) <> "" Then

 

[gheyman]

I really want to make sure it was a number (including 0) and not text or blank. But this will work. Thank You!!

 

[JGordon11]

I think you would need nested ifs like

If isnumeric(x) then
      if x<>"" then
          '... code for when numeric and not blank ...
      else
           '... code for when blank
      endif
else
     '... code for when not numeric
endif

 

[gheyman]

Thanks!! Very much appreciate your time and help

 

[gheyman]

Just as an FYI; I went with If IsNumeric(Sheets("3 Enter Quote Data").Range("H" & Z).Value) = True And Sheets("3 Enter Quote Data").Range("H" & Z).Value <> "" Then

 

[Joe4]

Note that this part:

IsNumeric(Sheets("3 Enter Quote Data").Range("H" & Z).Value) = True

can simply be reduced to this:

IsNumeric(Sheets("3 Enter Quote Data").Range("H" & Z).Value)

as "IsNumeric" is a boolean function that returns TRUE or FALSE.

Another way of saying it, is you just need:
If TRUE ...
instead of:
IF TRUE = TRUE ...

 

[gheyman]

Thanks. appreciate you taking the time to teach us. It definitely helps.

 

[Joe4]

Thanks. appreciate you taking the time to teach us. It definitely helps.

You are welcome.
Glad we could help.

Yes, the reason why you usually use equal to, greater than, less than, etc is to elicit a TRUE/FALSE value that you can evaluate in your IF functions.
Since the functions that begin with "IS..." already return those boolean values of TRUE/FALSE, it usually isn't necessary to use those mathematical operands.