For Each Statement

jdoiron

New Member
Joined
Aug 19, 2002
Messages
16
I am using this "for each" statement to calculate a value in a new column. It works on some test data that I created in a new sheet but does not work in the intended application. I am pulling data from the first and fourth rows back from column O and multiplying them and then dividing the product by 100. This calculation should populate column O with data if there are values in the pull columns.

Dim Rng As Range
Dim c As Range
Set Rng = Range("O2:O" & Range("A65536").End(xlUp).Row)
For Each c In Rng
c.Value = c.Offset(0, -1).Value * c.Offset(0, -4).Value / 100
Next c

the formatting did not hold due to the window size but you can get the idea of the formula. Any ideas as to why this does not work?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Howdy.

What goes wrong exactly? The code seems to work with no problem on some sample data in a test file. Can you give us any more detail?
 

jdoiron

New Member
Joined
Aug 19, 2002
Messages
16
The debuuger refers me to the line

c.value = c.offset(0,-1) etc etc

I tried seperating the code into its own sub and calling it from the previous sub but that did not work. I got it to work on some test data as well but it will not work where I want it to.

The only thing that I can think of is that the columns I am pulling the data from is being copied and pasted into the active sheet and then the calculation is being made. I could try to perform the calculation in the original sheet and then copy it into the new one but I would prefer to do it in the new sheet if possible.

The debugger is giving me a "run time error 13" type mismatch error
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Your formula may be trying to multiply a test string from either column N or K (-1 and -4 columns from column O). Here's a modified version (if that's whats going on):


Code:
Set Rng = Range("O2:O" & Range("A65536").End(xlUp).Row)
For Each c In Rng

    If IsNumeric(c.Offset(0, -1).Value) = True And IsNumeric(c.Offset(0, -4).Value) = True Then
        c.Value = c.Offset(0, -1).Value * c.Offset(0, -4).Value / 100
    Else
        c.Value = 0
    End If

Next c
 

Forum statistics

Threads
1,144,052
Messages
5,722,249
Members
422,418
Latest member
Chipsy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top