Calculate & Store using Excel 2007 vba

[jkashi]

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

[Hack]

Moved From The CodeBank (which is for sharing code rather than asking questions )

[Merton]

There are many problems with your code.
Lets go through it line by line.

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.

Dim r1 As Integer
Dim r2 as Double

you do not use r3 here so I left it out.

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

r1 = ThisWorkbook.Sheets("Sheet1").Cells(8, 4).Value

The next line is completely useless.

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.

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.

r2 = r1 * 0.15

Finally you need to make Cell(9,4) show your result.

ThisWorkbook.Sheets("Sheet1").Cells(9, 4).Value = r2

So putting it all together it should look like this.

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.

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.

[incidentals]

why can you not just put

= 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 )

[Merton]

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

[jkashi]

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

[jkashi]

why can you not just put

= 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?

[incidentals]

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

[Merton]

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

Sub helloworld
Cells(1, 1) = "Hello World"
End Sub

[incidentals]

@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!