Help with a subtraction function 1

[Excelerate2004]

I'm wondering if I could have some help in trying to set up this procedure to subtract 2 cells and then divide.

The code is as follows:

Dim rngLastcell As Integer

With Sheets("affy data")
For z = 2 To rngLastcell
'(L2-N2)/K2

Cells(z, "O").Value = .Range("L" & _ z).Value - .Range("N" & z).Value / .Range("K" & z).Value

Next z
End With


The Troublesome part comes here:

Cells(z, "O").Value = .Range("L" & z).Value - .Range("N" & z).Value / .Range("K" & z).Value

This is the formula I'd actually like to execute:
(L2-N2)/K2

Thanks,

 

[PHV]

Lacks of parenthesis ?
Where is rngLastcell assigned a value ?
= (.Range("L" & z).Value - .Range("N" & z).Value) / .Range("K" & z).Value

Why not simply set the Formula or FormulaR1C1 property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

if .Range("K" & z).Value <> 0 then
  .Cells(z, "O").Value = (.Range("L" & z).Value - .Range("N" & z).Value) / .Range("K" & z).Value
else
  [b].Cells(z, "O").Value = ???[/b]
end if

also consider the else condition

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Excelerate2004]

Thanks PHV, I forgot to set the rngLastcell value D'OH!! Bad mistake!

Cheers!

 

[Zathras]

I'm with PHV. Why not use the FormulaR1C1 property?

Here's one way:

Sub Test()
Dim z As Long
  z = 13
  With Range("O2:O" & z)
    .FormulaR1C1 = "=(RC[-3]-RC[-1])/RC[-4]"
    .Copy
    .PasteSpecial xlValues
  End With
  Application.CutCopyMode = False
End Sub