us Name to ref to cell(R,C)

[Sidata]

I am trying use names to ref to calculations.
in following code i can get all of the "names" to work ecept "prod"

Check = True: counter = 0: R = 50 ' Initialize variables.

Do ' Outer loop.
Do While counter < 1137 ' Inner loop.
R = 50 + counter 'start row
C = 16 'start Column

CloseStk = Cells(R, C).Value
CalcZero = Cells(R - 2, C) - Cells(R, C - 1)
MinWks = Val(Cells(R - 3, 11).Value) / 4
ERQ = Cells(R - 3, 9).Value
Demand1 = Cells(R - 2, C + 1).Value
Demand2 = Cells(R - 2, C + 2).Value
Demand3 = Cells(R - 2, C + 2).Value
Demand4 = Cells(R - 2, C + 2).Value
MthsC = Cells(R - 3, 10).Value
Prod = Cells(R - 1, C).Value

If Cells(R - 3, 1).Value = "Y" And Cells(R - 3, 2).Value = 1 Then

If Cells(R, C).Value = 0 Then
If MthsC = 1 Then
If Demand1 > 0 Then
Prod = CalcZero + Demand1 * (Cells(R - 3, 11).Value) /
4
Else: Prod = CalcZero
End If

ElseIf MthsC = 2 Then
If Demand1 > 0 And Demand2 = 0 Then
Prod = CalcZero + Demand1
Else:
Prod = CalcZero + Demand1 + Demand2 * MinWks
End If

ElseIf MthsC = 3 Then
Cells(R - 1, C) = CalcZero + Demand1 + Demand2 + Demand3 *
(Cells(R - 3, 11).Value) / 4

ElseIf MthsC = 4 Then
Cells(R - 1, C) = CalcZero + Demand1 + Demand2 + Demand3 +
Demand4 * (Cells(R - 3, 11).Value) / 4

Else: Cells(R - 1, C) = ERQ

End IF


End If
End If


counter = counter + 4 ' Increment Counter.
If counter > 1137 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.

Thanks for any help

 

[HS Hartkamp]

If you want to link certain variables to cells, you should define them as
cells, and if it's variables you want, you should define them as such (e.g.
integer or single). e.g. DIM Prod as Object vs. DIM Prod as Single.

A cell-type variable linked to a physical cell on the worksheet is an
object, and should be assigned (or linked) using the SET statement, e.g. SET
Prod = Cells(R,C)
A variable that gets a value based on a cell does not, e.g. Prod =
Cells(R,C) [ is the same as Prod = Cells(R,C).Value ]


Example: The following code sets the worksheet cell J10 to the value of 100,
but J11 remains unchanged:
Dim c As Object
Set c = Cells(10, 10) 'Create a link to cell J10
c = 100 'Assing a value

Dim v As Single
v = Cells(10, 11) 'Set variable to the value of J11
v = 100 'Set variable to the value 100 (and forget
the previous value)

In any case, I'd try "option explicit" at the start of the routine (before
the SUB of FUNCTION statement), so that excel will force you to define all
variables in the correct way. Thus, there can be no miscommunication as to
what you want and what excel thinks you want.

Does this help ?

Bas Hartkamp.