none
VBA code for adding range f cells and dividing the answers RRS feed

  • Question

  • Hello,

    So i have a worksheet and i want to add specific range of values and divide the results and display it on my userform in textbox13. If there are no values then the text box should display "not applicable/not started"

    On the excel sheet the formula i have that works is =IF(ISERROR(SUM(Accuracy!H2:H8)/SUM(Accuracy!F2:F8)),"Not Applicable / Not Started",SUM(Accuracy!H2:H8)/SUM(Accuracy!F2:F8))

    I tried to take that and recreate it in VBA but i have runtime overflow error message. Please help me.

    The code i have currently under initialize is TextBox13.Value = WorksheetFunction.Sum(Worksheets("Accuracy").Range("H2.H8")) / WorksheetFunction.Sum(Worksheets("Accuracy").Range("F2.F8"))

    Monday, August 8, 2016 5:05 PM

Answers

  •     If WorksheetFunction.Sum(Worksheets("Accuracy").Range("F2.F8")) <> 0 Then
            TextBox13.Value = WorksheetFunction.Sum(Worksheets("Accuracy").Range("H2.H8")) / WorksheetFunction.Sum(Worksheets("Accuracy").Range("F2.F8"))
        Else
            TextBox13.Value = "Not Applicable / Not Started"
        End If

    • Marked as answer by teecute Tuesday, August 9, 2016 5:39 PM
    Monday, August 8, 2016 5:45 PM
  • IF the dropdown list is a combobox named ComboBox1 and the textbox is TextBox1, then you could use


    Private Sub ComboBox1_Change()
        Me.TextBox1.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("Team").Range("A:B"), 2, False)
    End Sub

    If the list can vary, you may want to have a dynamic data source for the combobox:

    Private Sub UserForm_Initialize()
        With Worksheets("Team")
            Me.ComboBox1.List = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
        End With
    End Sub


    Wednesday, August 10, 2016 7:13 PM

All replies

  •     If WorksheetFunction.Sum(Worksheets("Accuracy").Range("F2.F8")) <> 0 Then
            TextBox13.Value = WorksheetFunction.Sum(Worksheets("Accuracy").Range("H2.H8")) / WorksheetFunction.Sum(Worksheets("Accuracy").Range("F2.F8"))
        Else
            TextBox13.Value = "Not Applicable / Not Started"
        End If

    • Marked as answer by teecute Tuesday, August 9, 2016 5:39 PM
    Monday, August 8, 2016 5:45 PM
  • Thank you so much it works. i appreciate
    Monday, August 8, 2016 8:39 PM
  • Great - happy to hear that it works.  If you could, mark my reply as the answer so that others will know that you no longer need help - thanks.

    Monday, August 8, 2016 9:00 PM
  • Bernie,

    Thanks for the solution. Please can you help me with one more thing.

    I will like to save the file to multiple excel sheets based on the choice of user department because this sheet will be sent out to the different managers and they want the the data meant for a particular department to be seen by the department head only.

    This is what i have: 

    Private Sub CommandButton3_Click()
    Dim LastRow As Long, ws As Worksheet

        Set ws = Sheets("Test")

        LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

        ws.Range("A" & LastRow).Value = ComboBox1.Text 'Adds the ComboBox1 into Col A & Last Blank Row
        ws.Range("B" & LastRow).Value = TextBox1.Text  'Adds the TextBox3 into Col B & Last Blank Row
        ws.Range("C" & LastRow).Value = TextBox2.Text

    .....

    Where should i place the if statement to handle that. There is a combo box with the different department (combo box 3)

    Please help me.

    Tuesday, August 9, 2016 5:59 PM
  • How many sheets do you have? How do you know which sheets go to which departments? Or are you changing what is on the sheet "Test" based on formulas in the sheet? If you post an example of what you sheet looks like, and how you would determine what information goes where, that would be helpful.
    Tuesday, August 9, 2016 6:03 PM
  • So sorry but the user scrapped the requirement.

    But i need to know how to code this

    1. I have a custom user form in Excel VBA.
    2. The form contains a dropdown list (Team Member Name) and textbox (corresponding Team Lead Name).
    3. For each team member name there is an associated team lead name.
    4. The team member name and team lead name will always be of the same row. On the excel workbook called 'teams'  team member name is Column A and team lead name is Column B
    5. The dropdown list is populated from SourceRow.

    Please kindly help me with a code so that when a team member is chosen from combobox1 the team leads name is displayed in the textbox1. Based on the association on the excel sheet.

    Wednesday, August 10, 2016 6:59 PM
  • IF the dropdown list is a combobox named ComboBox1 and the textbox is TextBox1, then you could use


    Private Sub ComboBox1_Change()
        Me.TextBox1.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("Team").Range("A:B"), 2, False)
    End Sub

    If the list can vary, you may want to have a dynamic data source for the combobox:

    Private Sub UserForm_Initialize()
        With Worksheets("Team")
            Me.ComboBox1.List = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
        End With
    End Sub


    Wednesday, August 10, 2016 7:13 PM
  • Thanks a lot. About wrapping this project. You have been a huge help
    Wednesday, August 10, 2016 8:28 PM
  • Great - glad to hear that things worked out for you.

    Bernie

    Wednesday, August 10, 2016 9:00 PM
  • Bernie,

     So i used Peter Thornton ScrollWheel API and it works with my combo boxes and form. The problem i am facing right now is that i have a textbox for comments and it is usually very long so and i activated the verticalscroll property which allows users to click on scroll bar to scroll .

    I will like them to be able to use their mouse wheel scroll too; so I wrote a code for the textbox like the rest (combobox and form) on the userform but the mousescroll wheel doesn't work on that textbox.

    See code below:

    Private Sub TextBox11_MouseMove( _
                            ByVal Button As Integer, ByVal Shift As Integer, _
                            ByVal X As Single, ByVal Y As Single)
                    HookListBoxScroll Me, Me.TextBox11
    End Sub

    

    Please any suggestions? 

    Thursday, August 11, 2016 3:29 PM
  • You should start a new thread for this one.....
    Thursday, August 11, 2016 4:21 PM
  • okay i will
    Thursday, August 11, 2016 4:23 PM