VBA if else loop in excel returning #VALUE

Question

Hi All in new to using VBA in excel and was trying to create a function that would look at a number and return it as a six digit number.

The function I wrote to try and accomplish this is below but when I use the command =Res(A1) in a cell I just get a #VALUE! as the answer.

The value in cell one at the moment is 30508.

Any help anyone could offer to resolve this would be greatly appreciated. Thanks Guys.

Function Res(myval As Integer) As Integer

Res = 0

If ((myval > 0) And (myval < 10)) Then    
    Res = myval * 100000

ElseIf ((myval > 9) And (myval < 100)) Then
    Res = myval * 10000

ElseIf ((myval > 99) And (myval < 1000)) Then 
    Res = myval * 1000

ElseIf ((myval > 999) And (myval < 10000)) Then  
    Res = myval * 100

ElseIf ((myval > 9999) And (myval < 100000)) Then
    Res = myval * 10

ElseIf ((myval > 999999) And (myval < 10000000)) Then
    Res = myval / 10

Else
    Res = myval

End If

End Function

danielpiestrak · Accepted Answer · 2012-09-05 19:23:14Z

9

Change Function Res(myval As Integer) As Integer to:

Function Res(myval As Long) As Long

You're hitting the integer maximum.

Long stands for long integer, and you want to use it anytime your number could go above 30k.

edited Sep 5, 2012 at 19:23

answered Sep 5, 2012 at 19:18

danielpiestrak

5,4793 gold badges33 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Patrick Honorez Over a year ago

Right. An integer's maximum is + or - 32767

danielpiestrak Over a year ago

Yeah. I use 30k as a mental stopping point because I always forget the exact #. Thanks for the comment.

user1649972 Over a year ago

That's sorted that, thank you very much for the reply's. That was actually one of the things I thought it would be but when I searched the range for an int I was looking at the wrong int in the table. I used that link msdn.microsoft.com/en-us/library/s3f49ktz%28v=vs.80%29.aspx but looked at the first int range which was 2,147,483,647 but I'm guessing I'm using a short int. Thanks again sheet is complete now and works great :D.

scott · Accepted Answer · 2012-09-05 19:22:13Z

You need to use long instead of integer

integer only covers from -32,768 to 32,767

long covers -2,147,483,648 to 2,147,483,647

    Function Res(myval As Long) As Long

    Res = 0

    If ((myval > 0) And (myval < 10)) Then    
       Res = myval * 100000

    ElseIf ((myval > 9) And (myval < 100)) Then
        Res = myval * 10000

    ElseIf ((myval > 99) And (myval < 1000)) Then 
        Res = myval * 1000

    ElseIf ((myval > 999) And (myval < 10000)) Then  
        Res = myval * 100

    ElseIf ((myval > 9999) And (myval < 100000)) Then
        Res = myval * 10

    ElseIf ((myval > 999999) And (myval < 10000000)) Then
        Res = myval / 10

    Else
        Res = myval

    End If

    End Function

Tim Williams · Accepted Answer · 2012-09-05 21:14:42Z

1

Function Res(myVal as long) As Long
    Res = myVal * 10 ^ ( 6 - Len(Cstr(myVal)) )
End Function

Note - this still suffers from the same problem as the original approach:

If myVal is 9999999 (7 digits) then myVal/10 is 999999.9 and Clng(999999.9) is 10000000 (still seven digits)

edited Sep 5, 2012 at 21:14

answered Sep 5, 2012 at 21:06

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

VBA if else loop in excel returning #VALUE

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related