ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Simple VBA code questions (https://www.excelbanter.com/excel-programming/398619-simple-vba-code-questions.html)

Jo[_2_]

Simple VBA code questions
 
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

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


"Jo" wrote:

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



Jo[_2_]

Simple VBA code questions
 
On Oct 3, 3:37 pm, Joel wrote:
Dim SCell As Range
RowCount = 1
do while cells(RowCount,"J").value < 1
RowCount = RowCount + 1
loop



"Jo" wrote:
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- Hide quoted text -


- Show quoted text -


SCell is defined but never used in your version?!


JLGWhiz

Simple VBA code questions
 
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" wrote:

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



Jo[_2_]

Simple VBA code questions
 
On Oct 3, 4:34 pm, JLGWhiz wrote:
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" wrote:
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- Hide quoted text -


- Show quoted text -


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


JLGWhiz

Simple VBA code questions
 
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.

"Jo" wrote:

On Oct 3, 4:34 pm, JLGWhiz wrote:
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" wrote:
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- Hide quoted text -


- Show quoted text -


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




All times are GMT +1. The time now is 01:25 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com