trying to calculate an end value

  • good day


    i have written a macro to find the answer to a sum i have for a single line in userforms Excel vba
    but now trying to do the same through a loop i am not getting the textbox values


    below is the code i am using currently that picks up text box names but not the values in the textbox


    Private Sub CalculateGroup()
    Dim WC As String
    Dim W1 As String
    Dim W2 As String
    Dim D As String
    Dim R As String
    Dim T As Double
    Dim W As String
    Dim Q As String
    Dim i As Integer
    Dim J As Integer
    Dim test As Integer



    For i = 1 To 4




    'test = UserForm3.Controls("Txtbox" & i & "A" & J).Value <----- this is the textbox where the answer goes



    WC = "Txtbox" & i & "A" & 2
    W1 = "Txtbox" & i & "A" & 3
    W2 = "Txtbox" & i & "A" & 4


    R = (Val(W1) + Val(WC)) - Val(W2)
    T = Val(R)


    Q = Val(R) / Val(W1)
    W = Val(Q) * 100
    'test.Value = W
    ' test.Text = Format(test, "0.00")



    Next i




    End Sub


    i have tried Val("Txtbox" & i & "A" & 2) but it gives a 0 instead of textbox value


    if anyone could help it would be much appreciated

  • Hello,


    To speed up designing a customized solution ... you should attach a sample file ...


    Most probably CLng() should do the job ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • i have the formula to just get the first lines answer but want to be able to loop through lines so that my code wont be as long.


    if you have any advice it would be much apreciated


    sorry you may have to zoom the userform to 100 i am not sure if i set it back

  • Extremely confusing ... :wink:


    To be tested



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • it runs the same as i had it except it is giving me a type mismatch error once the CDbl() function is used thats wht the Val() function worked the best here,


    all i am getting stuck on is trying to find the Numeric Value of the things so when i F8 through it should read as follows when i hover mice over the codes




    WC = 22.55
    W1 = 100.04
    W2 = 122.63


    instead of


    WC = Txtbox1A2
    W1 = Txtbox1A3
    W2 = Txtbox1A4


    and so on

  • Just a couple of questions :


    1. What is the final objective ? The sum of all the TextBoxes ?


    2. Are all these Values already stored in your sheet ... or not ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • the final objective is to use the calculations to find a value for W which i have shown in my first post with the code, i have messed around a bit and have worked it out thank you for your help

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!