Simple VBA code questions

[Jo]

Hi everyone,

I have thise short piece of code (Macro):

Dim SCell As Range
If SCell.Value <> 1 Then
Range("J24").Select
Calculate
ElseIf SCell = 1 Then
End If

where "SCell" is name of a cell. I want to keep running till SCell is
"1". I know it needs a do-loop kind of thing. Anyhelp would be greatly
appreciated.

Thanks,
Jo

 

[Joel]

Dim SCell As Range
RowCount = 1
do while cells(RowCount,"J").value <> 1
RowCount = RowCount + 1
loop

 

[Jo]

Dim SCell As Range
RowCount = 1
do while cells(RowCount,"J").value <> 1
RowCount = RowCount + 1
loop









- Show quoted text -

SCell is defined but never used in your version?!

 

[JLGWhiz]

The problem with putting this in a loop is
that if your calculation never puts the value
of SCell to 1, the loop will run infinitely or
until you get a memory overflow. However, here
is the loop.

Dim SCell As Range
Do Until SCell.Value = 1
If SCell.Value <> 1 Then
Range("J24").Select
Calculate
End If
Loop

You could avoid the continuous loop by making the
Do Until SCell.Value >= 1 Or <= 1, depending on
Whether your calcultion runs up or down.

 

[Jo]

The problem with putting this in a loop is
that if your calculation never puts the value
of SCell to 1, the loop will run infinitely or
until you get a memory overflow. However, here
is the loop.

Dim SCell As Range
Do Until SCell.Value = 1
If SCell.Value <> 1 Then
Range("J24").Select
Calculate
End If
Loop

You could avoid the continuous loop by making the
Do Until SCell.Value >= 1 Or <= 1, depending on
Whether your calcultion runs up or down.









- Show quoted text -

I am getting this error: "Object variable or With block variable not
set"!?

 

[JLGWhiz]

Since you Dim the SCell As Range, Somewhere in your code, before you run the
loop, you need to define the SCell:

Set SCell = Worksheets(?).Range(?)

Fill in the ? with your worksheet and range data.