fiveworlds Posted May 30, 2019 Share Posted May 30, 2019 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. Link to comment Share on other sites More sharing options...
Bufofrog Posted May 30, 2019 Share Posted May 30, 2019 It should work. Are you sure that you are writing the notation correctly for excel. What is the error message? Link to comment Share on other sites More sharing options...
fiveworlds Posted May 30, 2019 Author Share Posted May 30, 2019 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) Link to comment Share on other sites More sharing options...
Strange Posted May 30, 2019 Share Posted May 30, 2019 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. Link to comment Share on other sites More sharing options...
fiveworlds Posted May 30, 2019 Author Share Posted May 30, 2019 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. Link to comment Share on other sites More sharing options...
Bufofrog Posted May 31, 2019 Share Posted May 31, 2019 (edited) 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, 2019 by Bufofrog it is Exit not End Link to comment Share on other sites More sharing options...
Strange Posted May 31, 2019 Share Posted May 31, 2019 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 Link to comment Share on other sites More sharing options...
Sensei Posted May 31, 2019 Share Posted May 31, 2019 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. Link to comment Share on other sites More sharing options...
fiveworlds Posted May 31, 2019 Author Share Posted May 31, 2019 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 Link to comment Share on other sites More sharing options...
Strange Posted May 31, 2019 Share Posted May 31, 2019 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! 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now