Evaluate Cell based on Number format VBA

[bajlewis]

Hi,

I'm trying to insert a random number into the unprotected cells on a worksheet depending on the type of number formatting in the cell. There are 4 basic cases:

1. Number formatted as percent
2. Number Formated general with no decimal places
3. Number formatted general with two decimal places
4. Number formatted in thousands

The code below only contains three criteria, I was trying to get that to work first....Based on this criteria I want to insert a random number of an appropriate size. Please find the vba below. Thanks in advanced for any help.

Sum randomnbr()
Dim F as String

For Each c In ActiveSheet.Range("A1:AK2500")
F = c.numberformat
If c.Locked = False And IsNumeric(c.Value) = True And c.EntireRow.Hidden = False And c.EntireColumn.Hidden = False Then
If F Like "*#,##0*" Then
c.Value = Round(Rnd, 0) * 1000000
If F Like "*%*" Then
c.Value = Round(Rnd, 2)
Else: c.Value = Round(Rnd, 2) * 1000
End If
End Sub

 

[Andrew Poulsom]

How can you have a General format with 2 decimal places? What actually are the possible formats (as seen in the Type box on the Numbers tab of the Format Cells dialog)?

 

[bajlewis]

Well the "number format with no decimals" displayed as this:
_(* #,##0_);_(* (#,##0);_(* "-"??_);_
@_)

The format displayed in thousands contains this:
0,

The percent is like this:
0.00%

The number format with decimals is like this:
0.00

I was wondering if it were possible to do wild card searches on the format type in case say a percent was only formatted to 1 or zero decimal places. Thanks.

 

[Andrew Poulsom]

Yes, it's possible. Here's an example:

Sub randomnbr()
    Dim c As Range
    Dim F As String
    For Each c In ActiveSheet.Range("A1:A4")
        F = c.NumberFormat
        If c.Locked = False And IsNumeric(c.Value) = True And c.EntireRow.Hidden = False And c.EntireColumn.Hidden = False Then
            If F Like "*," Then
                MsgBox "Thousands"
            ElseIf F Like "*%" Then
                MsgBox "Percent"
            ElseIf F Like "*.00" Then
                MsgBox "Two decimals"
            ElseIf F Like "_(*" Then
                MsgBox "Accounting"
            End If
        End If
    Next c
End Sub

 

[bajlewis]

That worked, thanks for the help.