I need to remove the offset

[netwerkz]

Hi all,

Edited by mod to add: This thread refers to thread Copy a range of cells (http://www.vbaexpress.com/forum/showthread.php?56590-Copy-a-range-of-cells)

My code...



Private Sub cmdAccept_Click()
Dim lastrow As Long
Dim RowCount As Long
Dim fieldData As Worksheet
lastrow = Range("A65536").End(xlUp).Row

RowCount = Worksheets("fieldData").Range("A2").CurrentRegion.Rows.Count
With Worksheets("fieldData").Range("A2")
.Offset(RowCount, 0).Value = Me.txtBox_fieldData_dateDelivered.Value
.Offset(RowCount, 1).Value = Me.comboBox_fieldData_fieldName.Value
.Offset(RowCount, 2).Value = Me.txtBox_fieldData_acres.Value
.Offset(RowCount, 3).Value = Me.comboBox_fieldData_crop.Value
.Offset(RowCount, 4).Value = Me.txtBox_fieldData_product1.Value
.Offset(RowCount, 5).Value = Me.txtBox_fieldData_product2.Value
.Offset(RowCount, 6).Value = Me.txtBox_fieldData_product3.Value
.Offset(RowCount, 7).Value = Me.txtBox_fieldData_product4.Value
.Offset(RowCount, 8).Value = Me.txtBox_fieldData_product5.Value
.Offset(RowCount, 9).Value = Me.txtBox_fieldData_product6.Value
End With
Call totals
End Sub




Sub totals()
Sheets("fieldData").Select
Range("K2").Select
ActiveCell.FormulaR1C1 = "=SUM(E2:J2)"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=(11.06*E2)+(11.7*F2)+(11.04*G2)+(10.9*H2)+(10.28*I2)+(9.5*J2)"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(32/100)/C2)+((11.7*F2)*(10/100)/C2)+((11.04*G2)*(12/100)/C2)+((10.9*H2)*(8/100)/C2)+((10.28*I2)*(7/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(34/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(25/100)/C2)+((10.28*I2)*(24/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(26/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
End Sub


On the output of the data sheet, this part of the code inserts an apostrophe (') around each cell number throwing the totals formulas into chaos with a #VALUE! warning in each cell on that row.



Option Explicit
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub


What I'm trying to do is remove the offset (someone suggested it, but it's not what I'm looking for in an output) and get the corresponding data that is input on a userform, added to the next consecutive empty row. I also tried to use VBA to gather the cell value, run a formula and place the formula value into totals columns on each row, based on the data received. The next thing I want the code to do is clear the userform once the user has clicked the "Accept" commandbutton. After that, I want the user to be able to click on a "Summary Page" tab on the userform and select a field to view data already entered.

[netwerkz]

After all that is cleaned by your suggestions, I want to have a "Print Page" that will allow the user to print a report of the summary for each field selected in one of four categories: *.PDF (for imaging and emailing), SMS, and to a selected printer. This part, I have NO CLUE on! :dunno

[netwerkz]

Quick update...The userform allows the user to continue adding data and it does appear on the next empty row, but the formula columns remain on the first row, A2. I thought adding a "$" before the cell letters would clear up the problem, but it halted everything after the first calling of "Sub totals()". I removed them and now all I get on the data sheet is "#NAME!".

Any clues?

[SamT]

0/100 = 0: 0/n = 0: 0*n = 0

Therefore (11.06*E2)*(0/100)/C2) = 0

[netwerkz]

This is what the "Sub totals()" module outputs...

=(((11.06*'E2')*(0/100)/$B:$B)+((11.7*'F2')*(0/100)/$B:$B)+((11.04*'G2')*(26/100)/$B:$B)+((10.9*'H2')*(0/100)/$B:$B)+((10.28*'I2')*(0/100)/$B:$B)+((9.5*'J2')*(0/100)/$B:$B))

Do you see the (') in each cell reference?

[netwerkz]

0/100 = 0: 0/n = 0: 0*n = 0

Therefore (11.06*E2)*(0/100)/C2) = 0


Hey SamT! This is the same project I've been working and you've been contributing on. The math on the formula is correct. Zeros are acceptable in some of the calculations.

[netwerkz]

I'm also having issues with the formula codes, as they change "/C2" to "$B:$B", which actually needs to be the value in the C column, as new rows are added.



Sub totals()
Sheets("fieldData").Select
Range("K2").Select
ActiveCell.FormulaR1C1 = "=SUM(E2:J2)"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=(11.06*E2)+(11.7*F2)+(11.04*G2)+(10.9*H2)+(10.28*I2)+(9.5*J2)"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(32/100)/C2)+((11.7*F2)*(10/100)/C2)+((11.04*G2)*(12/100)/C2)+((10.9*H2)*(8/100)/C2)+((10.28*I2)*(7/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(34/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(25/100)/C2)+((10.28*I2)*(24/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(26/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
End Sub

[SamT]

Just for grins, try assigning them as standard formulas

Range("L2").Formula = "=(11.06*E2)+(11.7*F2)+(11.04*G2)+(10.9*H2)+(10.28*I2)+(9.5*J2)"



An R1C1 formula would look like

Range("L2").FormulaR1C1 = "=(11.06*R[-7]C)+(11.7*R[-6]C)+(11.04*R[-5]C)+(10.9*R[-4]C)+(10.28*R[-3]C)+(9.5*R[-2]C)"
However, since I never use R1C1 formulas it might be R(-7)C, etc

[netwerkz]

Just for grins, try assigning them as standard formulas

Range("L2").Formula = "=(11.06*E2)+(11.7*F2)+(11.04*G2)+(10.9*H2)+(10.28*I2)+(9.5*J2)"



An R1C1 formula would look like

Range("L2").FormulaR1C1 = "=(11.06*R[-7]C)+(11.7*R[-6]C)+(11.04*R[-5]C)+(10.9*R[-4]C)+(10.28*R[-3]C)+(9.5*R[-2]C)"
However, since I never use R1C1 formulas it might be R(-7)C, etc

Sounds like fun! I'll give it a try tomorrow when I get in to work. I did however, change the offset from A2 to A1 and it works just as I hoped. But, now I'm encountering a problem with the formulas section where it doesn't use the formulas on each row as I hoped. I'll try anything to make it work as soon as possible. 😃

[SamT]

change the offset from A2 to A1
I sure am missing something, because I can't find any offset or even any reference to A2

[netwerkz]

I changed the offset in the UserForm code from "A2" to A1" and it is working just fine for the data entry piece...

From:


Private Sub cmdAccept_Click()
Dim lastrow As Long
Dim RowCount As Long
Dim fieldData As Worksheet
lastrow = Range("A65536").End(xlUp).Row

RowCount = Worksheets("fieldData").Range("A2").CurrentRegion.Rows.Count
With Worksheets("fieldData").Range("A2")
.Offset(RowCount, 0).Value = Me.txtBox_fieldData_dateDelivered.Value
.Offset(RowCount, 1).Value = Me.comboBox_fieldData_fieldName.Value
.Offset(RowCount, 2).Value = Me.txtBox_fieldData_acres.Value
.Offset(RowCount, 3).Value = Me.comboBox_fieldData_crop.Value
.Offset(RowCount, 4).Value = Me.txtBox_fieldData_product1.Value
.Offset(RowCount, 5).Value = Me.txtBox_fieldData_product2.Value
.Offset(RowCount, 6).Value = Me.txtBox_fieldData_product3.Value
.Offset(RowCount, 7).Value = Me.txtBox_fieldData_product4.Value
.Offset(RowCount, 8).Value = Me.txtBox_fieldData_product5.Value
.Offset(RowCount, 9).Value = Me.txtBox_fieldData_product6.Value
End With
Call totals
End Sub

To:


Private Sub cmdAccept_Click()

Dim lastrow As Long
Dim RowCount As Long
Dim fieldData As Worksheet
lastrow = Range("A65536").End(xlUp).Row

RowCount = Worksheets("fieldData").Range("A1").CurrentRegion.Rows.Count
With Worksheets("fieldData").Range("A1")
.Offset(RowCount, 0).Value = Me.txtBox_fieldData_dateDelivered.Value
.Offset(RowCount, 1).Value = Me.comboBox_fieldData_fieldName.Value
.Offset(RowCount, 2).Value = Me.txtBox_fieldData_acres.Value
.Offset(RowCount, 3).Value = Me.comboBox_fieldData_crop.Value
.Offset(RowCount, 4).Value = Me.txtBox_fieldData_product1.Value
.Offset(RowCount, 5).Value = Me.txtBox_fieldData_product2.Value
.Offset(RowCount, 6).Value = Me.txtBox_fieldData_product3.Value
.Offset(RowCount, 7).Value = Me.txtBox_fieldData_product4.Value
.Offset(RowCount, 8).Value = Me.txtBox_fieldData_product5.Value
.Offset(RowCount, 9).Value = Me.txtBox_fieldData_product6.Value
End With
Call totals
End Sub


Now, if I could only have the formulas run and display right alongside the data entered on the UserForm. Someone suggested I drop the "R1C1" part of the formula and it worked. However, I need the results to be entered on the same row as the data that was entered.



Sub totals()
Sheets("fieldData").Select
Range("K2").Select
ActiveCell.Formula = "=SUM(E2:J2)"
Range("L2").Select
ActiveCell.Formula = "=(11.06*E2)+(11.7*F2)+(11.04*G2)+(10.9*H2)+(10.28*I2)+(9.5*J2)"
Range("M2").Select
ActiveCell.Formula = "=(((11.06*E2)*(32/100)/C2)+((11.7*F2)*(10/100)/C2)+((11.04*G2)*(12/100)/C2)+((10.9*H2)*(8/100)/C2)+((10.28*I2)*(7/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("N2").Select
ActiveCell.Formula = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(34/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(25/100)/C2)+((10.28*I2)*(24/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("O2").Select
ActiveCell.Formula = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
Range("P2").Select
ActiveCell.Formula = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(26/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
End Sub

[netwerkz]

OK!!! I figured out how to get the formula results to appear on the same row as the data, but there is a bad source of data being fed into the formula. If you notice in my code, that it is all referring to row 2, instead of the row it should be refer to.


Private Sub cmdAccept_Click()


Dim lastrow As Long
Dim RowCount As Long
Dim fieldData As Worksheet
lastrow = Range("A65536").End(xlUp).Row

RowCount = Worksheets("fieldData").Range("A1").CurrentRegion.Rows.Count
With Worksheets("fieldData").Range("A1")
.Offset(RowCount, 0).Value = Me.txtBox_fieldData_dateDelivered.Value
.Offset(RowCount, 1).Value = Me.comboBox_fieldData_fieldName.Value
.Offset(RowCount, 2).Value = Me.txtBox_fieldData_acres.Value
.Offset(RowCount, 3).Value = Me.comboBox_fieldData_crop.Value
.Offset(RowCount, 4).Value = Me.txtBox_fieldData_product1.Value
.Offset(RowCount, 5).Value = Me.txtBox_fieldData_product2.Value
.Offset(RowCount, 6).Value = Me.txtBox_fieldData_product3.Value
.Offset(RowCount, 7).Value = Me.txtBox_fieldData_product4.Value
.Offset(RowCount, 8).Value = Me.txtBox_fieldData_product5.Value
.Offset(RowCount, 9).Value = Me.txtBox_fieldData_product6.Value
.Offset(RowCount, 10).Value = "=SUM(E2:J2)"
.Offset(RowCount, 11).Value = "=(11.06*E2)+(11.7*F2)+(11.04*G2)+(10.9*H2)+(10.28*I2)+(9.5*J2)"
.Offset(RowCount, 12).Value = "=(((11.06*E2)*(32/100)/C2)+((11.7*F2)*(10/100)/C2)+((11.04*G2)*(12/100)/C2)+((10.9*H2)*(8/100)/C2)+((10.28*I2)*(7/100)/C2)+((9.5*J2)*(0/100)/C2))"
.Offset(RowCount, 13).Value = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(34/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(25/100)/C2)+((10.28*I2)*(24/100)/C2)+((9.5*J2)*(0/100)/C2))"
.Offset(RowCount, 14).Value = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(0/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
.Offset(RowCount, 15).Value = "=(((11.06*E2)*(0/100)/C2)+((11.7*F2)*(0/100)/C2)+((11.04*G2)*(26/100)/C2)+((10.9*H2)*(0/100)/C2)+((10.28*I2)*(0/100)/C2)+((9.5*J2)*(0/100)/C2))"
End With
' Call totals
End Sub


Unfortunately, I don't know how to change this. Does anyone have any other ideas?

[SamT]

You will have to take Dim RowCount As Long out of the sub and make it a Module level variable, possibly Public.

Then use

Range("L" & RowCount).Formula = "=(11.06*E$2)+(11.7*F$2)+(11.04*G$2)+(10.9*H$2)+(10.28*I$2)+(9.5*J$2)"

This is redundant

SomeCell.Select
ActiveCell do somethingOnly the Macro recorder uses that style and even then only because the Recorder can only record the effects of your individual keystrokes and mouse clicks.

[SamT]

You need to be aware that

RowCount = Worksheets("fieldData").Range("A1").CurrentRegion.Rows.Count
is the Row number of the last non-empty Row in the Current Region. You may want to add 1 to it

RowCount = Worksheets("fieldData").Range("A1").CurrentRegion.Rows.Count + 1



:dunno:

[netwerkz]

What I'm looking for is a way to have (RowCount, 10) thru (RowCount, 15) us the data from that individual row. I want the value of the formula to be displayed in columns K - P. Such as:



Date
Field#
Acres
Crop
Mix 1
Mix 2
Mix 3
Mix 4
Mix 5
Mix 6
Gallons Loaded
Total lbs
Total N/Acre


7/6/2016
Field 1
800
Beets
600
500
400
300
200
100
=SUM($E2:$J2)
=11.06*$E2
=SUM(((11.06*$E2)*(32/100)/$C2)+((11.7*$F2)*(10/100)/$C2)+((11.04*$G2)*(12/100)/$C2)+((10.9*$H2)*(8/100)/$C2)+((10.28*$I2)*(7/100)/$C2)+((9.5*$J2)*(0/100)/$C2))


7/6/2017
Field 2
801
Beets
650
550
450
350
250
150
=SUM($E3:$J3)
=11.06*$E3
=SUM(((11.06*$E3)*(32/100)/$C3)+((11.7*$F3)*(10/100)/$C3)+((11.04*$G3)*(12/100)/$C3)+((10.9*$H3)*(8/100)/$C3)+((10.28*$I3)*(7/100)/$C3)+((9.5*$J3)*(0/100)/$C3))


7/6/2018
Field 3
802
Beets
700
600
500
400
300
200
=SUM($E4:$J4)
=11.06*$E4
=SUM(((11.06*$E4)*(32/100)/$C4)+((11.7*$F4)*(10/100)/$C4)+((11.04*$G4)*(12/100)/$C4)+((10.9*$H4)*(8/100)/$C4)+((10.28*$I4)*(7/100)/$C4)+((9.5*$J4)*(0/100)/$C4))


7/6/2019
Field 4
803
Beets
750
650
550
450
350
250
=SUM($E5:$J5)
=11.06*$E5
=SUM(((11.06*$E5)*(32/100)/$C5)+((11.7*$F5)*(10/100)/$C5)+((11.04*$G5)*(12/100)/$C5)+((10.9*$H5)*(8/100)/$C5)+((10.28*$I5)*(7/100)/$C5)+((9.5*$J5)*(0/100)/$C5))


7/6/2020
Field 5
804
Beets
800
700
600
500
400
300
=SUM($E6:$J6)
=11.06*$E6
=SUM(((11.06*$E6)*(32/100)/$C6)+((11.7*$F6)*(10/100)/$C6)+((11.04*$G6)*(12/100)/$C6)+((10.9*$H6)*(8/100)/$C6)+((10.28*$I6)*(7/100)/$C6)+((9.5*$J6)*(0/100)/$C6))


7/6/2021
Field 6
805
Beets
850
750
650
550
450
350
=SUM($E7:$J7)
=11.06*$E7
=SUM(((11.06*$E7)*(32/100)/$C7)+((11.7*$F7)*(10/100)/$C7)+((11.04*$G7)*(12/100)/$C7)+((10.9*$H7)*(8/100)/$C7)+((10.28*$I7)*(7/100)/$C7)+((9.5*$J7)*(0/100)/$C7))


7/6/2022
Field 7
806
Beets
900
800
700
600
500
400
=SUM($E8:$J8)
=11.06*$E8
=SUM(((11.06*$E8)*(32/100)/$C8)+((11.7*$F8)*(10/100)/$C8)+((11.04*$G8)*(12/100)/$C8)+((10.9*$H8)*(8/100)/$C8)+((10.28*$I8)*(7/100)/$C8)+((9.5*$J8)*(0/100)/$C8))


7/6/2023
Field 8
807
Beets
950
850
750
650
550
450
=SUM($E9:$J9)
=11.06*$E9
=SUM(((11.06*$E9)*(32/100)/$C9)+((11.7*$F9)*(10/100)/$C9)+((11.04*$G9)*(12/100)/$C9)+((10.9*$H9)*(8/100)/$C9)+((10.28*$I9)*(7/100)/$C9)+((9.5*$J9)*(0/100)/$C9))


7/6/2024
Field 9
808
Beets
1000
900
800
700
600
500
=SUM($E10:$J10)
=11.06*$E10
=SUM(((11.06*$E10)*(32/100)/$C10)+((11.7*$F10)*(10/100)/$C10)+((11.04*$G10)*(12/100)/$C10)+((10.9*$H10)*(8/100)/$C10)+((10.28*$I10)*(7/100)/$C10)+((9.5*$J10)*(0/100)/$C10))


7/6/2025
Field 10
809
Beets
1050
950
850
750
650
550
=SUM($E11:$J11)
=11.06*$E11
=SUM(((11.06*$E11)*(32/100)/$C11)+((11.7*$F11)*(10/100)/$C11)+((11.04*$G11)*(12/100)/$C11)+((10.9*$H11)*(8/100)/$C11)+((10.28*$I11)*(7/100)/$C11)+((9.5*$J11)*(0/100)/$C11))


7/6/2026
Field 11
810
Beets
1100
1000
900
800
700
600
=SUM($E12:$J12)
=11.06*$E12
=SUM(((11.06*$E12)*(32/100)/$C12)+((11.7*$F12)*(10/100)/$C12)+((11.04*$G12)*(12/100)/$C12)+((10.9*$H12)*(8/100)/$C12)+((10.28*$I12)*(7/100)/$C12)+((9.5*$J12)*(0/100)/$C12))


7/6/2027
Field 12
811
Beets
1150
1050
950
850
750
650
=SUM($E13:$J13)
=11.06*$E13
=SUM(((11.06*$E13)*(32/100)/$C13)+((11.7*$F13)*(10/100)/$C13)+((11.04*$G13)*(12/100)/$C13)+((10.9*$H13)*(8/100)/$C13)+((10.28*$I13)*(7/100)/$C13)+((9.5*$J13)*(0/100)/$C13))


7/6/2028
Field 13
812
Beets
1200
1100
1000
900
800
700
=SUM($E14:$J14)
=11.06*$E14
=SUM(((11.06*$E14)*(32/100)/$C14)+((11.7*$F14)*(10/100)/$C14)+((11.04*$G14)*(12/100)/$C14)+((10.9*$H14)*(8/100)/$C14)+((10.28*$I14)*(7/100)/$C14)+((9.5*$J14)*(0/100)/$C14))


7/6/2029
Field 14
813
Beets
1250
1150
1050
950
850
750
=SUM($E15:$J15)
=11.06*$E15
=SUM(((11.06*$E15)*(32/100)/$C15)+((11.7*$F15)*(10/100)/$C15)+((11.04*$G15)*(12/100)/$C15)+((10.9*$H15)*(8/100)/$C15)+((10.28*$I15)*(7/100)/$C15)+((9.5*$J15)*(0/100)/$C15))


7/6/2030
Field 15
814
Beets
1300
1200
1100
1000
900
800
=SUM($E16:$J16)
=11.06*$E16
=SUM(((11.06*$E16)*(32/100)/$C16)+((11.7*$F16)*(10/100)/$C16)+((11.04*$G16)*(12/100)/$C16)+((10.9*$H16)*(8/100)/$C16)+((10.28*$I16)*(7/100)/$C16)+((9.5*$J16)*(0/100)/$C16))


7/6/2031
Field 16
815
Beets
1350
1250
1150
1050
950
850
=SUM($E17:$J17)
=11.06*$E17
=SUM(((11.06*$E17)*(32/100)/$C17)+((11.7*$F17)*(10/100)/$C17)+((11.04*$G17)*(12/100)/$C17)+((10.9*$H17)*(8/100)/$C17)+((10.28*$I17)*(7/100)/$C17)+((9.5*$J17)*(0/100)/$C17))

[netwerkz]

I just don't know how to get column 11 (or column K) to refer to data placed in columns 1 - 10 (or columns A - J), same for the remaining columns.

[Paul_Hossler]

If you're just wanting to put in formulas, either of these would probably work for you

The second is most likely faster and more elegant, IF the sheet layout permits




Option Explicit

Sub FillInFormula_1()
Dim r As Long
Dim sFormula As String
Dim rCell As Range

For Each rCell In Range(Range("A2"), Range("A2").End(xlDown)).Cells

If Len(Trim(rCell.Value)) = 0 Then Exit For ' I think you have some spaces

r = rCell.Row

'col K
'=SUM($E2:$J2)
sFormula = "=SUM($E" & r & ":$J" & r & ")"
rCell.Offset(0, 10).Formula = sFormula

'col L
'=11.06*$E2
sFormula = "=11.06*$E" & r
rCell.Offset(0, 11).Formula = sFormula

'col M
'=SUM(((11.06*$E2)*(32/100)/$C2)+((11.7*$F2)*(10/100)/$C2)+((11.04*$G2)*(12/100)/$C2)+((10.9*$H2)*(8/100)/$C2)+((10.28*$I2)*(7/100)/$C2)+((9.5*$J2)*(0/100)/$C2))
sFormula = "=SUM(((11.06*$E" & r & ")*(32/100)/$C" & r & ")+((11.7*$F" & r & ")*(10/100)/$C" & r & ")+((11.04*$G" & r & ")*(12/100)/$C" & r & ")+((10.9*$H" & r & ")*(8/100)/$C" & r & ")+((10.28*$I" & r & ")*(7/100)/$C" & r & ")+((9.5*$J" & r & ")*(0/100)/$C" & r & "))"
rCell.Offset(0, 12).Formula = sFormula

Next
End Sub

Sub FillInFormula_2()
Dim sFormula As String
Dim r As Long

'col K
sFormula = "=SUM($E2:$J2)"
Range("K2").Formula = sFormula

'col L
sFormula = "=11.06*$E2"
Range("L2").Formula = sFormula

'col M
sFormula = "=SUM(((11.06*$E2)*(32/100)/$C2)+((11.7*$F2)*(10/100)/$C2)+((11.04*$G2)*(12/100)/$C2)+((10.9*$H2)*(8/100)/$C2)+((10.28*$I2)*(7/100)/$C2)+((9.5*$J2)*(0/100)/$C2))"
Range("M2").Formula = sFormula

r = Range("A2").End(xlDown).Row

Range("K2").AutoFill Destination:=Range("K2:K" & r)
Range("L2").AutoFill Destination:=Range("L2:L" & r)
Range("M2").AutoFill Destination:=Range("M2:M" & r)
End Sub

[SamT]

What I'm looking for is a way to have (RowCount, 10) thru (RowCount, 15)
That is not quite correct.

I would say that you want to insert those formulas in the row the first Row the UserForm inserts date, then copy it down every time the Form inserts a new Row of data.

I am "Assuming" that the very first Row to receive Form data is always the same.

There was not enough information in this thread for me to perfect this code, but I think you will see how easy it is to alter to fit your needs. It WILL add each set of data to the next empty Row on the sheet, AND it will not add all those formulas to the sheet, helping keep the file size down and not slowing down the workbook when it gets a lot of data in it.

Obviously N is Nitrogen, but I can't tell what else you are using/calculating, so I use Potash and Potassium for examples.

Almost every thing in this code only had the first lines) of a section typed, then I just C&Ped as many times as need . Then I just edited the numbers or used Ctrl+H to replace "N_" with "P_ "and "K_." Placing the 0's in the Constants allowed all the lines of calculations to look the same in code.

Note that even the Cell assignments use Variables when available. You can look at the Worksheet Data assignment section and tell exactly what is going where. Maybe I should have used Mix1_Qty instead of just Mix1.

I would place all these Constants and and Variables in a separate Standard Module

Const FirstDataRow As Long = 2 'adjust to suit

'Lbs/Gal x %
Const N_Mix1 As Double = 11.06 * 0.32
Const N_Mix2 As Double = 11.7 * 0.1
Const N_Mix1 As Double = 11.04 * 0.12
Const N_Mix1 As Double = 10.9 * 0.8
Const N_Mix1 As Double = 10.28 * 0.7
Const N_Mix1 As Double = 9.5 * 0

Const K_Mix1 As Double = 10 * 0.1 'Adjust all as needed
Const K_Mix2 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1

Const P_Mix1 As Double = 10 * 0.1 'Adjust all as needed
Const P_Mix2 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1


Dim Acres As Double
Dim Mix1 As Double
Dim Mix2 As Double
Dim Mix3 As Double
Dim Mix4 As Double
Dim Mix5 As Double
Dim Mix6 As Double

Dim Gals_Loaded As Double

Dim Total_N_Lbs As Double
Dim Total_N As Double
Dim Total_N_Acre As Double

Dim Total_P_Lbs As Double
Dim Total_P As Double
Dim Total_P_Acre As Double

Dim Total_K_Lbs As Double
Dim Total_K As Double
Dim Total_K_Acre As Double

This is a almost perfected example of the Command Button code I would use

Private Sub cmdAccept_Click()
Dim NextRow As Long

With Me
Acres = txtBox_fieldData_acres
Mix1 = txtBox_fieldData_product1
Mix2 = txtBox_fieldData_product2
Mix3 = txtBox_fieldData_product3
Mix4 = txtBox_fieldData_product4
Mix5 = txtBox_fieldData_product5
Mix6 = txtBox_fieldData_product6
End With


''''''''''''''''''''''''''''''''''''''Begin Calculations
Gals_Loaded = Mix1 + Mix2 + Mix3 + Mix4

Total_N = (N_Mix1 * Mix1) + (N_Mix2 * Mix2) + (N_Mix3 * Mix3) + (N_Mix4 * Mix4) + (N_Mix5 * Mix5) + (N_Mix6 * Mix6)
Total_N_Acre = Total_N / Acres

Total_P = (P_Mix1 * Mix1) + (P_Mix2 * Mix2) + (P_Mix3 * Mix3) + (P_Mix4 * Mix4) + (P_Mix5 * Mix5) + (P_Mix6 * Mix6)
Total_P_Acre = Total_P / Acres

Total_K = (K_Mix1 * Mix1) + (K_Mix2 * Mix2) + (K_Mix3 * Mix3) + (K_Mix4 * Mix4) + (K_Mix5 * Mix5) + (K_Mix6 * Mix6)
Total_K_Acre = Total_K / Acres
''''''''''''''''''''''''''''''''''''''''''''''''''End Caclulations

With Worksheets("fieldData")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
If NextRow < FirstDataRow Then NextRow = FirstDataRow

.Cells(NextRow, "A").Value = Me.txtBox_fieldData_dateDelivered.Value
.Cells(NextRow, "B").Value = Me.comboBox_fieldData_fieldName.Value
.Cells(NextRow, "C").Value = Acres
.Cells(NextRow, "D").Value = Me.comboBox_fieldData_crop.Value
.Cells(NextRow, "E").Value = Mix1
.Cells(NextRow, "F").Value = Mix2
.Cells(NextRow, "G").Value = Mix3
.Cells(NextRow, "H").Value = Mix4
.Cells(NextRow, "I").Value = Mix5
.Cells(NextRow, "J").Value = Mix6

.Cells(NextRow, "K").Value = Gals_Loaded
.Cells(NextRow, "L").Value = Total_N 'Adjust names to suit
.Cells(NextRow, "M").Value = Total_N_Acres
.Cells(NextRow, "N").Value = Total_P
.Cells(NextRow, "O").Value = Total_P_Acre
.Cells(NextRow, "P").Value = Total_K
End With
End Sub

[netwerkz]

That is not quite correct.

I would say that you want to insert those formulas in the row the first Row the UserForm inserts date, then copy it down every time the Form inserts a new Row of data.

I am "Assuming" that the very first Row to receive Form data is always the same.

There was not enough information in this thread for me to perfect this code, but I think you will see how easy it is to alter to fit your needs. It WILL add each set of data to the next empty Row on the sheet, AND it will not add all those formulas to the sheet, helping keep the file size down and not slowing down the workbook when it gets a lot of data in it.

Obviously N is Nitrogen, but I can't tell what else you are using/calculating, so I use Potash and Potassium for examples.

Almost every thing in this code only had the first lines) of a section typed, then I just C&Ped as many times as need . Then I just edited the numbers or used Ctrl+H to replace "N_" with "P_ "and "K_." Placing the 0's in the Constants allowed all the lines of calculations to look the same in code.

Note that even the Cell assignments use Variables when available. You can look at the Worksheet Data assignment section and tell exactly what is going where. Maybe I should have used Mix1_Qty instead of just Mix1.

I would place all these Constants and and Variables in a separate Standard Module

Const FirstDataRow As Long = 2 'adjust to suit

'Lbs/Gal x %
Const N_Mix1 As Double = 11.06 * 0.32
Const N_Mix2 As Double = 11.7 * 0.1
Const N_Mix1 As Double = 11.04 * 0.12
Const N_Mix1 As Double = 10.9 * 0.8
Const N_Mix1 As Double = 10.28 * 0.7
Const N_Mix1 As Double = 9.5 * 0

Const K_Mix1 As Double = 10 * 0.1 'Adjust all as needed
Const K_Mix2 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1
Const K_Mix1 As Double = 10 * 0.1

Const P_Mix1 As Double = 10 * 0.1 'Adjust all as needed
Const P_Mix2 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1
Const P_Mix1 As Double = 10 * 0.1


Dim Acres As Double
Dim Mix1 As Double
Dim Mix2 As Double
Dim Mix3 As Double
Dim Mix4 As Double
Dim Mix5 As Double
Dim Mix6 As Double

Dim Gals_Loaded As Double

Dim Total_N_Lbs As Double
Dim Total_N As Double
Dim Total_N_Acre As Double

Dim Total_P_Lbs As Double
Dim Total_P As Double
Dim Total_P_Acre As Double

Dim Total_K_Lbs As Double
Dim Total_K As Double
Dim Total_K_Acre As Double

This is a almost perfected example of the Command Button code I would use

Private Sub cmdAccept_Click()
Dim NextRow As Long

With Me
Acres = txtBox_fieldData_acres
Mix1 = txtBox_fieldData_product1
Mix2 = txtBox_fieldData_product2
Mix3 = txtBox_fieldData_product3
Mix4 = txtBox_fieldData_product4
Mix5 = txtBox_fieldData_product5
Mix6 = txtBox_fieldData_product6
End With


''''''''''''''''''''''''''''''''''''''Begin Calculations
Gals_Loaded = Mix1 + Mix2 + Mix3 + Mix4

Total_N = (N_Mix1 * Mix1) + (N_Mix2 * Mix2) + (N_Mix3 * Mix3) + (N_Mix4 * Mix4) + (N_Mix5 * Mix5) + (N_Mix6 * Mix6)
Total_N_Acre = Total_N / Acres

Total_P = (P_Mix1 * Mix1) + (P_Mix2 * Mix2) + (P_Mix3 * Mix3) + (P_Mix4 * Mix4) + (P_Mix5 * Mix5) + (P_Mix6 * Mix6)
Total_P_Acre = Total_P / Acres

Total_K = (K_Mix1 * Mix1) + (K_Mix2 * Mix2) + (K_Mix3 * Mix3) + (K_Mix4 * Mix4) + (K_Mix5 * Mix5) + (K_Mix6 * Mix6)
Total_K_Acre = Total_K / Acres
''''''''''''''''''''''''''''''''''''''''''''''''''End Caclulations

With Worksheets("fieldData")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
If NextRow < FirstDataRow Then NextRow = FirstDataRow

.Cells(NextRow, "A").Value = Me.txtBox_fieldData_dateDelivered.Value
.Cells(NextRow, "B").Value = Me.comboBox_fieldData_fieldName.Value
.Cells(NextRow, "C").Value = Acres
.Cells(NextRow, "D").Value = Me.comboBox_fieldData_crop.Value
.Cells(NextRow, "E").Value = Mix1
.Cells(NextRow, "F").Value = Mix2
.Cells(NextRow, "G").Value = Mix3
.Cells(NextRow, "H").Value = Mix4
.Cells(NextRow, "I").Value = Mix5
.Cells(NextRow, "J").Value = Mix6

.Cells(NextRow, "K").Value = Gals_Loaded
.Cells(NextRow, "L").Value = Total_N 'Adjust names to suit
.Cells(NextRow, "M").Value = Total_N_Acres
.Cells(NextRow, "N").Value = Total_P
.Cells(NextRow, "O").Value = Total_P_Acre
.Cells(NextRow, "P").Value = Total_K
End With
End Sub



Hey SamT! Good Morning all!

This is what we finally figured out...



Private Sub cmdAccept_Click()
Dim RowCount As Long
Dim fieldData As Worksheet
Set fieldData = Worksheets("fieldData")

RowCount = fieldData.Range("A1").CurrentRegion.Rows.Count + 1
With fieldData
.Cells(RowCount, 1).Value = Me.txtBox_fieldData_dateDelivered.Value
.Cells(RowCount, 2).Value = Me.comboBox_fieldData_fieldName.Value
.Cells(RowCount, 3).Value = Me.txtBox_fieldData_acres.Value
.Cells(RowCount, 4).Value = Me.comboBox_fieldData_crop.Value
.Cells(RowCount, 5).Value = Me.txtBox_fieldData_product1.Value
.Cells(RowCount, 6).Value = Me.txtBox_fieldData_product2.Value
.Cells(RowCount, 7).Value = Me.txtBox_fieldData_product3.Value
.Cells(RowCount, 8).Value = Me.txtBox_fieldData_product4.Value
.Cells(RowCount, 9).Value = Me.txtBox_fieldData_product5.Value
.Cells(RowCount, 10).Value = Me.txtBox_fieldData_product6.Value
.Range("K" & RowCount - 1 & ":P" & RowCount).FillDown
End With
End Sub


That's it! It works beautifully!

[SamT]

Yeah, I considered that method first. Did you notice my comments about how that can slow down Excel and increase the file size. each 10 Rows of your formulas takes up about the same hard drive space as the code does, yours or mine.

Not to mention the much greater hassle of adjusting formulas when mix ingredients change, as they are wont to do.

At least keep my example around for future reference.

An alternative that uses your formulas and still helps with speed and file size is to fill down, then convert the previous Row to values.

[netwerkz]

Yes, I did see your comments regarding the speed of the code, but we are not really concerned that much since it will be mostly older gentlemen who will be using it out in the field.

Are you up for another challenge? Now, I need to get the "Summary Page" up and running. Here's the code I have so far...



Private Sub UserForm1_Initialize()


With ListBox1
.AddItem.Range ("fieldName")
End With


OptionButton3.Value = True


End Sub

Private Sub cmdAdd_Click()
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i
End Sub

Private Sub cmdRemove_Click()
Dim counter As Integer
Dim i As Integer
counter = 0
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i - counter) Then
ListBox2.RemoveItem (i - counter)
counter = counter + 1
End If
Next i
CheckBox2.Value = False
End Sub

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = True
Next i
End If

If CheckBox1.Value = False Then
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next i
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
For i = 0 To ListBox2.ListCount - 1
ListBox2.Selected(i) = True
Next i
End If

If CheckBox2.Value = False Then
For i = 0 To ListBox2.ListCount - 1
ListBox2.Selected(i) = False
Next i
End If
End Sub

Private Sub OptionButton1_Click()
ListBox1.MultiSelect = 0
ListBox2.MultiSelect = 0
End Sub

Private Sub OptionButton2_Click()
ListBox1.MultiSelect = 1
ListBox2.MultiSelect = 1
End Sub

Private Sub OptionButton3_Click()
ListBox1.MultiSelect = 2
ListBox2.MultiSelect = 2
End Sub

Based on the field selected, at first I want the last date's data entered to be displayed.

Eventually, I will use a third page called "Print Page" on the UserForm, that will allow the user to select a field and choose to do "Single Select", "Click item or space to select multiple items", or "Hold down Shift and Ctrl to select multiple items". Luckily, I have figured out how to get the first and second ListBoxes, with "Check / Uncheck All" check boxes underneath each ListBox, "Add" and "Remove" buttons, and the "Select Type" area. The user will select the fields to print from a list of fieldName, then click the "Add" to populate the second ListBox. And, finally, select one or more types of output destinations, "Print to a specified printer", "Print to a PDF file", Print to a PDF and Email", "Print to SMS".

Are you up for it? :hi:

[SamT]

yeah, us older gents don't mind waithing 4-10 seconds for an excel sheet to update after we click the mouse.

Nah, you figgerd out the last problem by yerself, I got's to larn this new fangled computer thingy. It ain't like pickin' cotton by hand or fixin' an ole 8N, ya no?

[SamT]

In a more serious vein, why don't you use the Thread Tools button at the top and mark this thread solved.Then repost your last query in a new thread.

I would also suggest that you practice a professional self documenting code style and give your UserForm Controls meaningful names and avoid the use of 'Magic Numbers.'