For Each Statement

[jdoiron]

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?

 

[Asala42]

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]

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]

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

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