Thread: Saving userform to sheet

Sorry Roy UK, thanks for correcting me.

Hi all- I'm sorry I know I've posted it twice, I'm really under pressure and need to fix this fast!

I'm creating what should be a fairly simple file. I have a userform that people fill in then they hit save and it writes to the worksheet "DATABASE". In the form you enter, amongst other info, the resource used and length of time. I then have a cell formula using VLOOKUP that calculates the cost based on the resource and time and another cell which adds this figure to the cost of the stock.

However, because I copied these two formulas into their columns when I press save the vb code i have puts the data in the next empty line. So either I have to find a way to write the forumlas in the code or get the form to save to the sheet differently.

Here's the code with the irrelevent stuff taken out.

Code:

Private Sub cmdSubmit_Click()

Dim RowCount As Long
Dim ctl As Control

' Check user input...

' Message to check first
Dim confirm As VbMsgBoxResult
     
    confirm = MsgBox("Are you sure you want to submit?", _
    vbYesNo, "STOP: Submit Data")
    If confirm = 6 Then

' Write data to worksheet

RowCount = Worksheets("Database").Range("A1").CurrentRegion.Rows.Count

With Worksheets("Database").Range("A1")
.Offset(RowCount, 0).Value = "P1088 (Bris)"
.Offset(RowCount, 1).Value = "80192D"
.Offset(RowCount, 2).Value = Me.txtWBS.Value
.Offset(RowCount, 4).Value = Me.txtDoj.Value
.Offset(RowCount, 5).Value = Me.txtAT.Value
.Offset(RowCount, 7).Value = Me.txtSC.Value
.Offset(RowCount, 8).Value = Me.txtJobdesc.Value
.Offset(RowCount, 9).Value = Me.cmbAct.Value
.Offset(RowCount, 10).Value = Me.txtWk.Value
.Offset(RowCount, 11).Value = Me.cmbPN.Value
.Offset(RowCount, 12).Value = Me.cmbFac.Value
.Offset(RowCount, 13).Value = Me.txtBT.Value
.Offset(RowCount, 14).Value = Me.txtPC.Value
.Offset(RowCount, 15).Value = Me.txtStock.Value
.Offset(RowCount, 16).Value = Me.txtMC.Value

End With
' Clear form after submitting...

As you can see numbers 3 and 6 are missing, these (columns C and F) are the ones with the formula already in. I tried switching it round so these were the first 2 columns and typing Range C1 at the start but that just errored and debugged highlighting that line of code.
The formula in column F is =IF(ISERROR(VLOOKUP(S13,BACKGROUND!J:M,4,FALSE)=TRUE),"",VLOOKUP(S13,BACKGROUND!J:M,4,FALSE)) the forumla in C is =IF(I13="",0,SUM((H13*I13)+J13))
Any ideas? I'm really stuck on this one and i need to complete the whole thing and enter almost 100 forms on it by tues evening! eek!
many thanks,
z

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

It doesn't matter how urgent this is to you, take the time to read the forum rules, then you will not get problems using the forum & would realise that you should not duplicate posts o any reason.

It would probably help if you didn't use "shorthand" in Thread Titles as well!

Looks like your code will always write to A1 of the database

Which column contains the formula?

Test this

Code:

Private Sub cmdSubmit_Click()

End Sub

Dim RowCount   As Long


' Check user input...

' Message to check first
Dim confirm    As VbMsgBoxResult

confirm = MsgBox("Are you sure you want to submit?", _
                 vbYesNo, "STOP: Submit Data")
If confirm = 6 Then

    ' Write data to worksheet

    RowCount = Worksheets("Database").Range("A1").CurrentRegion.Rows.Count

    With Worksheets("Database").Range("A1")
        .Offset(RowCount, 0).Value = "P1088 (Bris)"
        .Offset(RowCount, 1).Value = "80192D"
        .Offset(RowCount, 2).Value = Me.txtWBS.Value
        .Offset(RowCount, 3).FormulaR1C1 = "=IF(R[11]C[2]="""",0,SUM((R[11]C[1]*R[11]C[2])+R[11]C[3]))"
        .Offset(RowCount, 4).Value = Me.txtDoj.Value
        .Offset(RowCount, 5).Value = Me.txtAT.Value
        .Offset(RowCount, 3).FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R[3]C[13],BACKGROUND!C[4]:C[7],4,FALSE)=TRUE),"""",VLOOKUP(R[3]C[13],BACKGROUND!C[4]:C[7],4,FALSE))"
        .Offset(RowCount, 7).Value = Me.txtSC.Value
        .Offset(RowCount, 8).Value = Me.txtJobdesc.Value
        .Offset(RowCount, 9).Value = Me.cmbAct.Value
        .Offset(RowCount, 10).Value = Me.txtWk.Value
        .Offset(RowCount, 11).Value = Me.cmbPN.Value
        .Offset(RowCount, 12).Value = Me.cmbFac.Value
        .Offset(RowCount, 13).Value = Me.txtBT.Value
        .Offset(RowCount, 14).Value = Me.txtPC.Value
        .Offset(RowCount, 15).Value = Me.txtStock.Value
        .Offset(RowCount, 16).Value = Me.txtMC.Value
    End With
    ' Clear form after submitting...

Can you attach the a small file with some dummy data & the formulas in?

Looking at the Database sheet I can see Column D has a formula, but not C or F

Can you attach a corrected workbook with the formulas in the correct column

This should put the correct formula into the correct Column. You need to check that the other inputs are correct

Code:

With Worksheets("Database").Range("A1")
            .Offset(RowCount, 0).Value = "P1088 (Bris)"
            .Offset(RowCount, 1).Value = "80192D"
            .Offset(RowCount, 2).Value = Me.txtWBS.Value
            .Offset(RowCount, 4).FormulaR1C1 = "=IF(RC[3]="""",0,SUM((RC[2]*RC[3])+RC[4]))"
            .Offset(RowCount, 4).Value = Me.txtDoj.Value
            .Offset(RowCount, 5).Value = Me.txtAT.Value
            .Offset(RowCount, 6).FormulaR1C1 = _
                "=IF(ISERROR(VLOOKUP(RC[10],BACKGROUND!C[3]:C[6],4,FALSE)=TRUE),"""",VLOOKUP(RC[10],BACKGROUND!C[3]:C[6],4,FALSE))"
            .Offset(RowCount, 7).Value = Me.txtSC.Value
            .Offset(RowCount, 8).Value = Me.txtMC.Value
            .Offset(RowCount, 9).Value = Me.cmbAct.Value
            .Offset(RowCount, 10).Value = Me.txtWk.Value
            .Offset(RowCount, 11).Value = Me.cmbPN.Value
            .Offset(RowCount, 12).Value = Me.cmbFac.Value
            .Offset(RowCount, 13).Value = Me.txtBT.Value
            .Offset(RowCount, 14).Value = Me.txtPC.Value
            .Offset(RowCount, 15).Value = Me.txtStock.Value
            .Offset(RowCount, 16).Value = Me.txtJobdesc.Value
        End With