-
Feb 13th, 2012, 02:44 PM
#1
Thread Starter
New Member
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
-
Feb 13th, 2012, 02:47 PM
#2
Re: Calculate & Store using Excel 2007 vba
Moved From The CodeBank (which is for sharing code rather than asking questions )
-
Feb 14th, 2012, 03:58 PM
#3
New Member
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.
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.
-
Feb 14th, 2012, 05:19 PM
#4
Frenzied Member
Re: Calculate & Store using Excel 2007 vba
why can you not just put 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 )
-
Feb 14th, 2012, 06:24 PM
#5
New Member
Re: Calculate & Store using Excel 2007 vba
I assume it's part of a larger program. Or I did his homework for him.
-
Feb 15th, 2012, 12:46 PM
#6
Thread Starter
New Member
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
-
Feb 15th, 2012, 12:48 PM
#7
Thread Starter
New Member
Re: Calculate & Store using Excel 2007 vba
Originally Posted by incidentals
why can you not just put 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?
-
Feb 15th, 2012, 05:33 PM
#8
Frenzied Member
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
-
Feb 16th, 2012, 10:34 AM
#9
New Member
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
-
Feb 18th, 2012, 05:50 AM
#10
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|