Excel VBA Formula

[risoam]

Can someone help me with this:

I have an excel spreadsheet that I am trying to write some code for:

Public Function mySuffix(suffix As String) As String
    If CInt(suffix) < 100 Then
        mySuffix = suffix
    Else
        myChr = CInt(suffix) - 100
        If myChr <= 26 Then
            mySuffix = Chr(96 + myChr)
        Else
            leadChr = CInt(myChr / 26)
            farChr = myChr - (leadChr * 26)
            mySuffix = leadChr & farChr
        End If
    End If
End Function

In my spreadsheet I have reference the formula by using: =mySuffix(m2) where m2 is the cell I want to run the formula on.

What I get is: #NAME? and I cant figure it out. I tried replacing all code to just return "a" or anything to make sure it wasnt my code and I get the same thing. It is like Excel doesnt see my function. I right-clicked the worksheet and put the above in the view code section.

Thanks.

 

[SkipVought]

Hi,

What you are passing is a RANGE (M2 is a range address)

Public Function mySuffix(suffix As Range) As String
    If Not IsNumeric(suffix) Then
        mySuffix = "NA"
    Else
        If CInt(suffix) < 100 Then
            mySuffix = suffix
        Else
            myChr = CInt(suffix) - 100
            If myChr <= 26 Then
                mySuffix = Chr(96 + myChr)
            Else
                leadChr = CInt(myChr / 26)
                farChr = myChr - (leadChr * 26)
                mySuffix = leadChr & farChr
            End If
        End If
    End If
End Function

theres a better way to return n/a. I can't find it tho.




Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

To return a true N/A# error that will be seen as an error by XL use:

my suffix = CVErr(xlErrNA)

;-)

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[risoam]

THanks but actually what I am trying to do is based off of the vaule in the cell which is a number, I want to return that number if it is less than 100, otherwise i want to return a, b, c... aa, ab, ac... that is what I am trying to accomplish. It just seems that Excel isnt recognizing my function... If I replace all of my code I still get that #NAME? error.

 

[SkipVought]

geez, it worked for me. That's exactly what it does. The range value is you number.

I put 99 in A1 and then refererce A1 as the argument and it returns 99.

What is a, b, c... aa, ab, ac... ???

I think that your logic is flawed.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

I got it to work (I think), but since I'm not exactly sure what you want to do, I only tested if it returns an error or not.

Function mySuffix(suffix As String) As String
    If CInt(suffix) < 100 Then
    Dim leadchr As String, farchr As String
    Dim mychr As Integer
        mySuffix = suffix
    Else
        mychr = CInt(suffix) - 100
        If mychr <= 26 Then
            mySuffix = Chr(96 + mychr)
        Else
            leadchr = CInt(mychr / 26)
            farchr = mychr - (leadchr * 26)
            mySuffix = leadchr & farchr
        End If
    End If
End Function

Is there a MAX value here or what?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Put your function in a MODULE

NOT a Sheet Object!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[risoam]

Yah I did notice I missed a line:
Public Function mySuffix(suffix As String) As String
If CInt(suffix) < 100 Then
mySuffix = suffix
Else
myChr = CInt(suffix) - 100
If myChr <= 26 Then
mySuffix = Chr(96 + myChr)
Else
leadChr = CInt(myChr / 26)
farChr = myChr - (leadChr * 26)
mySuffix = Chr(leadChr) & Chr(farChr)
End If
End If
End Function

But how come it keeps telling me #NAME? in the cell? Did you have to do anything to make the forumla work in the worksheet?

 

[SkipVought]

MODULE

MODULE

MODULE

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[risoam]

THANK YOU!

 

[SkipVought]

Put this function in a MODULE

Public Function mySuffix(suffix As Range) As String
    If Not IsNumeric(suffix) Then
        mySuffix = CVErr(xlErrNA)
    Else
        If CInt(suffix) < 100 Then
            mySuffix = suffix
        Else
            myChr = CInt(suffix) - 100
            If myChr <= 25 Then
                mySuffix = Chr(65 + myChr)
            Else
                leadchr = CInt(myChr / 26)
                farChr = Chr(65 + myChr - (leadchr * 26))
                leadchr = Chr(65 + leadchr - 1)
                mySuffix = leadchr & farChr
            End If
        End If
    End If
End Function

for numbers greater than 99, I got
A thru Z for 100 - 125
AA, AB etc

Is that what you want?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Actually found another error if I am on the right track.

I am using INT(myChr / 26) rather than CInt(MyChr / 26)

Public Function mySuffix(suffix As Range) As String
    If Not IsNumeric(suffix) Then
        mySuffix = CVErr(xlErrNA)
    Else
        If CInt(suffix) < 100 Then
            mySuffix = suffix
        Else
            mychr = CInt(suffix) - 100
            If mychr <= 25 Then
                mySuffix = Chr(64 + mychr)
            Else
                leadchr = Int(mychr / 26)
                farChr = Chr(65 + mychr Mod 26)
                leadchr = Chr(64 + leadchr)
                mySuffix = leadchr & farChr
            End If
        End If
    End If
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com