CODEWITHSUNDEEP
vbaexceluserformcalculated-field
Bethany Minor
Bethany Minor

Reputation: 57

Returning an Answer using VBA in a TextBox

I am creating a fairly simple userform. The user has to enter 2 values that I want to calculate and enter into a column on my spreadsheet in Excel.

I am not sure how to format the VBA to have it calculate these values and then save it in the spreadsheet.

Here is what I have:

Private Sub txtRecruitPct_Change()
    Dim A As Integer
    Dim B As Integer

    A = txtApprovedAffs.Value
    B = txtEmailsSent.Value

    Answer = A / B    
End Sub

I need to know how to get this value to calculate and save in the specific column in Excel.

Upvotes: 0

Views: 697

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

If you were going to dump all your answers into column "A" then:

Private Sub txtRecruitPct_Change()
    Dim A As Integer
    Dim B As Integer
    Dim LastRow As Long

    A = txtApprovedAffs.Value
    B = txtEmailsSent.Value

    Answer = A / B
    LastRow = Range("G" & Rows.Count).End(xlUp).Row
    Range("G" & LastRow + 1).Value = Answer

End Sub

If you wanted all the data in seperate columns:

Private Sub txtRecruitPct_Change()
    Dim A As Integer
    Dim B As Integer
    Dim LastRow As Long

    A = txtApprovedAffs.Value
    B = txtEmailsSent.Value

    Answer = A / B
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LastRow + 1).Value = A
    Range("B" & LastRow + 1).Value = B
    Range("C" & LastRow + 1).Value = Answer

End Sub

Upvotes: 1

Related Questions