How do I write an if statement in excel vba to compare two numbers from worksheet cells that are in scientific notation? I thought it would be as simple as comparing the cell.value but that didn't work. 

It should work.  Are you sure that you are writing the notation correctly for excel.  What is the error message?

There is none I just want it to do one thing if the number is larger than the other and it doesn't execute. The numbers are negative to as in -2.3553456E23 > -4.3527892E21. It is also an old Excel version (2013)

Numbers are just numbers; it is the format of the cell that displays them in scientific notation (or dates or whatever).

I have just tried =IF(D2>D3, "y","n") with your numbers and it seems to work.

Are you sure the content of the cell is not being interpreted as text for some reason? (eg. having a space before the number will prevent Excel seeing it as a number) You can find out by using the format menu (Ctrl 1) and changing the format to Number or Currency.

Quote

 

=IF

 

That always worked that is the function version and not the vba version. I have to use vba because my function is >8124 characters. I found how to use it in vba though, it is iif not if confusing.

Like this?

Dim First, Second

First = Range ("A1").value

Second = Range("A2").value

If  Second>First Then 

    Exit Sub   "This will end the subroutine"

Else

 EndIf       "if Second is less than First then the subroutine will finish"   

 

Edited May 31, 20196 yr by Bufofrog
it is Exit not End

7 hours ago, fiveworlds said:

That always worked that is the function version and not the vba version. I have to use vba because my function is >8124 characters. I found how to use it in vba though, it is iif not if confusing.

Sorry missed the vba bit

not sure why plain if wouldn’t work

8 hours ago, fiveworlds said:

I have to use vba because my function is >8124 characters.

If I have to process a lot of data, I am exporting CSV, making C/C++ parser which is processing them extremely fast, and then importing CSV back to OpenOffice/Excel.

 

Quote

If I have to process a lot of data, I am exporting CSV, making C/C++ parser which is processing them extremely fast, and then importing CSV back to OpenOffice/Excel.

Yeah I have one of those already but there is huge amounts of data 

This was the actual code that was causing an error

  If IsEmpty("F29") = False And Round(Range("F29").Value, 2) >= 0.05 Then
    content = content & vbCrLf & "- Display Time (Seconds) " & WorksheetFunction.Round(Range("'Chart Data'!P34") * 100, 0) & "% worse"
  End If

where F29 = 0.121043590973107

8 minutes ago, fiveworlds said:

Yeah I have one of those already but there is huge amounts of data 

This was the actual code that was causing an error

  If IsEmpty("F29") = False And Round(Range("F29").Value, 2) >= 0.05 Then
    content = content & vbCrLf & "- Display Time (Seconds) " & WorksheetFunction.Round(Range("'Chart Data'!P34") * 100, 0) & "% worse"
  End If

where F29 = 0.121043590973107

I think that should be 

If IsEmpty(Range("F29"))

Because "F29" is a string and never empty!