I need to remove the offset

**netwerkz** · 07-25-2016, 11:08 AM

Hi all,

Edited by mod to add: This thread refers to thread 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** · 07-25-2016, 11:17 AM

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!

**netwerkz** · 07-25-2016, 11:43 AM

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** · 07-25-2016, 12:22 PM

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

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

**netwerkz** · 07-25-2016, 12:27 PM

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** · 07-25-2016, 12:29 PM

Originally Posted by SamT

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** · 07-25-2016, 12:53 PM

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** · 07-25-2016, 05:45 PM

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** · 07-25-2016, 06:31 PM

Originally Posted by 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

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** · 07-25-2016, 10:43 PM

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** · 07-26-2016, 11:04 AM

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** · 07-26-2016, 12:05 PM

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** · 07-26-2016, 12:15 PM

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 something

Only 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** · 07-26-2016, 12:24 PM

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

:

**netwerkz** · 07-26-2016, 02:07 PM

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** · 07-26-2016, 02:19 PM

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** · 07-26-2016, 03:17 PM

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** · 07-26-2016, 04:43 PM

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** · 07-27-2016, 07:04 AM

Originally Posted by SamT

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** · 07-27-2016, 09:29 AM

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.

Thread: I need to remove the offset

Thread Tools

Display

I need to remove the offset

Posting Permissions