Setting number format by multiplication - possible bug?
I have problems getting the numberformat right, when offsetting a user input
from a user form to a table. The values are formatted as text, which means that I can't perform math manipulations with the numbers. On the application side, the xl.helpfile recommends multiplying the "troubled area" by 1. this works. The relevant area takes the format from the 1, which is formatted as a number. Trying to record this procedure (rather unelegant I know) and integrate it into VBA doesn't work. Can anybody guide me? Recommendations on how to format the output area or a hint on how I can make the fix described above work are most welcome. Private Sub GemClick_Click() Dim i As Integer Dim dato As Date Dim rg As Range, rgBlank As Range Application.ScreenUpdating = False Worksheets("Timeseddel").Activate Range(Cells(15, 3), Cells(31, 7)).ClearContents Cells(7, 7).ClearContents 'ActiveWorkbook.Save Cells(15, 3).Activate With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = TextTimeDbt1.Text .Offset(0, 4).Value = TextTimeNonDbt1.Text .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = TextTimeDbt2.Text .Offset(1, 4).Value = TextTimeNonDbt2.Text .. .. .. Range("J1").Select Selection.Copy Range("F15:G31").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=True Application.CutCopyMode = False End With End sub |
Setting number format by multiplication - possible bug?
why not just convert them before you write them:
With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = Cdate(TextTimeDbt1.Text) .Offset(0, 4).Value = Cdate(TextTimeNonDbt1.Text) .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = cDate(TextTimeDbt2.Text) .Offset(1, 4).Value = cDate(TextTimeNonDbt2.Text) .offset(0,3).Resize(2,2).Numberformat = "hh:mm" or use cDbl if these are not time values. change the Numberformat to whatever is appropriate. -- Regards, Tom Ogilvy "Kragelund" wrote: I have problems getting the numberformat right, when offsetting a user input from a user form to a table. The values are formatted as text, which means that I can't perform math manipulations with the numbers. On the application side, the xl.helpfile recommends multiplying the "troubled area" by 1. this works. The relevant area takes the format from the 1, which is formatted as a number. Trying to record this procedure (rather unelegant I know) and integrate it into VBA doesn't work. Can anybody guide me? Recommendations on how to format the output area or a hint on how I can make the fix described above work are most welcome. Private Sub GemClick_Click() Dim i As Integer Dim dato As Date Dim rg As Range, rgBlank As Range Application.ScreenUpdating = False Worksheets("Timeseddel").Activate Range(Cells(15, 3), Cells(31, 7)).ClearContents Cells(7, 7).ClearContents 'ActiveWorkbook.Save Cells(15, 3).Activate With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = TextTimeDbt1.Text .Offset(0, 4).Value = TextTimeNonDbt1.Text .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = TextTimeDbt2.Text .Offset(1, 4).Value = TextTimeNonDbt2.Text . . . Range("J1").Select Selection.Copy Range("F15:G31").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=True Application.CutCopyMode = False End With End sub |
Setting number format by multiplication - possible bug?
Tom, thanks.
Strangely, excel now accepts the first of the following code lines, but not the next, they are supposed to be almost completely identical, the first line records billable hours, the second non-billable hours. They both worked in the same (erroneous) manner just before. Any ideas? Thanks btw. for the proposed solution, I learned something there. .Offset(0, 3).Value = CDbl(TextTimeDbt1.Text) -- works .Offset(0, 4).Value = CDbl(TextTimeNonDbt1.Text) -- crashes "Tom Ogilvy" wrote: why not just convert them before you write them: With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = Cdate(TextTimeDbt1.Text) .Offset(0, 4).Value = Cdate(TextTimeNonDbt1.Text) .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = cDate(TextTimeDbt2.Text) .Offset(1, 4).Value = cDate(TextTimeNonDbt2.Text) .offset(0,3).Resize(2,2).Numberformat = "hh:mm" or use cDbl if these are not time values. change the Numberformat to whatever is appropriate. -- Regards, Tom Ogilvy "Kragelund" wrote: I have problems getting the numberformat right, when offsetting a user input from a user form to a table. The values are formatted as text, which means that I can't perform math manipulations with the numbers. On the application side, the xl.helpfile recommends multiplying the "troubled area" by 1. this works. The relevant area takes the format from the 1, which is formatted as a number. Trying to record this procedure (rather unelegant I know) and integrate it into VBA doesn't work. Can anybody guide me? Recommendations on how to format the output area or a hint on how I can make the fix described above work are most welcome. Private Sub GemClick_Click() Dim i As Integer Dim dato As Date Dim rg As Range, rgBlank As Range Application.ScreenUpdating = False Worksheets("Timeseddel").Activate Range(Cells(15, 3), Cells(31, 7)).ClearContents Cells(7, 7).ClearContents 'ActiveWorkbook.Save Cells(15, 3).Activate With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = TextTimeDbt1.Text .Offset(0, 4).Value = TextTimeNonDbt1.Text .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = TextTimeDbt2.Text .Offset(1, 4).Value = TextTimeNonDbt2.Text . . . Range("J1").Select Selection.Copy Range("F15:G31").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=True Application.CutCopyMode = False End With End sub |
Setting number format by multiplication - possible bug?
what is a value where it works?
what is a value where it fails? (in other words, what is the value in the textbox in each case). I suspect the value that fails isn't being recognized as a number. -- Regards, Tom Ogilvy "Kragelund" wrote: Tom, thanks. Strangely, excel now accepts the first of the following code lines, but not the next, they are supposed to be almost completely identical, the first line records billable hours, the second non-billable hours. They both worked in the same (erroneous) manner just before. Any ideas? Thanks btw. for the proposed solution, I learned something there. .Offset(0, 3).Value = CDbl(TextTimeDbt1.Text) -- works .Offset(0, 4).Value = CDbl(TextTimeNonDbt1.Text) -- crashes "Tom Ogilvy" wrote: why not just convert them before you write them: With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = Cdate(TextTimeDbt1.Text) .Offset(0, 4).Value = Cdate(TextTimeNonDbt1.Text) .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = cDate(TextTimeDbt2.Text) .Offset(1, 4).Value = cDate(TextTimeNonDbt2.Text) .offset(0,3).Resize(2,2).Numberformat = "hh:mm" or use cDbl if these are not time values. change the Numberformat to whatever is appropriate. -- Regards, Tom Ogilvy "Kragelund" wrote: I have problems getting the numberformat right, when offsetting a user input from a user form to a table. The values are formatted as text, which means that I can't perform math manipulations with the numbers. On the application side, the xl.helpfile recommends multiplying the "troubled area" by 1. this works. The relevant area takes the format from the 1, which is formatted as a number. Trying to record this procedure (rather unelegant I know) and integrate it into VBA doesn't work. Can anybody guide me? Recommendations on how to format the output area or a hint on how I can make the fix described above work are most welcome. Private Sub GemClick_Click() Dim i As Integer Dim dato As Date Dim rg As Range, rgBlank As Range Application.ScreenUpdating = False Worksheets("Timeseddel").Activate Range(Cells(15, 3), Cells(31, 7)).ClearContents Cells(7, 7).ClearContents 'ActiveWorkbook.Save Cells(15, 3).Activate With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = TextTimeDbt1.Text .Offset(0, 4).Value = TextTimeNonDbt1.Text .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = TextTimeDbt2.Text .Offset(1, 4).Value = TextTimeNonDbt2.Text . . . Range("J1").Select Selection.Copy Range("F15:G31").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=True Application.CutCopyMode = False End With End sub |
Setting number format by multiplication - possible bug?
Ok, now I'm really confused. Apparently the actual values don't matter (its
typically 4,5 / 3,5 / 1,25 etc.) for whether the line crashes or not. I changed the .text for .value in the code, which made it work - sort of. Now it crashes in the following line: .Offset(3, 3).Value = CDbl(TextTimeDbt4.Value) Apparently, because the respective input fields are empty. Why should that matter? "Tom Ogilvy" wrote: what is a value where it works? what is a value where it fails? (in other words, what is the value in the textbox in each case). I suspect the value that fails isn't being recognized as a number. -- Regards, Tom Ogilvy "Kragelund" wrote: Tom, thanks. Strangely, excel now accepts the first of the following code lines, but not the next, they are supposed to be almost completely identical, the first line records billable hours, the second non-billable hours. They both worked in the same (erroneous) manner just before. Any ideas? Thanks btw. for the proposed solution, I learned something there. .Offset(0, 3).Value = CDbl(TextTimeDbt1.Text) -- works .Offset(0, 4).Value = CDbl(TextTimeNonDbt1.Text) -- crashes "Tom Ogilvy" wrote: why not just convert them before you write them: With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = Cdate(TextTimeDbt1.Text) .Offset(0, 4).Value = Cdate(TextTimeNonDbt1.Text) .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = cDate(TextTimeDbt2.Text) .Offset(1, 4).Value = cDate(TextTimeNonDbt2.Text) .offset(0,3).Resize(2,2).Numberformat = "hh:mm" or use cDbl if these are not time values. change the Numberformat to whatever is appropriate. -- Regards, Tom Ogilvy "Kragelund" wrote: I have problems getting the numberformat right, when offsetting a user input from a user form to a table. The values are formatted as text, which means that I can't perform math manipulations with the numbers. On the application side, the xl.helpfile recommends multiplying the "troubled area" by 1. this works. The relevant area takes the format from the 1, which is formatted as a number. Trying to record this procedure (rather unelegant I know) and integrate it into VBA doesn't work. Can anybody guide me? Recommendations on how to format the output area or a hint on how I can make the fix described above work are most welcome. Private Sub GemClick_Click() Dim i As Integer Dim dato As Date Dim rg As Range, rgBlank As Range Application.ScreenUpdating = False Worksheets("Timeseddel").Activate Range(Cells(15, 3), Cells(31, 7)).ClearContents Cells(7, 7).ClearContents 'ActiveWorkbook.Save Cells(15, 3).Activate With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = TextTimeDbt1.Text .Offset(0, 4).Value = TextTimeNonDbt1.Text .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = TextTimeDbt2.Text .Offset(1, 4).Value = TextTimeNonDbt2.Text . . . Range("J1").Select Selection.Copy Range("F15:G31").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=True Application.CutCopyMode = False End With End sub |
Setting number format by multiplication - possible bug?
cdbl doesn't see "" as a number stored as a string - so you get a type
mismatch error. Try = val(TextTimeDbt4.Value) However I don't think Val will see the comma as a decimal separator (it will truncate your number). If it truncates the number then use = Val(replace(TextTimeDbt4.Value,",",".")) -- Regards, Tom Ogilvy "Kragelund" wrote: Ok, now I'm really confused. Apparently the actual values don't matter (its typically 4,5 / 3,5 / 1,25 etc.) for whether the line crashes or not. I changed the .text for .value in the code, which made it work - sort of. Now it crashes in the following line: .Offset(3, 3).Value = CDbl(TextTimeDbt4.Value) Apparently, because the respective input fields are empty. Why should that matter? "Tom Ogilvy" wrote: what is a value where it works? what is a value where it fails? (in other words, what is the value in the textbox in each case). I suspect the value that fails isn't being recognized as a number. -- Regards, Tom Ogilvy "Kragelund" wrote: Tom, thanks. Strangely, excel now accepts the first of the following code lines, but not the next, they are supposed to be almost completely identical, the first line records billable hours, the second non-billable hours. They both worked in the same (erroneous) manner just before. Any ideas? Thanks btw. for the proposed solution, I learned something there. .Offset(0, 3).Value = CDbl(TextTimeDbt1.Text) -- works .Offset(0, 4).Value = CDbl(TextTimeNonDbt1.Text) -- crashes "Tom Ogilvy" wrote: why not just convert them before you write them: With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = Cdate(TextTimeDbt1.Text) .Offset(0, 4).Value = Cdate(TextTimeNonDbt1.Text) .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = cDate(TextTimeDbt2.Text) .Offset(1, 4).Value = cDate(TextTimeNonDbt2.Text) .offset(0,3).Resize(2,2).Numberformat = "hh:mm" or use cDbl if these are not time values. change the Numberformat to whatever is appropriate. -- Regards, Tom Ogilvy "Kragelund" wrote: I have problems getting the numberformat right, when offsetting a user input from a user form to a table. The values are formatted as text, which means that I can't perform math manipulations with the numbers. On the application side, the xl.helpfile recommends multiplying the "troubled area" by 1. this works. The relevant area takes the format from the 1, which is formatted as a number. Trying to record this procedure (rather unelegant I know) and integrate it into VBA doesn't work. Can anybody guide me? Recommendations on how to format the output area or a hint on how I can make the fix described above work are most welcome. Private Sub GemClick_Click() Dim i As Integer Dim dato As Date Dim rg As Range, rgBlank As Range Application.ScreenUpdating = False Worksheets("Timeseddel").Activate Range(Cells(15, 3), Cells(31, 7)).ClearContents Cells(7, 7).ClearContents 'ActiveWorkbook.Save Cells(15, 3).Activate With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = TextTimeDbt1.Text .Offset(0, 4).Value = TextTimeNonDbt1.Text .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = TextTimeDbt2.Text .Offset(1, 4).Value = TextTimeNonDbt2.Text . . . Range("J1").Select Selection.Copy Range("F15:G31").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=True Application.CutCopyMode = False End With End sub |
Setting number format by multiplication - possible bug?
This seems to work. Many thanks for your assistance!
"Tom Ogilvy" wrote: cdbl doesn't see "" as a number stored as a string - so you get a type mismatch error. Try = val(TextTimeDbt4.Value) However I don't think Val will see the comma as a decimal separator (it will truncate your number). If it truncates the number then use = Val(replace(TextTimeDbt4.Value,",",".")) -- Regards, Tom Ogilvy "Kragelund" wrote: Ok, now I'm really confused. Apparently the actual values don't matter (its typically 4,5 / 3,5 / 1,25 etc.) for whether the line crashes or not. I changed the .text for .value in the code, which made it work - sort of. Now it crashes in the following line: .Offset(3, 3).Value = CDbl(TextTimeDbt4.Value) Apparently, because the respective input fields are empty. Why should that matter? "Tom Ogilvy" wrote: what is a value where it works? what is a value where it fails? (in other words, what is the value in the textbox in each case). I suspect the value that fails isn't being recognized as a number. -- Regards, Tom Ogilvy "Kragelund" wrote: Tom, thanks. Strangely, excel now accepts the first of the following code lines, but not the next, they are supposed to be almost completely identical, the first line records billable hours, the second non-billable hours. They both worked in the same (erroneous) manner just before. Any ideas? Thanks btw. for the proposed solution, I learned something there. .Offset(0, 3).Value = CDbl(TextTimeDbt1.Text) -- works .Offset(0, 4).Value = CDbl(TextTimeNonDbt1.Text) -- crashes "Tom Ogilvy" wrote: why not just convert them before you write them: With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = Cdate(TextTimeDbt1.Text) .Offset(0, 4).Value = Cdate(TextTimeNonDbt1.Text) .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = cDate(TextTimeDbt2.Text) .Offset(1, 4).Value = cDate(TextTimeNonDbt2.Text) .offset(0,3).Resize(2,2).Numberformat = "hh:mm" or use cDbl if these are not time values. change the Numberformat to whatever is appropriate. -- Regards, Tom Ogilvy "Kragelund" wrote: I have problems getting the numberformat right, when offsetting a user input from a user form to a table. The values are formatted as text, which means that I can't perform math manipulations with the numbers. On the application side, the xl.helpfile recommends multiplying the "troubled area" by 1. this works. The relevant area takes the format from the 1, which is formatted as a number. Trying to record this procedure (rather unelegant I know) and integrate it into VBA doesn't work. Can anybody guide me? Recommendations on how to format the output area or a hint on how I can make the fix described above work are most welcome. Private Sub GemClick_Click() Dim i As Integer Dim dato As Date Dim rg As Range, rgBlank As Range Application.ScreenUpdating = False Worksheets("Timeseddel").Activate Range(Cells(15, 3), Cells(31, 7)).ClearContents Cells(7, 7).ClearContents 'ActiveWorkbook.Save Cells(15, 3).Activate With ActiveCell .Offset(0, 0).Value = LstNavn1.Text .Offset(0, 1).Value = TxtWkType1.Text .Offset(0, 2).Value = TextRmk1.Text .Offset(0, 3).Value = TextTimeDbt1.Text .Offset(0, 4).Value = TextTimeNonDbt1.Text .Offset(1, 0).Value = LstNavn2.Text .Offset(1, 1).Value = TxtWkType2.Text .Offset(1, 2).Value = TextRmk2.Text .Offset(1, 3).Value = TextTimeDbt2.Text .Offset(1, 4).Value = TextTimeNonDbt2.Text . . . Range("J1").Select Selection.Copy Range("F15:G31").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=True Application.CutCopyMode = False End With End sub |
All times are GMT +1. The time now is 02:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com