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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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!

Code:
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
Solution
I really want to make sure it was a number (including 0) and not text or blank. But this will work. Thank You!!
 
Upvote 0
I think you would need nested ifs like

VBA Code:
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
 
Upvote 0
Thanks!! Very much appreciate your time and help
 
Upvote 0
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
 
Upvote 0
Note that this part:
VBA Code:
IsNumeric(Sheets("3 Enter Quote Data").Range("H" & Z).Value) = True
can simply be reduced to this:
VBA Code:
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 ...
 
Upvote 0
Thanks. appreciate you taking the time to teach us. It definitely helps.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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