Excel VBA: IF Then Else not working

bt_24

New Member
Joined
Jan 16, 2017
Messages
19
Hi Everyone,

I am trying to execute what I thought was a few simple lines of code but it is not working as expected. The test that I am trying to execute is if there are values greater than .07 then print them on another worksheet, however it is printing everything including values that are 0. Can anyone help?

Code:
Dim lastRow As Long
Dim mlRow As Long
            lastRow = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
           
        Dim wrdArray() As String
        Dim cleanKWarray() As String
            
            wrdArray = Split(Range("A1"), " - ")
            cleanKWarray = Split(wrdArray(3), " (")
                
            
         Dim y As Long
                          
          Range("H3:H" & lastRow).Formula = "=If(OR(b3=""You"", b3=""test.com""), 0,Iferror(E3 * f3, 0))"
              Dim testValue As Integer
                  testValue = 0.07
              For y = 3 To lastRow
              mlRow = results.Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                 
                 If Cells(y, 8).Value = 0 And Cells(y, 8).Value < testValue Then
                    'do nothing
                 'Debug.Print Cells(y, 8).Value
                 
                    Else
                    results.Range("A" & mlRow + 1) = wrdArray(2)
                    results.Range("B" & mlRow + 1) = cleanKWarray(0) 
                    results.Range("C" & mlRow + 1) = Cells(y, 1).Value 
                    results.Range("E" & mlRow + 1) = Cells(y, 2).Value 
                    results.Range("F" & mlRow + 1) = Cells(y, 5).Value 
                    results.Range("G" & mlRow + 1) = Cells(y, 6).Value 
                    results.Range("H" & mlRow + 1) = Cells(y, 8).Value 
                
                End If
             
              Next
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You have declared testvalue as integer therefore when you assign the value of 0.07 it will round it to zero
 
Upvote 0
Hi Everyone,

I am trying to execute what I thought was a few simple lines of code but it is not working as expected. The test that I am trying to execute is if there are values greater than .07 then print them on another worksheet, however it is printing everything including values that are 0. Can anyone help?

Code:
          -------snipped--------                          
          Range("H3:H" & lastRow).Formula = "=If(OR(b3=""You"", b3=""test.com""), 0,Iferror(E3 * f3, 0))"
              [COLOR="#FF0000"]Dim testValue As Integer[/COLOR]
                  testValue = 0.07
              For y = 3 To lastRow
              mlRow = results.Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                 
                 If Cells(y, 8).Value = 0 And Cells(y, 8).Value < testValue Then
                    'do nothing
          -------snipped--------
You declared testValue as an Integer, so the 0.07 got rounded to 0 for the assignment to that variable. Change the declaration from Integer to Double and it should then work for you.
 
Upvote 0
@jimrward & @Rick Rothstein - thank you for the help it is greatly appreciated. Knew it had to be something simple I was forgetting
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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