Userform for addition calculation

[ipbr21054]

Evening.
Can you help please with a basic code where once two values are entered into two TextBoxes on a userform the command button will run the code adding these values to the existing cell values.

Some info for you.
Userform will have Textbox1 & Textbox2
Also the Commandbutton1
Range on worksheet will be A10:B25
The code should check for last cell in column A & B then paste new value over existing value.

The code should work like this.
I open the Userform.
I enter a value in Textbox1 & also Textbox2
When I press Commandbutton1 the code should look in column A & B for the last cell with a value in it.
My entered value in Textbox1 should be added to the cell in column A & my entered value in Textbox2 should be added to the cell in column B

Have a good night.
Thanks.

 

[SQUIDD]

Hi

this looks for the last raw of date in column A and B. places the textbox entries into the next blank row.

it was not very clear to me eactly how you wanted this to work.

Private Sub CommandButton1_Click()
lra = Range("A" & Rows.Count).End(xlUp).Row
lrb = Range("B" & Rows.Count).End(xlUp).Row
LASTROW = Application.WorksheetFunction.Max(lra, lrb) + 1
Range("A" & LASTROW) = Me.TextBox1
Range("B" & LASTROW) = Me.TextBox2
End Sub

 

[ipbr21054]

Hi,
Thanks for that.
I need the code to only look for last row in the range A10:A25 & B10:B25
Reason being i have other values below which musnt be altered.

I tried this but just entered a value all the way down the worksheet.

Private Sub CommandButton1_Click()
lra = Range("A" & Rows.Count).End(xlUp).Row
lrb = Range("B" & Rows.Count).End(xlUp).Row
LastRow = Application.WorksheetFunction.Max(lra, lrb) - 1
Range("A10:A25" & LastRow) = Me.TextBox1
Range("B10:B25" & LastRow) = Me.TextBox2
End Sub

 

[SQUIDD]

ok, i see.

try this, however, im still not sure if you want the data to go into the same row once we discover the last data entry, or you require them to be seperate rows for each.

Private Sub CommandButton1_Click()
For A = 10 To 25
If Range("A" & A) <> "" Then lra = A
If Range("B" & A) <> "" Then lrb = A
Next A
LASTROW = Application.WorksheetFunction.Max(lra, lrb)
Range("A" & LASTROW) = Me.TextBox1
Range("B" & LASTROW) = Me.TextBox2
End Sub

 

[SQUIDD]

sorry, this would be more efficient

Private Sub CommandButton1_Click()
For A = 25 To 10 Step -1
If Range("A" & A) <> "" Then lra = A
If Range("B" & A) <> "" Then lrb = A
Next A
LASTROW = Application.WorksheetFunction.Max(lra, lrb)
Range("A" & LASTROW) = Me.TextBox1
Range("B" & LASTROW) = Me.TextBox2
End Sub

 

[ipbr21054]

Thanks thats got me moving.
Have a nice day

 

[SQUIDD]

hi

glad to help

just thinking on this further however,

this is better still,

the other code, if you had blank cells may have actulally written to the incorrect row.

Private Sub CommandButton1_Click()
For a = 25 To 10 Step -1
If Range("A" & a) <> "" Or Range("B" & a) <> "" Then
    Exit For
End If
Next a
Range("A" & a) = Me.TextBox1
Range("B" & a) = Me.TextBox2
End Sub

 

[ipbr21054]

Just so I understand.
Capital “A & B” refer to column.
Where small case “a” refers to range.

Also the “-1” refers to adding the textbox value to the last cell which currently has a value in it.

Is that correct.

 

[SQUIDD]

Just so I understand.
Capital “A & B” refer to column.
Where small case “a” refers to range.

Also the “-1” refers to adding the textbox value to the last cell which currently has a value in it.

Is that correct.

Hi

Yes A and B refers to the column.

the lowercase "a" is just assigned to the variable number for 10 to 25. in a FOR loop.

The code simple starts at row 25, checks if column A or column B has data. If either do have data, the loop ends and the value "a" is 25(row 25), if there was no date, then 25 becomes 24 since we are looping from 25 to 10 stepping by -1. if i had used step -5 the next row it would have looked at would be 20 then 15,10,5 etc.

hope that makes sense

dave

 

[ipbr21054]

I notice in your revised advice the for & next use “a” where the post before it used “A”