Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Hi All
Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Just a note first, your sheet range formulas can be simplified
IF TRIM(Sheets("Income Expense 2007").RANGE("A" & rownum)) ="" THEN will work just as your current formula does And yes, you can do what you want - Sheets("Income Expense 2007").Range("B7") = Tb1.Value + _ Sheets("Income Expense 2007").Range("B7") Except during testing of things for equality, the = symbol in VB (and all dialects of Basic that I am familiar with) actually does not mean "equal to", it means "set the value of the left side of the equation to the results of the operations on the right side of the equation. A fine distinction, but allows you to do things like: X = 5 X = X + 6 at which point X will have a value of 11. "Hazel" wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Maybe....
Replace this line: Sheets("Income Expense 2007").Range("B7") = Tb1.Value with this set of lines: with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value end if end with An empty cell is treated as a numeric cell (value = 0) by VBA) If that cell is not numeric, what should happen? with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value else .value = tbl.value 'or '.value = "ERROR" end if end with Hazel wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Gentlemen
It is a beautiful day here in the UK -- and you have both made it more gorgeous with your solving of my question - my boss has just thrown me another question as we work to quarter periods throughout the year e.g. 01/11/06 to 31/01/07 on the 01/02/07 is it possible for the Tb1 value on the 01/02/07 to automatically enter cell D7 instead of B7 on that date. Once again thank you everything much appreciated. -- Many thanks hazel "Dave Peterson" wrote: Maybe.... Replace this line: Sheets("Income Expense 2007").Range("B7") = Tb1.Value with this set of lines: with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value end if end with An empty cell is treated as a numeric cell (value = 0) by VBA) If that cell is not numeric, what should happen? with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value else .value = tbl.value 'or '.value = "ERROR" end if end with Hazel wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Maybe, but I think you'd have to share the rules for how quarters are determined
and what cells get filled... Hazel wrote: Gentlemen It is a beautiful day here in the UK -- and you have both made it more gorgeous with your solving of my question - my boss has just thrown me another question as we work to quarter periods throughout the year e.g. 01/11/06 to 31/01/07 on the 01/02/07 is it possible for the Tb1 value on the 01/02/07 to automatically enter cell D7 instead of B7 on that date. Once again thank you everything much appreciated. -- Many thanks hazel "Dave Peterson" wrote: Maybe.... Replace this line: Sheets("Income Expense 2007").Range("B7") = Tb1.Value with this set of lines: with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value end if end with An empty cell is treated as a numeric cell (value = 0) by VBA) If that cell is not numeric, what should happen? with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value else .value = tbl.value 'or '.value = "ERROR" end if end with Hazel wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Hi Mr Peterson,
The quarter periods and designated cells are as follows 01/11/06 to 31/01/07 - Cell "B7" 01/02/06 to 30/04/07 - Cell "D7" 01/05/07 to 31/07/07 - Cell "F7" 01/08/07 to 31/10/07 - Cell "H7" Does this help? -- Many thanks hazel "Dave Peterson" wrote: Maybe, but I think you'd have to share the rules for how quarters are determined and what cells get filled... Hazel wrote: Gentlemen It is a beautiful day here in the UK -- and you have both made it more gorgeous with your solving of my question - my boss has just thrown me another question as we work to quarter periods throughout the year e.g. 01/11/06 to 31/01/07 on the 01/02/07 is it possible for the Tb1 value on the 01/02/07 to automatically enter cell D7 instead of B7 on that date. Once again thank you everything much appreciated. -- Many thanks hazel "Dave Peterson" wrote: Maybe.... Replace this line: Sheets("Income Expense 2007").Range("B7") = Tb1.Value with this set of lines: with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value end if end with An empty cell is treated as a numeric cell (value = 0) by VBA) If that cell is not numeric, what should happen? with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value else .value = tbl.value 'or '.value = "ERROR" end if end with Hazel wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Well for those 4 quarters, maybe you could just check the date...
Kind of like this: Option Explicit Sub testme() Dim DestCell As Range Dim myDate As Date myDate = DateSerial(2007, 2, 3) Set DestCell = Nothing With Worksheets("sheet1") Select Case myDate Case Is <= DateSerial(2007, 1, 31): Set DestCell = .Range("b7") Case Is <= DateSerial(2007, 4, 30): Set DestCell = .Range("d7") Case Is <= DateSerial(2007, 7, 31): Set DestCell = .Range("f7") Case Is <= DateSerial(2007, 10, 31): Set DestCell = .Range("h7") End Select End With If DestCell Is Nothing Then 'not a valid date Else DestCell.Value = "whatever you want" End If End Sub Hazel wrote: Hi Mr Peterson, The quarter periods and designated cells are as follows 01/11/06 to 31/01/07 - Cell "B7" 01/02/06 to 30/04/07 - Cell "D7" 01/05/07 to 31/07/07 - Cell "F7" 01/08/07 to 31/10/07 - Cell "H7" Does this help? -- Many thanks hazel "Dave Peterson" wrote: Maybe, but I think you'd have to share the rules for how quarters are determined and what cells get filled... Hazel wrote: Gentlemen It is a beautiful day here in the UK -- and you have both made it more gorgeous with your solving of my question - my boss has just thrown me another question as we work to quarter periods throughout the year e.g. 01/11/06 to 31/01/07 on the 01/02/07 is it possible for the Tb1 value on the 01/02/07 to automatically enter cell D7 instead of B7 on that date. Once again thank you everything much appreciated. -- Many thanks hazel "Dave Peterson" wrote: Maybe.... Replace this line: Sheets("Income Expense 2007").Range("B7") = Tb1.Value with this set of lines: with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value end if end with An empty cell is treated as a numeric cell (value = 0) by VBA) If that cell is not numeric, what should happen? with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value else .value = tbl.value 'or '.value = "ERROR" end if end with Hazel wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
Hi Mr Peterson
Thanks for the info will probaly have to leave this now until Monday -- other jobs landing on my desk that have to be finished before 16:30Hrs. Will Reply Monday -- should I put this on a new module or the userform code sheet? -- Many thanks hazel "Dave Peterson" wrote: Well for those 4 quarters, maybe you could just check the date... Kind of like this: Option Explicit Sub testme() Dim DestCell As Range Dim myDate As Date myDate = DateSerial(2007, 2, 3) Set DestCell = Nothing With Worksheets("sheet1") Select Case myDate Case Is <= DateSerial(2007, 1, 31): Set DestCell = .Range("b7") Case Is <= DateSerial(2007, 4, 30): Set DestCell = .Range("d7") Case Is <= DateSerial(2007, 7, 31): Set DestCell = .Range("f7") Case Is <= DateSerial(2007, 10, 31): Set DestCell = .Range("h7") End Select End With If DestCell Is Nothing Then 'not a valid date Else DestCell.Value = "whatever you want" End If End Sub Hazel wrote: Hi Mr Peterson, The quarter periods and designated cells are as follows 01/11/06 to 31/01/07 - Cell "B7" 01/02/06 to 30/04/07 - Cell "D7" 01/05/07 to 31/07/07 - Cell "F7" 01/08/07 to 31/10/07 - Cell "H7" Does this help? -- Many thanks hazel "Dave Peterson" wrote: Maybe, but I think you'd have to share the rules for how quarters are determined and what cells get filled... Hazel wrote: Gentlemen It is a beautiful day here in the UK -- and you have both made it more gorgeous with your solving of my question - my boss has just thrown me another question as we work to quarter periods throughout the year e.g. 01/11/06 to 31/01/07 on the 01/02/07 is it possible for the Tb1 value on the 01/02/07 to automatically enter cell D7 instead of B7 on that date. Once again thank you everything much appreciated. -- Many thanks hazel "Dave Peterson" wrote: Maybe.... Replace this line: Sheets("Income Expense 2007").Range("B7") = Tb1.Value with this set of lines: with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value end if end with An empty cell is treated as a numeric cell (value = 0) by VBA) If that cell is not numeric, what should happen? with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value else .value = tbl.value 'or '.value = "ERROR" end if end with Hazel wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform Add
You'll have to take the portion of the code that you want and integrate it into
your existing code. I don't think it's useable for you the way it sits Hazel wrote: Hi Mr Peterson Thanks for the info will probaly have to leave this now until Monday -- other jobs landing on my desk that have to be finished before 16:30Hrs. Will Reply Monday -- should I put this on a new module or the userform code sheet? -- Many thanks hazel "Dave Peterson" wrote: Well for those 4 quarters, maybe you could just check the date... Kind of like this: Option Explicit Sub testme() Dim DestCell As Range Dim myDate As Date myDate = DateSerial(2007, 2, 3) Set DestCell = Nothing With Worksheets("sheet1") Select Case myDate Case Is <= DateSerial(2007, 1, 31): Set DestCell = .Range("b7") Case Is <= DateSerial(2007, 4, 30): Set DestCell = .Range("d7") Case Is <= DateSerial(2007, 7, 31): Set DestCell = .Range("f7") Case Is <= DateSerial(2007, 10, 31): Set DestCell = .Range("h7") End Select End With If DestCell Is Nothing Then 'not a valid date Else DestCell.Value = "whatever you want" End If End Sub Hazel wrote: Hi Mr Peterson, The quarter periods and designated cells are as follows 01/11/06 to 31/01/07 - Cell "B7" 01/02/06 to 30/04/07 - Cell "D7" 01/05/07 to 31/07/07 - Cell "F7" 01/08/07 to 31/10/07 - Cell "H7" Does this help? -- Many thanks hazel "Dave Peterson" wrote: Maybe, but I think you'd have to share the rules for how quarters are determined and what cells get filled... Hazel wrote: Gentlemen It is a beautiful day here in the UK -- and you have both made it more gorgeous with your solving of my question - my boss has just thrown me another question as we work to quarter periods throughout the year e.g. 01/11/06 to 31/01/07 on the 01/02/07 is it possible for the Tb1 value on the 01/02/07 to automatically enter cell D7 instead of B7 on that date. Once again thank you everything much appreciated. -- Many thanks hazel "Dave Peterson" wrote: Maybe.... Replace this line: Sheets("Income Expense 2007").Range("B7") = Tb1.Value with this set of lines: with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value end if end with An empty cell is treated as a numeric cell (value = 0) by VBA) If that cell is not numeric, what should happen? with Sheets("Income Expense 2007").range("B7") if isnumeric(.value) then .value = .value + Tb1.Value else .value = tbl.value 'or '.value = "ERROR" end if end with Hazel wrote: Hi All Using the following code below my userform adds to the designated sheet if there is a value already in the cell is it possible for the new value added in Tb1 to add to the value already in the cell and show the new value in the same cell. e.g the cell B7 has a value of 100 and I add 200 in Tb1 then click on Add1 cell B7 would then show 300. Private Sub Add1_Click() startrownum = 7 endrownum = 25 For rownum = startrownum To endrownum If Trim(Sheets("Income Expense 2007").Range("A" & Trim(Str(rownum)))) = "" Then freerownum = rownum rownum = endrownum End If Next rownum Sheets("Income Expense 2007").Range("A" & Trim(Str(freerownum))) = Com1.Value Sheets("Income Expense 2007").Range("B7") = Tb1.Value End Sub -- Many thanks hazel -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
coping and create new userform | Excel Discussion (Misc queries) | |||
Userform and variables | Excel Discussion (Misc queries) | |||
Data Validation Cell - Move to UserForm | Excel Worksheet Functions | |||
Cell Content from UserForm Not Retained | Excel Discussion (Misc queries) | |||
How can I run a macro in the background whilst a UserForm is visib | Excel Discussion (Misc queries) |