Userform Add

[Guest]

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

 

[Guest]

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.

 

[Dave Peterson]

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

 

[Guest]

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.

 

[Dave Peterson]

Maybe, but I think you'd have to share the rules for how quarters are determined
and what cells get filled...

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.

 

[Guest]

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?

 

[Dave Peterson]

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

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?

 

[Guest]

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?

 

[Dave Peterson]

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

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?