Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
Hi,
I have a custom number format that prefixes positive numbers and negative numbers with + and - signs, and a macro line that makes a cell DP increase by one, based on a key cell DP placement. IE Key cell DP is: 0.000 and answer cell is therefo 0.0000 Because the number of DP changes for the key cell each time I use the spreadsheet, I cant just have it formatted for custom all the time for the + and - prefix, as when I run my macro to set the DP of that cell, it overrides the custom formatting for the + or -. I need both things to happen, is it possible or is there another way to simultaneously change the DP of the cell and apply the custom number format for + and - numbers each time? Cheers, Aaron. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
On 27 Aug 2006 15:23:57 -0700, "Slashman" wrote:
Hi, I have a custom number format that prefixes positive numbers and negative numbers with + and - signs, and a macro line that makes a cell DP increase by one, based on a key cell DP placement. IE Key cell DP is: 0.000 and answer cell is therefo 0.0000 Because the number of DP changes for the key cell each time I use the spreadsheet, I cant just have it formatted for custom all the time for the + and - prefix, as when I run my macro to set the DP of that cell, it overrides the custom formatting for the + or -. I need both things to happen, is it possible or is there another way to simultaneously change the DP of the cell and apply the custom number format for + and - numbers each time? Cheers, Aaron. You could maybe use a macro like this to set both: ======================================= Option Explicit Sub IncrDP() Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [a3] Set AnswerCell = [b3] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".") If InStr(1, kcFmt, ".") = 0 Then DP = 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ====================================== --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
Thanks alot for this I will try it when I am in work on Friday.
Cheers again, Aaron. Ron Rosenfeld wrote: On 27 Aug 2006 15:23:57 -0700, "Slashman" wrote: Hi, I have a custom number format that prefixes positive numbers and negative numbers with + and - signs, and a macro line that makes a cell DP increase by one, based on a key cell DP placement. IE Key cell DP is: 0.000 and answer cell is therefo 0.0000 Because the number of DP changes for the key cell each time I use the spreadsheet, I cant just have it formatted for custom all the time for the + and - prefix, as when I run my macro to set the DP of that cell, it overrides the custom formatting for the + or -. I need both things to happen, is it possible or is there another way to simultaneously change the DP of the cell and apply the custom number format for + and - numbers each time? Cheers, Aaron. You could maybe use a macro like this to set both: ======================================= Option Explicit Sub IncrDP() Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [a3] Set AnswerCell = [b3] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".") If InStr(1, kcFmt, ".") = 0 Then DP = 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ====================================== --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
Hi Ron,
I have tried to run this macro, I have copied it back for you to see. ================================================== ===== Sub IncrDP() Sheets("Working02").Select Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [d10] Set AnswerCell = [aa12] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".") If InStr(1, kcFmt, ".") = 0 Then DP = 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) <<<<<<<<<<<<<<<<<<<< acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ================================================== ======= It stalls on the acFmt line I highlighted and says unable to set worksheetfunction class. I added the sheets select line also as I need it to apply to one of many sheets in my book. Can you elaborate on this error. I cant find much on the net and the code doesent give me many places to look as a beginner in VB stuff. Cheers, Aaron. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
On 31 Aug 2006 17:52:46 -0700, "Slashman" wrote:
Hi Ron, I have tried to run this macro, I have copied it back for you to see. ================================================= ====== Sub IncrDP() Sheets("Working02").Select Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [d10] Set AnswerCell = [aa12] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".") If InStr(1, kcFmt, ".") = 0 Then DP = 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) <<<<<<<<<<<<<<<<<<<< acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ================================================= ======== It stalls on the acFmt line I highlighted and says unable to set worksheetfunction class. I added the sheets select line also as I need it to apply to one of many sheets in my book. Can you elaborate on this error. I cant find much on the net and the code doesent give me many places to look as a beginner in VB stuff. Cheers, Aaron. Two quick comments, Aaron, but I'm on my way out and won't have time to look at that error until Tuesday. 1. I wonder if you are missing a reference in VBA. 2. There is no need to "Select" a worksheet to reference it. Unless you need to select the worksheet for some other reason, it is usually more effective to reference it directly. e.g. dim ws as worksheet set ws = Set KeyCell = ws.range("d10") etc. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
Hi Ron,
Thats fine, whenever you get the chance to have a look at this for me would be great. I have tried playing with the suggestion you wrote but no luck. Cheers, Aaron . Ron Rosenfeld wrote: On 31 Aug 2006 17:52:46 -0700, "Slashman" wrote: Hi Ron, I have tried to run this macro, I have copied it back for you to see. ================================================= ====== Sub IncrDP() Sheets("Working02").Select Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [d10] Set AnswerCell = [aa12] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".") If InStr(1, kcFmt, ".") = 0 Then DP = 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) <<<<<<<<<<<<<<<<<<<< acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ================================================= ======== It stalls on the acFmt line I highlighted and says unable to set worksheetfunction class. I added the sheets select line also as I need it to apply to one of many sheets in my book. Can you elaborate on this error. I cant find much on the net and the code doesent give me many places to look as a beginner in VB stuff. Cheers, Aaron. Two quick comments, Aaron, but I'm on my way out and won't have time to look at that error until Tuesday. 1. I wonder if you are missing a reference in VBA. 2. There is no need to "Select" a worksheet to reference it. Unless you need to select the worksheet for some other reason, it is usually more effective to reference it directly. e.g. dim ws as worksheet set ws = Set KeyCell = ws.range("d10") etc. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
Hi Ron,
I have looked closer at the different items in the macro and have got the following information. With the keycell contents being 20000.2 I get the following: DP= -2 Keycell= 20000.2 AnswerCell=0.29999999999999797 kcFmt= "0.0" acFmt="" InStr (;) =0 InStr (.) =2 If I rem out the rept line, it formats the answer cell on the worksheet simply as + Hope this debug info helps. Cheers, Aaron. Ron Rosenfeld wrote: On 31 Aug 2006 17:52:46 -0700, "Slashman" wrote: Hi Ron, I have tried to run this macro, I have copied it back for you to see. ================================================= ====== Sub IncrDP() Sheets("Working02").Select Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [d10] Set AnswerCell = [aa12] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".") If InStr(1, kcFmt, ".") = 0 Then DP = 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) <<<<<<<<<<<<<<<<<<<< acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ================================================= ======== It stalls on the acFmt line I highlighted and says unable to set worksheetfunction class. I added the sheets select line also as I need it to apply to one of many sheets in my book. Can you elaborate on this error. I cant find much on the net and the code doesent give me many places to look as a beginner in VB stuff. Cheers, Aaron. Two quick comments, Aaron, but I'm on my way out and won't have time to look at that error until Tuesday. 1. I wonder if you are missing a reference in VBA. 2. There is no need to "Select" a worksheet to reference it. Unless you need to select the worksheet for some other reason, it is usually more effective to reference it directly. e.g. dim ws as worksheet set ws = Set KeyCell = ws.range("d10") etc. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
On 3 Sep 2006 17:53:52 -0700, "Slashman" wrote:
Hi Ron, I have looked closer at the different items in the macro and have got the following information. With the keycell contents being 20000.2 I get the following: DP= -2 Keycell= 20000.2 AnswerCell=0.29999999999999797 kcFmt= "0.0" acFmt="" InStr (;) =0 InStr (.) =2 If I rem out the rept line, it formats the answer cell on the worksheet simply as + Hope this debug info helps. Cheers, Aaron. The debug info helps considerably. The problem is that your KeyCell format does not follow the rules I thought it would follow. Mea culpa. I thought the KeyCell format would also have provision for + or -, but it does not. Rather it is exactly how you wrote it. So given that the KeyCell format will be in the form of "0" optionally followed by a decimal point and n0's, the following should work: ============================================= Option Explicit Sub IncrDP() Sheets("Working02").Select Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [a1] Set AnswerCell = [a2] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) 'special case of kcFmt is "0" If kcFmt = "0" Then acFmt = "0.0" acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ========================================== --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
Hi Ron,
Yes that works brilliantly, thanks so much for that. chiselling away at the little tuning probs on my ssheet at the moment is a bit of a task. Thanks so much again, Cheers, Aaron. Ron Rosenfeld wrote: On 3 Sep 2006 17:53:52 -0700, "Slashman" wrote: Hi Ron, I have looked closer at the different items in the macro and have got the following information. With the keycell contents being 20000.2 I get the following: DP= -2 Keycell= 20000.2 AnswerCell=0.29999999999999797 kcFmt= "0.0" acFmt="" InStr (;) =0 InStr (.) =2 If I rem out the rept line, it formats the answer cell on the worksheet simply as + Hope this debug info helps. Cheers, Aaron. The debug info helps considerably. The problem is that your KeyCell format does not follow the rules I thought it would follow. Mea culpa. I thought the KeyCell format would also have provision for + or -, but it does not. Rather it is exactly how you wrote it. So given that the KeyCell format will be in the form of "0" optionally followed by a decimal point and n0's, the following should work: ============================================= Option Explicit Sub IncrDP() Sheets("Working02").Select Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [a1] Set AnswerCell = [a2] 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 1 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) 'special case of kcFmt is "0" If kcFmt = "0" Then acFmt = "0.0" acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt End Sub ========================================== --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual Formatting in Macro
On 4 Sep 2006 15:14:15 -0700, "Slashman" wrote:
Hi Ron, Yes that works brilliantly, thanks so much for that. chiselling away at the little tuning probs on my ssheet at the moment is a bit of a task. Thanks so much again, Cheers, Aaron. You're welcome. Glad to help. Without your debugging output, it would have taken much longer. Best wishes --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
link to combobox | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
How to stop Excel remembering/loading macro from previously opened Workbook | Excel Discussion (Misc queries) | |||
Macro not formatting correctly | Excel Discussion (Misc queries) |