Checking number format within a cell

jeskit · May 5th, 2011, 10:09 AM

Hi

Thanks

so in the loop i should put:

Code:

 PPNewIR.cells(Nrow, 11).Value = fTryThis

I ran the macro and i got an error of "argument not optional"

mtranchi · May 5th, 2011, 10:13 AM

yes but you need to pass "fTryThis" the range, the single cell as a range, that has the formatting information you're figuring out. If I recall, you named your range "cell", so it would be something like

Code:

 PPNewIR.cells(Nrow, 11).Value = fTryThis(cell)

jeskit · May 5th, 2011, 10:23 AM

Hi

Ok done that but now i get an error saying "sub or function not defined"

Do i need to declare the function with variables or should the fuction go before the sub routine?

mtranchi · May 5th, 2011, 10:29 AM

this part:

Code:

Function fTryThis(Target As Range) As String

    'reset iDigitsAfterDecimalPoint
    iDigitsAfterDecimalPoint = 0
    
    'get the formatting style for the cell
    stFormatStyle = Target.NumberFormat
    
    'if the format style is "general", return the default string and exit the function
    If stFormatStyle = "General" Then
        fTryThis = "%10.0f%%"
        Exit Function
    End If
    
    'if the format style is "comma", find the number of digits after decimal and return the string
    'this is what the NumberFormat string looks like for a "comma" format:
        '   _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
    If InStr(stFormatStyle, "#,##") Then
        'find the period
        iStart = InStr(stFormatStyle, ".")
        'find the ending 0
        iEnd = InStr(iStart, stFormatStyle, "_")
        'subtract the two to get the number of digits after the period
        iDigitsAfterDecimalPoint = iEnd - iStart - 1
        'return the string
        fTryThis = "%10." & iDigitsAfterDecimalPoint & "f%"
        'exit the function
        Exit Function
    End If
    
    'this looks at the last character of the NumberFormat string and if it's a percentage sign, then sets a Boolean to true (this will be used later to put an extra "%" sign on the return string if need be
    If Right(stFormatStyle, 1) = "%" Then
         bIsPercentage = True
    Else
         bIsPercentage = False
    End If
    'find the decimal point position:
    iStart = InStr(stFormatStyle, ".")
    
    If iStart = 0 Then 'there is no period
        iDigitsAfterDecimalPoint = 0
    Else 'there is a period
        iDigitsAfterDecimalPoint = Len(stFormatStyle) - iStart
    End If
    
    'if it's a percentage format with digits after period, subtract one to account for the "%" sign
    If bIsPercentage And iStart > 0 Then
        If bIsPercentage Then iDigitsAfterDecimalPoint = iDigitsAfterDecimalPoint - 1
    End If
    'Now you should have all the pieces you need so just put it together:
    stResult = "%10." & iDigitsAfterDecimalPoint & "f%"
    If bIsPercentage Then stResult = stResult & "%"
    
    'return the string
    fTryThis = stResult
End Function

needs to be put in all by itself. You're missing the top line of it I think.
And this part:

Code:

Option Explicit

'I declared these variables at the module level so your code doesn't have to allocate memory everytime the function is called
Dim stFormatStyle As String
Dim iStart As Integer
Dim iEnd As Integer
Dim iDigitsAfterDecimalPoint As Integer
Dim stResult As String
Dim iDecimals As Integer
Dim bIsPercentage As Boolean

needs to be put in at the tip-top of the module you're working in.

jeskit · May 5th, 2011, 10:38 AM

Hi

Ok it the macro ran but when it got to the line below it gave an error of "Object required"

Code:

PPNewIR.cells(Nrow, 11).Value = fTryThis(cell.Value)

I have decalred the variables at the top after the Option Explict line and the function is underneith the sub, is this right?

mtranchi · May 5th, 2011, 10:40 AM

yes that is correct, but you're passing the value of the cell, not the range object:

Code:

'incorrect
PPNewIR.cells(Nrow, 11).Value = fTryThis(cell.Value)

'correct
PPNewIR.cells(Nrow, 11).Value = fTryThis(cell)

jeskit · May 5th, 2011, 10:46 AM

Hi

Ok tried that and got an error of "ByRef argument type mismatch"

mtranchi · May 5th, 2011, 10:50 AM

is your "cell" variable pointing to a single cell?

mtranchi · May 5th, 2011, 10:50 AM

actually can you post all the code so i can look at it?

jeskit · May 5th, 2011, 10:54 AM

Not sure as i defined it as a range and the loop goes through teh range provided and cell variable is used to defined each cell it comes to.

I hope that makes sense. The line below is where the cell variable (highlighte din red) is defined first. It is then used throughout the loop to define the activecell

Code:

For Each cell In PPWBForm.Range("F4:U644")