Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 3rd, 2011, 09:49 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Checking number format within a cell

Hi
I have an IF statement inside a loop which loops through a cell range on a sheet called "PPIIIFORM" and my if statement should be checking the number format of the cell and doing the following

If its a percentage but no decimal point in sheet "Input_Reference_Table" on the 9th column place "%10.0f%%"

ElseIf its a percentage and has a decimal point in sheet "Input_Reference_Table" on the 9th column place "%10.(decimal point number)f%%"

ElseIf its a number with a decimal point sheet "Input_Reference_Table" on the 9th column place "%10.(decimal point number)f%"

Else place "%10.0f%" in the "Input_Reference_Table" on the 9th column.

Code:
Sub PP3InputRef()
Dim Nrow As Long
Dim cell As Range

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
 If cell.Value <> "" Then Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value

If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
ElseIf Right(cell.NumberFormat, 1) = "0.000%" Then
     Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt(cell.NumberFormat)
ElseIf Right(cell.NumberFormat, 1) = "0.000" Then
     Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt2(cell.NumberFormat)
Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
End If
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

End Sub

Function ParseNFmt(NFmt As String) As String
Dim NDec As Long, NType As String
NType = IIf(Right(NFmt, 1) = "%", "Percent", "Number")
If NFmt Like "*.*" Then ' has decimals
    NDec = Len(NFmt) - InStr(NFmt, ".")
    If NType = "Percent" Then NDec = NDec - 1
Else
    NDec = 0
End If
ParseNFmt = NType & " " & NDec & " decimals"
End Function
 
Function ParseNFmt2(NFmt As String) As String
Dim NDec As Long, NType As String
NType = IIf(Right(NFmt, 1) = "0", "Number")
If NFmt Like "*.*" Then ' has decimals
    NDec = Len(NFmt) - InStr(NFmt, ".")
    If NType = "Number" Then NDec = NDec - 1
Else
    NDec = 0
End If
ParseNFmt = NType & " " & NDec & " decimals"
End Function
However itsdoing all of it except placing the decimal point number in the cells which have decimal points and i know some of them do have a decimal points.

Can anyone help me?

Thanks

Jeskit
 
Old May 3rd, 2011, 10:03 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

One thing you can try to do is passing your ParseNFmt function a hardcoded string that you know has a decimal point, then stepping thru the code to see where the problem is. It seems the problem would be in your If test:
Code:
If NFmt Like "*.*" Then ' has decimals
I've never used the "Like" keyword so not familiar with it. Try using the "Instr" function as your test.
Code:
'The Instr function will return 0 if the text being searched for doesn't exist
If Instr(NFmt, ".") > 0 Then ' has decimals
You can make your code more readable and also execute faster by using a variable for the position of the decimal point and using that
Code:
Function ParseNFmt(NFmt As String) As String
Function ParseNFmt(NFmt As String) As String
Dim NDec As Long, NType As String

Dim iDecimal as Integer
iDecimal = Instr(NFmt, ".")

NType = IIf(Right(NFmt, 1) = "%", "Percent", "Number")
If iDecimal > 0 Then ' has decimals
    NDec = Len(NFmt) - iDecimal
    If NType = "Percent" Then NDec = NDec - 1
Else
    NDec = 0
End If
ParseNFmt = NType & " " & NDec & " decimals"
End Function

Last edited by mtranchi; May 3rd, 2011 at 10:16 AM..
 
Old May 3rd, 2011, 10:51 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

hI

I tried that and i am still not sure what its doing. I know it works when its looking at whether its a percentage of a number.

But i need to also look at how many decimal points there are so i was thing could the two lines below be adapted to include decimal points so it at both a percentage and decimal points and number and decimal points.

Code:
If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
 
Old May 3rd, 2011, 11:06 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

I know nothing of working with number formats of cells but maybe you could do something like this:
Code:
If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
End If
'Looks like no matter what happens in your If blocks you do this, so have it after the If blocks like so:
Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt2(cell.NumberFormat)
 
Old May 3rd, 2011, 11:12 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

To step thru your code to see what it's doing you put your cursor in the sub where you want to start and press F8 on your keyboard. Then keep pressing F8 to step thru the next line. You can hover over varaibles to see their value or add watches by right clicking on the variable and selecting "Add watch," then opening the "Watch" window.
 
Old May 3rd, 2011, 11:25 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

I have made a breakthrough!

I tried it like you said so the decimal point part of the IF statement was after the if statement and was in a seperate column.

It worked perfectly so i tried to combine it so i had:

Code:
    If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"

    ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10." & ParseNFmt(cell.NumberFormat) & "f%%"

    Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If
But it didn't work it just ignored the decimal point line. When it was in seperate column it puts "Number 2 decimals" in the cells. so i'm not sure if its the function or the if statement
 
Old May 3rd, 2011, 11:37 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

it looks like you can just skip your ParseNFmt function altogether as you're already testing how many decimal points you have. Try this:
Code:
If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"

'Try just entering the string you want directly
    ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Percentage 2 Decimals f%%"



    Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If
 
Old May 3rd, 2011, 11:42 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i just noticed the problem. When you're testing the number format you're only returning the last character for the percentage thing:
Code:
ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then

'you need to check the last 5 characters like so:
ElseIf Right(cell.NumberFormat, 5) = "0.00%" Then
you need to change the number of characters to check with the Right() function from 1 to 5
 
Old May 3rd, 2011, 11:52 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Great that works!

One last question how would you rewrite that line of code so it doesn't look at percentages just numbers?

Code:
    ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Percentage 2 Decimals f%%"
 
Old May 3rd, 2011, 12:08 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i think you can just specify the number format in one of your ElseIf blocks:
Code:
    ElseIf Right(cell.NumberFormat, 4) = "0.00" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Number 2 Decimals f%%"
There's probably a more succinct way to do it too. Try something like this:
Code:
Dim stFormatStyle as String
Dim iPeriodPosition as Integer
Dim iDecimals as Integer
Dim bIsPercentage as Boolean
Dim stCurrentStyle as String

'start loop here

    stFormatStyle = cell.NumberFormat

    If Right(stFormatStyle, 1) = "%" Then
         bIsPercentage = True
         stCurrentStyle = "Percent"
    Else
         bIsPercentage = False
         stCurrentStyle = "Decimal"
    End If

    iPeriodPosition = Instr(stFormatStyle, ".")
    iDecimals = len(stFormatStyle) - iPeriodPosition
    If bIsPercentage Then iDecimals = iDecimals - 1

    'Now you should have all the pieces you need so just put it together:
    Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. " & stCurrentStyle & " " & iDecimals &  " " & "Decimals f%%"

'next loop
Didn't test it so you may have to debug. But this way, no matter how many decimals you may have and whether or not it's a percentage formatted style, this will pick it up and you don't have to write an ElseIf block for each one.

Last edited by mtranchi; May 4th, 2011 at 04:27 AM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Cell Format sumith ASP.NET 2.0 Professional 0 January 5th, 2010 05:34 AM
Checking an ICQ number SKYDOS BOOK: Beginning Regular Expressions 3 November 9th, 2008 03:55 PM
Checking cell value is integer ozPATT Excel VBA 2 October 5th, 2005 06:43 AM
Checking for upper,lower case, number and symbol dumbdumb SQL Server 2000 1 March 18th, 2004 03:51 AM
Checking for a number aware Classic ASP Basics 5 June 5th, 2003 09:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.