none
HELP please with VBA problem

    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 06, 2013 6:09 AM

All replies

  • Questions related to VBA should be posted here:

    Visual Basic for Applications  (VBA)

    Be sure to include the details of the error message you get when you try to compile your code.

    • Proposed as answer by Armin Zingler Sunday, October 06, 2013 3:50 PM
    Sunday, October 06, 2013 6:26 AM
  • it says compile error: object required
    Sunday, October 06, 2013 6:41 AM
  • What variable is the error pointing too? Slope?

    Is slope an object function?

    Myrange is an object and you are setting it to slope which you declare as integer.

    http://www.excelforum.com/excel-programming-vba-macros/346174-call-slope-function-from-vba.html

    Sunday, October 06, 2013 1:07 PM
  • I'm not sure you are using the input box function correctly. It, as far as I know, returns a string. Not an integer. You can look at the visual basic language reference for VBA also to see if what you are trying to do is accurate or not. And I don't know if you're using the range object correctly or not although it seems to handle strings in its useage.

    Please BEWARE that I have NO EXPERIENCE and NO EXPERTISE and probably onset of DEMENTIA which may affect my answers! Also, I've been told by an expert, that when you post an image it clutters up the thread and mysteriously, over time, the link to the image will somehow become "unstable" or something to that effect. :) I can only surmise that is due to Global Warming of the threads.

    Sunday, October 06, 2013 3:43 PM
  • Hi,

    Since this thread is more related to Excel development, I moved this thread to Excel for Developers forum.

    Thanks for your understanding.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 07, 2013 3:18 AM
  • Couple of things.

    A) Change

    Dim myrange As Ranges

    to

    Dim myrange As Range

    B) Since you are accepting Integers so simply remove the word "Set" from

    Set slope = Application.InputBox(Prompt:= _
        "Please enter a slope value (Ex. 2,1,-1)", _
            Title:="InputBox Method", Type:=1)

    Make it

    slope = Application.InputBox(Prompt:= _
            "Please enter a slope value (Ex. 2,1,-1)", _
            Title:="InputBox Method", Type:=1)
    I am not sure what are you trying to achieve by "myrange.Range = slope" If you are simply trying to assign values to the range then try this

    myrange.Value = Slope


    Sid

    Monday, October 07, 2013 5:28 AM
    Moderator
  • Thank you for the help. I actually figured out the mistakes but thank you never the less. But know I am having some more difficulty with some hard lines which i do not know how to even start. so my current code looks like this.

    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

    BUt now 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".

    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.


    • Edited by cantprogram Monday, October 07, 2013 8:35 AM
    Monday, October 07, 2013 5:48 AM
  • What is "Lcall", "Lput", "Scall" or "Sput"? Where have you defined it?

    Sid

    Monday, October 07, 2013 6:49 AM
    Moderator
  • I have not defined them yet but they would be just a sting so maybe just

    Dim LCall As Sting

    And same for the rest.

    I would like to get an output like this.

    Message box 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.


    • Edited by cantprogram Monday, October 07, 2013 8:39 AM
    Monday, October 07, 2013 8:32 AM