none
need help with simple code and object required error RRS feed

  • Question

  • Cant seem to compile this simple code. I am trying to learn how to use VBA and have no programing experience. Please explain what is wrong with the code. Thank you.

    I am trying to take a input (integer value) and trying to set a series of cells (range) in that column to the value. Also i am trying to take multiple ranges and values at the same time. My code is below. Please if someone can fix it or even write a nicer way to do it, i would be grateful.

    Sub Ask()
        Dim myrange As Ranges
        Dim slope As Integer
        
        Set myrange = Application.InputBox(Prompt:= _
            "Please input a Range (Ex. B2:B52, B53:B125)", _
            Title:="InputBox Method", Type:=8)
       
        Set slope = Application.InputBox(Prompt:= _
        "Please enter a slope value (Ex. 2,1,-1)", _
            Title:="InputBox Method", Type:=1)
            myrange.Range = slope
    End Sub

    The compile errors: object required, is the errors i get when i try to compile. And also i am running 2007.
    Sunday, October 6, 2013 6:48 AM

All replies

  • Your "object required" error is caused by the line:

    Set slope = Application.InputBox(Prompt:= _ ......

    With an Integer variable you do not need the word "Set".  So it should read:

    slope = Application.InputBox(Prompt:= _ ......

    Also, I think that you meant to have:

    Dim myrange As Range     rather than     Dim ..... Ranges

    Your penultimate line should (if I understand your requirement correctly) read:

    myRange = Slope

    So a slightly simplified version of your code would be:

    Sub Ask()

        Dim myRange As Range
        Dim Slope As Integer
        
            Set myRange = Application.InputBox("Please input Range", Type:=8)
            Slope = Application.InputBox("lease enter a slope value.", Type:=1)

        myRange = Slope
       
    End Sub

    Hope this helps.

    Andy C

    Sunday, October 6, 2013 9:47 AM
  • Thank YOu so much. IT compiles now!!!. I actually do have one questions though. YOur corrected code only can take in one input for slope. IF I put in the range B2:B50, B51:B100 and input the slope value to lets say 1, 2. Meaning i want the cells of the ifrst range to be 1 and the cells of the second range to be 2. the program says invalid number. I know slope is an integer but is there an way i can make it so that it can take an input like 1, 2 and set it for 2 ranges at the same time??
    Sunday, October 6, 2013 5:58 PM
  • ok so this is my newest code

    Sub Ask()
        Dim myrange As Range
        Dim slope As Integer
        Dim msg As Integer
       
       Do
        Set myrange = Application.InputBox(Prompt:= _
            "Please input a Range for B2 up to B202 (Ex. B2:B52)", _
            Title:="InputBox Method", Type:=8)
       
        slope = Application.InputBox(Prompt:= _
        "Please enter a slope value (Ex. 2,1,-1)", _
            Title:="InputBox Method", Type:=1)
            
            myrange = slope
           
        msg = Application.InputBox(Prompt:= _
        "Would you like to set another range? 1 for Yes 2 for No", _
            Title:="InputBox Method", Type:=1)
             
        If msg = 2 Then Exit Do
        msg = 1
        Loop
        
    End Sub

    IT gets the job done for the ranges and the basics of what i have to do but now i want to do the hard part. I want to program to give me an output of either "Lcall", "Lput", "Scall" or "Sput" everytime the slope changes. For example if the slope goes from 0 to positive integer output "LCall".

    slope: 0=>positive integer output "Lcall"

    slope: 0=>negative integer output "LPut"

    slope: positive to negative output "Lcall and LPut"

    THe program should be able to do this for all total ranges of the input.


    • Edited by cantprogram Sunday, October 6, 2013 8:24 PM
    Sunday, October 6, 2013 8:23 PM
  • It might be possible to deal with several different ranges from the myRange input box using the Application.Union method, but I cannot see an easy way of getting the input box dealing with Slope to deal with two integers.  So you could perhaps get one slope to apply to several ranges.

    You might possibly achieve what you want by treating the Slope input as a string, then changing the individual parts to integers using the CInt Function.  (Remember that if you want a decimal numbers for your slope you will need to declare it as a "Single" and use CSng.)

    However, my experience with VBA is limited, and one of the MVPs might come up with a method of achieving what you want.

    Sunday, October 6, 2013 8:41 PM
  • There are a few points to consider with respect to your "new code".

    First of all, error trapping.  Decide what you want to do if the user changes his mind and clicks Cancel on your Input boxes.  Presumably it will be to exit, but you have to say so in the code.  Deal too with the case where the user does not enter a recognisable range or puts in "Tuesday" for a slope.

    Second, you have used an input box to get a yes / no answer about setting another range, and given an option for the user to input 1 or 2.  A simple Message box with yes / no options (or possibly yes / no / cancel if required) would probably be more appropriate, followed by a Select Case statement.

    When you say that you want the program to give an output of Lcall etc, decide where you want this output to go.  Is it to be put into a cell on the worksheet, or output as a message box or sent elsewhere?  Dealing with the individual cases could be simply a matter of flogging through the options with an If ...Then ... Else If statement and directing the output as appropriate.

    Monday, October 7, 2013 9:58 AM
  • Here is a variation that will break up the Slope values based on comma separated list.

    Sub Ask()
        Dim myrange As Range
        Dim rngArea As Range
        Dim strSlopeValues As String
        Dim vntValues As Variant
        Dim lngIndex As Long
        
        Set myrange = Application.InputBox(Prompt:= _
            "Please input a Range (Ex. B2:B52, B53:B125)", _
            Title:="InputBox Method", Type:=8)
       
        strSlopeValues = Application.InputBox(Prompt:= _
        "Please enter slope value(s) (Ex. 2,1,-1)", _
            Title:="InputBox Method", Type:=2)
            
        vntValues = Split(strSlopeValues, ",")
        lngIndex = 0
        For Each rngArea In myrange.Areas
            If lngIndex <= UBound(vntValues) Then
                rngArea = CDbl(vntValues(lngIndex))
            End If
            lngIndex = lngIndex + 1
        Next
        
    End Sub
    


    Cheers,

    Andy
    www.andypope.info

    Monday, October 7, 2013 1:57 PM
  • I would like the output to be in a message box but if it is easier to do in a cell then that is fine. Unfortunately I dont know how to start this part.
    Monday, October 7, 2013 5:14 PM
  • What output exactly do you want to see in a message box?

    Cheers,

    Andy
    www.andypope.info

    Tuesday, October 8, 2013 7:33 AM
  • I want the program to give me an output of either "Lcall", "Lput", "Scall" or "Sput" everytime the slope changes. For example if the slope goes from 0 to positive integer output "LCall" where is just a sting. 

    slope: 0=>positive integer output "Lcall"

    slope: 0=>negative integer output "SCall"

    slope: positve=>0 integer output "SPut"

    slope: negative=>0 integer output "LPut"

    slope: positive to negative output "Lcall and LPut"

    THe program should be able to do this for all total ranges of the input.

    Perhaps in a Message box that says Lcall, SPut, LCall, ...... however many needed.

    Ex. so if the ranges were B2:B52, B53:B153, B154:B200

    And the slopes were 0,1,0 respectively

    I would like the program to output the message  "LCall, SPut"

    Since 0=>1 is a 0 to positive change (LCall) and then 1=> 0 is positive to 0 change which is what i wanna call SPut.

    So basically it is looking at the 2 adjacent slopes and determining what to categorize it as. I hope this was clear.
    Tuesday, October 8, 2013 11:41 AM
  • not sure what part the actually ranges play but this tests adjacent slope values.

    You may need to adjust the tests to get the exact descriptions you want

        strMsg = ""
        For lngIndex = LBound(vntValues) To UBound(vntValues) - 1
            If CLng(vntValues(lngIndex)) = 0 Then
                If CLng(vntValues(lngIndex)) >= 0 Then
                    strMsg = strMsg & " LCall"
                Else
                    strMsg = strMsg & " SCall"
                End If
            ElseIf CLng(vntValues(lngIndex)) > 0 Then
                If CLng(vntValues(lngIndex)) >= 0 Then
                    strMsg = strMsg & " SPut"
                Else
                    strMsg = strMsg & " LCall and LPut"
                End If
            ElseIf CLng(vntValues(lngIndex)) < 0 Then
                If CLng(vntValues(lngIndex)) >= 0 Then
                    strMsg = strMsg & " LPut"
                End If
            End If
        Next
        
        MsgBox strMsg
    


    Cheers,

    Andy
    www.andypope.info

    Tuesday, October 8, 2013 12:32 PM
  • Thank you very much. This is greatly appreciated. I am just learning how to program so I dont fully understand the loops but I will try to make sense of the logic and syntax. Thanks again.
    Tuesday, October 8, 2013 4:27 PM