0

This may be a pretty easy piece of code for experimented VBA developers/users, but im new programming an Im stuck on this task for a couple of days already :( . All I need is to apply a formula to a user defined range either as a whole (if possible) or looping through it with a "for-each next" or "for next" and I just get mistakes for every attempt Ive tried for this. Can anyone help me out with this issue please....thxs very much in advance

Lets say that the formula is something easy as F =m*a, being "m" my selected range

Here the code to select the ranges:

Option Base 1


    Sub KVmodel()

    'Select the data points from the excel spreadsheet

    Dim A, B As Range
    Set A= Application.InputBox("Select A Range", "Select a range", Type:=8)
    Set B= Application.InputBox("Select B Range", "Select a range", Type:=8)


    'Verify the lenght of the selected data
    If A.Count = B.Count Then
    MsgBox "Do you want to run the KV Model Adjustment?", vbYesNo


    'Calculates F according to the values of the model

    -
    -
    -


    Else
    MsgBox "The range sizes are different, please re-select the input data"

    End If

    End Sub
Community
  • 1
  • 1
Migel Varga
  • 1
  • 2
  • Do you want to insert a formula into each cell or insert a the result of a formula calculated in VBA? Your given example for the formula, you say what m is, what about a? also is m the entire range? or a cell inside it, same for a? – NickSlash Mar 12 '13 at 22:27
  • Oh sorry for not being more precise, "a" would mean an scalar and the whole idea would be to generate another range or array as the result of the formula applied to the selected range. PS: thxs for your prompt answer ;) – Migel Varga Mar 12 '13 at 22:35
  • So you have 3 ranges? A, B and M (your selected) and you want to do something with A and B and put the result in M? Or you have 2 ranges, A and B, do something to A and put the result in B? – NickSlash Mar 12 '13 at 22:54
  • I have the range A or B (the selected one) and I should generate a third one, F, as result of the formula. – Migel Varga Mar 12 '13 at 23:06
  • I tried to simplify the problem using F=m*a instead of the real case, and it seems that I just made it harder to understand :/ All apologies. – Migel Varga Mar 12 '13 at 23:10
  • Your code example gets 2 ranges (A and B, not or) if you want a third range, you would also need to get that from the user unless as it would have nowhere to go. Could you update your question to have a little more detail? – NickSlash Mar 12 '13 at 23:13

1 Answers1

0

Try below code :

Option Base 1


Sub KVmodel()

    On Error Resume Next

    'Select the data points from the excel spreadsheet

    Dim A As Range, B As Range
    Set A = Application.InputBox("Select A Range", "Select a range", Type:=8)
    Set B = Application.InputBox("Select B Range", "Select a range", Type:=8)

    Dim userDefinedRng As Range ' assumption
    Set userDefinedRng = Range("A1:A10")

    On Error GoTo 0

    If Not A Is Nothing And Not B Is Nothing Then

        'Verify the lenght of the selected data
        If A.Count = B.Count Then
            retVal = MsgBox("Do you want to run the KV Model Adjustment?", vbYesNo)

            If retVal = vbYes Then
                'Calculates F according to the values of the model
                userDefinedRng.FormulaR1C1 = "= 1 * 2" ' here it applies forumla to whole userdefined range
                MsgBox "yes"
            Else
                MsgBox "no"
            End If

        Else
            MsgBox "The range sizes are different, please re-select the input data"

        End If
    End If

    Exit Sub

End Sub
  • Thxs for your help 2063626. At least it runs without errors, but all i got is a fixed unidimensional vector of numbers 2 from A1 till A10. The location isnt a problem, its easy to modify, the problem is the value: Ive tried diff. inputs within the If retVal and also different selected ranges and it keeps giving me a unidim. vector of 2 no matter what i choose :( thxs anyway. – Migel Varga Mar 13 '13 at 11:46