Calculate & Store using Excel 2007 vba-VBForums
Results 1 to 10 of 10

Thread: Calculate & Store using Excel 2007 vba

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Arrow Calculate & Store using Excel 2007 vba

    I am trying to find the percentage of the value from cell (8,4) and store the result in cell (9,4). I am getting "Runtime Error 424" Object required.
    Here is my code.

    Sub SubmitDP()

    Dim r1, r2, r3 As Integer

    Set r1 = thisworksheet.Sheets("Sheet1").Cells(8, 4).Value
    Set r2 = thisworksheet.Sheets("Sheet1").Cells(9, 4).Value

    r2.Value = r1.Value * 0.15

    End Sub

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Calculate & Store using Excel 2007 vba

    Moved From The CodeBank (which is for sharing code rather than asking questions )
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3
    New Member
    Join Date
    Jul 2011
    Posts
    9

    Re: Calculate & Store using Excel 2007 vba

    There are many problems with your code.
    Lets go through it line by line.
    Code:
    Dim r1, r2, r3 As Integer
    Here you are telling Excel that you are going to be using r1, r2, and r3 as Variables which will hold integers. These cannot be used as references to a cell. They hold numbers directly. Also Integers are only whole numbers and their negatives. Your result is not an integer it is a Double, meaning a number with decimals. you should have this.
    Code:
    Dim r1 As Integer
    Dim r2 as Double
    you do not use r3 here so I left it out.
    Code:
    Set r1 = thisworksheet.Sheets("Sheet1").Cells(8, 4).Value
    Here you are saying to put the "value" that is in Cell(8,4) into your variable called r1. There are 2 problems with how you wrote this.
    1. You cannot use the Set command with a primitive data type like like an integer or a double. it can only be used with Objects.
    2. it should be ThisWorkbook not thisworksheet
    so it should look like this
    Code:
    r1 = ThisWorkbook.Sheets("Sheet1").Cells(8, 4).Value
    The next line is completely useless.
    Code:
    Set r2 = thisworksheet.Sheets("Sheet1").Cells(9, 4).Value
    Just like the line above it, this puts the "value" from cell(9,4) into r2. since we will be changing the value of r2 in the next line, there is no point in setting it here.
    I believe you wanted to make it so that anything put into r2 would show up in cell(9,2) but this line does not do that.
    Code:
    r2.Value = r1.Value * 0.15
    Here you want to make r2 equal to 15% of r1.
    variables do not have a value atribute. They simply represent a number directly. It should look like this.
    Code:
    r2 = r1 * 0.15
    Finally you need to make Cell(9,4) show your result.
    Code:
    ThisWorkbook.Sheets("Sheet1").Cells(9, 4).Value = r2
    So putting it all together it should look like this.
    Code:
    Sub SubmitDP()
    
    Dim r1 As Integer
    Dim r2 as Double
    r1 = ThisWorkbook.Sheets("Sheet1").Cells(8, 4).Value
    r2 = r1 * 0.15
    ThisWorkbook.Sheets("Sheet1").Cells(9, 4).Value = r2
    
    End Sub
    This is not the best way to solve this problem. It works but I Think you intended to do something more like this.
    Code:
    Sub SubmitDP
    Dim r1, r2 As Range
    Set r1 = ThisWorkbook.Sheets("Sheet1").Range("H4")
    Set r2 = ThisWorkbook.Sheets("Sheet1").Range("I4")
    r2.Value = r1.Value * 0.15
    End Sub
    Here instead of making r1 and r2 numbers, I am making them range objects.
    Next I can use the Set command so they become references to the ranges themselves.
    Finally I can use the .Value atribute of the Ranges to set the values appropriately.

    What a great way to kill my last hour of work.
    Last edited by Merton; Feb 14th, 2012 at 06:31 PM.

  4. #4
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Calculate & Store using Excel 2007 vba

    why can you not just put
    Code:
    = h4*0.15
    in the cell i4 and be done with it

    this programming is an awful waste concidering what spreadsheets are supposed to be for

    in my humble opinion ( of course )

  5. #5
    New Member
    Join Date
    Jul 2011
    Posts
    9

    Re: Calculate & Store using Excel 2007 vba

    I assume it's part of a larger program. Or I did his homework for him.

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Re: Calculate & Store using Excel 2007 vba

    Thank you very much, it works great. Your assumption is right, it is a part of a bigger program and absolutely not HW. I am helping one of my friend, and I am just exploring vba myself. You are a great help, and your help is not wasted for sure.

    BTW, any VBA book/site recommendation?

    Thanks again

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Re: Calculate & Store using Excel 2007 vba

    Quote Originally Posted by incidentals View Post
    why can you not just put
    Code:
    = h4*0.15
    in the cell i4 and be done with it

    this programming is an awful waste concidering what spreadsheets are supposed to be for

    in my humble opinion ( of course )
    Sure, your opinion is right if I were to use just an excel for myself. What about I am calculating the customer input value?

  8. #8
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Calculate & Store using Excel 2007 vba

    it makes no odds the cell formula works because it is a cell formula no matter who is using it!

    the idea that all solutions need to be written in vba vb or any other language when the outer workings of the sheet can do the task is a complete waste of time and money on the part of the individual who paid through the nose for the spreadsheet system in the first place!

    here to help and talk

  9. #9
    New Member
    Join Date
    Jul 2011
    Posts
    9

    Re: Calculate & Store using Excel 2007 vba

    Of course You are right about the actual work being done in this question.
    I (admittedly reading between the lines) took this as coming from someone looking to learn VBA, not really how to perform a simple multiplication.
    it was a question of how to address a cell properly and how to use a variable properly.
    it's value is as a learning tool not as a way to solve a particular problem.
    This is just the next step after
    Code:
    Sub helloworld
    Cells(1, 1) = "Hello World"
    End Sub

  10. #10
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Calculate & Store using Excel 2007 vba

    @jkashi

    are you done - what did you do?

    where we any help?

    please close the thread if done and rate the posts that helped using the tools on the top bar

    we only do this for the gratitude!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.