| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding row from userfrom and copying formulas
I have the following code that will add a record to the next blank row.
Private Sub cmdAdd_Click() ' Save form contents before changing rows: SaveRow ' Set current row to first empty row, i.e. one row after ' the last row currently in use: If Cells(1, 1).Value = "" Then lCurrentRow = 1 ' (list is empty - start in row 1) Else lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1 End If ' Clear the form for user to add new name: LoadRow ' Set focus to Name textbox: txtReqNum.SetFocus End Sub What I would like to do is instead of add the userform data to the blank row is to add a new row and add the userform data to that new row. I have existing data in columns A:O (this is also where the new data from the userform will be added, but in the newly added row) and I have hidden formulas that counts up the various values of A:O in columns P:AG. I need the formulas from the previous row that currently has existing hidden formulas (P:AG) copied to the new row that we just added in the same range (P:AG). I also have the sums of colums P:AG added up in the row below the new added row in the same range P:AG in each of those columns and need the sum formula updated to reflect the counts of the newly added row data. I know this may be very complicated (it is for me at least) so any help would be greatly appreciated. Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding row from userfrom and copying formulas
Hi Criss,
I cooked the procedure below. But before you use this make usure that there is a blank line between the figures and the SUM formulas in P-AG. The blank line MUSR be included in the formulas. So if the last row with figures is line 23, line 24 will be blank. the formula for P25 looks like =SUM(P1:P24) Now you cab use this procedure Private Sub cmdAddRow_Click() Dim myNewRow As Long Dim myFormulas As Long myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown Cells(myNewRow, 1).Value = Form1.TextBox1.Text Cells(myNewRow, 2).Value = Form1.TextBox2.Text Cells(myNewRow, 3).Value = Form1.TextBox3.Text Cells(myNewRow, 4).Value = Form1.TextBox4.Text Cells(myNewRow, 5).Value = Form1.TextBox5.Text Cells(myNewRow, 6).Value = Form1.TextBox6.Text Cells(myNewRow, 7).Value = Form1.TextBox7.Text Cells(myNewRow, 8).Value = Form1.TextBox8.Text Cells(myNewRow, 9).Value = Form1.TextBox9.Text Cells(myNewRow, 10).Value = Form1.TextBox10.Text Cells(myNewRow, 11).Value = Form1.TextBox12.Text Cells(myNewRow, 12).Value = Form1.TextBox12.Text Cells(myNewRow, 13).Value = Form1.TextBox13.Text Cells(myNewRow, 14).Value = Form1.TextBox14.Text Cells(myNewRow, 15).Value = Form1.TextBox15.Text For myFormulas = 16 To 33 Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1, myFormulas).Formula Next End Sub HTH, RadarEye |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding row from userfrom and copying formulas
Thanks!!! - works like a charm!
RadarEye wrote: Hi Criss, I cooked the procedure below. But before you use this make usure that there is a blank line between the figures and the SUM formulas in P-AG. The blank line MUSR be included in the formulas. So if the last row with figures is line 23, line 24 will be blank. the formula for P25 looks like =SUM(P1:P24) Now you cab use this procedure Private Sub cmdAddRow_Click() Dim myNewRow As Long Dim myFormulas As Long myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown Cells(myNewRow, 1).Value = Form1.TextBox1.Text Cells(myNewRow, 2).Value = Form1.TextBox2.Text Cells(myNewRow, 3).Value = Form1.TextBox3.Text Cells(myNewRow, 4).Value = Form1.TextBox4.Text Cells(myNewRow, 5).Value = Form1.TextBox5.Text Cells(myNewRow, 6).Value = Form1.TextBox6.Text Cells(myNewRow, 7).Value = Form1.TextBox7.Text Cells(myNewRow, 8).Value = Form1.TextBox8.Text Cells(myNewRow, 9).Value = Form1.TextBox9.Text Cells(myNewRow, 10).Value = Form1.TextBox10.Text Cells(myNewRow, 11).Value = Form1.TextBox12.Text Cells(myNewRow, 12).Value = Form1.TextBox12.Text Cells(myNewRow, 13).Value = Form1.TextBox13.Text Cells(myNewRow, 14).Value = Form1.TextBox14.Text Cells(myNewRow, 15).Value = Form1.TextBox15.Text For myFormulas = 16 To 33 Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1, myFormulas).Formula Next End Sub HTH, RadarEye |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding row from userfrom and copying formulas
One last thing...
What if I had a formula in column 3 that I also needed to have copied to the new row? RadarEye wrote: Hi Criss, I cooked the procedure below. But before you use this make usure that there is a blank line between the figures and the SUM formulas in P-AG. The blank line MUSR be included in the formulas. So if the last row with figures is line 23, line 24 will be blank. the formula for P25 looks like =SUM(P1:P24) Now you cab use this procedure Private Sub cmdAddRow_Click() Dim myNewRow As Long Dim myFormulas As Long myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown Cells(myNewRow, 1).Value = Form1.TextBox1.Text Cells(myNewRow, 2).Value = Form1.TextBox2.Text Cells(myNewRow, 3).Value = Form1.TextBox3.Text Cells(myNewRow, 4).Value = Form1.TextBox4.Text Cells(myNewRow, 5).Value = Form1.TextBox5.Text Cells(myNewRow, 6).Value = Form1.TextBox6.Text Cells(myNewRow, 7).Value = Form1.TextBox7.Text Cells(myNewRow, 8).Value = Form1.TextBox8.Text Cells(myNewRow, 9).Value = Form1.TextBox9.Text Cells(myNewRow, 10).Value = Form1.TextBox10.Text Cells(myNewRow, 11).Value = Form1.TextBox12.Text Cells(myNewRow, 12).Value = Form1.TextBox12.Text Cells(myNewRow, 13).Value = Form1.TextBox13.Text Cells(myNewRow, 14).Value = Form1.TextBox14.Text Cells(myNewRow, 15).Value = Form1.TextBox15.Text For myFormulas = 16 To 33 Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1, myFormulas).Formula Next End Sub HTH, RadarEye |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding row from userfrom and copying formulas
Hi Chris,
Sorry for the late reply. You have now: For myFormulas = 16 To 33 Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1, myFormulas).Formula Next just add 1 line: Cells(myNewRow, 3).Formula = Cells(myNewRow - 1, 3).Formula HTH RadarEye. Chris wrote: One last thing... What if I had a formula in column 3 that I also needed to have copied to the new row? RadarEye wrote: Hi Criss, I cooked the procedure below. But before you use this make usure that there is a blank line between the figures and the SUM formulas in P-AG. The blank line MUSR be included in the formulas. So if the last row with figures is line 23, line 24 will be blank. the formula for P25 looks like =SUM(P1:P24) Now you cab use this procedure Private Sub cmdAddRow_Click() Dim myNewRow As Long Dim myFormulas As Long myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown Cells(myNewRow, 1).Value = Form1.TextBox1.Text Cells(myNewRow, 2).Value = Form1.TextBox2.Text Cells(myNewRow, 3).Value = Form1.TextBox3.Text Cells(myNewRow, 4).Value = Form1.TextBox4.Text Cells(myNewRow, 5).Value = Form1.TextBox5.Text Cells(myNewRow, 6).Value = Form1.TextBox6.Text Cells(myNewRow, 7).Value = Form1.TextBox7.Text Cells(myNewRow, 8).Value = Form1.TextBox8.Text Cells(myNewRow, 9).Value = Form1.TextBox9.Text Cells(myNewRow, 10).Value = Form1.TextBox10.Text Cells(myNewRow, 11).Value = Form1.TextBox12.Text Cells(myNewRow, 12).Value = Form1.TextBox12.Text Cells(myNewRow, 13).Value = Form1.TextBox13.Text Cells(myNewRow, 14).Value = Form1.TextBox14.Text Cells(myNewRow, 15).Value = Form1.TextBox15.Text For myFormulas = 16 To 33 Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1, myFormulas).Formula Next End Sub HTH, RadarEye |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Userfrom problem... | Excel Discussion (Misc queries) | |||
| How do you use DTpicker in a Userfrom | Excel Discussion (Misc queries) | |||
| sum function in a userfrom | Excel Programming | |||
| Userfrom textboxes | Excel Programming | |||
| Userfrom textboxes | Excel Programming | |||
Linear Mode
