Thread: Range object/property problem.

Hail Experts!

I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column.

Below, there are two procedures. "SimpleCalc" and "SimpleCalc2".
I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it.

I'm trying to develop more function based on this code, so I need to make it neat and flexible.

Can anyone tell me what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"?

I'm using Excel 2003.

Thanks!

---------------------

Sub SimpleCalc()

Dim SimpleMethodRng, SimpleMethod As String, i As Integer

ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select

SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")

For i = 1 To 8

SimpleMethod = SimpleMethodRng(i, 1)

If SimpleMethod = "per pyung" Then _
Selection.Offset(i - 1, 0).Value = SimpleMethodRng(i, 2) * Range("GLA") _
Else Selection.Offset(i - 1, -1).Value = SimpleMethodRng(i, 3) / Range("GLA")

Next i

End Sub


Sub SimpleCalc2()

Dim SimpleMethodRng As Range, SimpleMethod As String, i As Integer

SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")

For i = 1 To 8

If SimpleMethodRng(i, 1) = "per pyung" Then _
SimpleMethodRng(i, 3).Value = SimpleMethodRng(i, 2) * Range("GLA") _
Else SimpleMethodRng(i, 2).Value = SimpleMethodRng(i, 3) / Range("GLA")

Next i

End Sub

Solution to my own problem... I'm a newbie to VBA coding and I seem to have forgotten using "Set" for defining range. I'm not sure if "Set" is required for the range but that solved the problem. Anyways, better, refined code below.

Sub CrossCalculation()

Dim myRange As Range

Set myRange = ActiveWorkbook.Worksheets("Dashboard").Range("N5:N12")

For Each Cell In myRange

Select Case Cell.Value

Case "per pyung"
Cell.Offset(0, 2) = Cell.Offset(0, 1) * Range("GLA")

Case Else
Cell.Offset(0, 1).Value = Cell.Offset(0, 2).Value / Range("GLA")

End Select

Next

End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here