Add the values in 3 cells, subtract the value in a fourth and put the result and the fifth cell

[CathalB]

Hi Everyone,
Hope you're all in good form.

I am stuck on this. I have a worksheet containing lots of columns 300+ and data.
I am transforming this into a new worksheet with the columns re-arranged and some renamed etc.
The VB code I have so far has worked to complete these tasks OK.

However, now I need to do the above i.e. Add the values in 3 cells, subtract the value in a fourth and put the result and the fifth cell.
This is what my current code looks like - which is obviously not working

Sub CalcTotalMisusRisk()

Dim i As Long
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Sheet2").Range("DD" & i) = ("CZ2" & i & ",DA2" & i & ",DB2" & i) - (",DC2" & i)
Next i​

End Sub

The values in cells CZ2, DA2 and DB2 are just numbers and row 1 contains the headers hence <for i="2" to="" cells="" etc="">
but VB is kicking up the following error:
"Runtime error '13'
Type mismatch

Apologies if I haven't articulated the problem very well - I'm (as you can see) not a coder.</for>

 

[Drrellik]

Worksheets("Sheet2").Range("DD" & i).Value = Range("CZ2" & i).Value + Range("DA2" & i).Value + Range("DB2" & i).Value - Range("DC2" & i).Value

 

[tdh777]

Hi CathalB.

Based on what you have described, you need something like :

[COLOR=#333333]Worksheets("Sheet2").Range("DD" & i).Value = Range("CZ" & i).Value + Range("DA" & i).Value  + Range("DB" & i).Value - Range("DC" & i).Value[/COLOR]

But seeing as you are putting this new value in the column after the 4 cells in question, why use VBA at all ?

You could just put a formula in Cell DD2

=CZ2+DA2+DB2-DC2

..and double-click in the bottom right corner of Cell DD2 to populate the formula down the column (or you could drag it down yourself).

Seems like you might be using a sledgehammer to crack a walnut !

Cheers,
Warren K.

 

[Drrellik]

Worksheets("Sheet2").Range("DD" & i).Value = Range("CZ2" & i).Value + Range("DA2" & i).Value + Range("DB2" & i).Value - Range("DC2" & i).Value

Still did not work correctly for me hope you are a little closer.

 

[tdh777]

Still did not work correctly for me hope you are a little closer.

You left his extra "2"s on the end of his cell location references.

Please see my version from 10:31 am that fixes the problem.

 

[CathalB]

Hi CathalB.

Based on what you have described, you need something like :

[COLOR=#333333]Worksheets("Sheet2").Range("DD" & i).Value = Range("CZ" & i).Value + Range("DA" & i).Value  + Range("DB" & i).Value - Range("DC" & i).Value[/COLOR]

But seeing as you are putting this new value in the column after the 4 cells in question, why use VBA at all ?

You could just put a formula in Cell DD2

=CZ2+DA2+DB2-DC2

..and double-click in the bottom right corner of Cell DD2 to populate the formula down the column (or you could drag it down yourself).

Seems like you might be using a sledgehammer to crack a walnut !

Cheers,
Warren K.

Hi Warren,

Thanks so much for helping me out, I appreciate it.
I admit it does seem like a lot just to do a little. However, I am preparing for a phase of a national project where I'm going to be sent an awful lot of data from all over the country (I'm in Ireland by the way!) and I need to automate as much of the work to transform these excel files into the format and structure I need to before importing them into SPSS and analyse them.
Hence, this small piece of code is part of a module of code I'm building up in the VB editor to perform the transformations.

Had I known how much I would be relying on Excel and VB I'd have done a course beforehand!

Thanks again!

 

[CathalB]

Hi Drrellik and WarrenK,

Between both of you my issue is now sorted and the code is doing exactly what I need.
Thanks so much for taking the time to help me out, I really appreciate it.

Have a great day,
Charles.

 

[Drrellik]

Thanks CathalB and thanks WarrenK I missed the "2"

Thanks again for the feedback