How to set variable to cell value in excel macro

M

most xlent

How do I perform a "do loop" using a cell value as criteria? (i.e.-do while
x < value($c$1). Thanks for your help.
 
D

Don Guillett

the help files are your friend
While...Wend Statement Example
This example uses the While...Wend statement to increment a counter
variable. The statements in the loop are executed as long as the condition
evaluates to True.

Dim Counter
Counter = 0 ' Initialize variable.
While Counter < 20 ' Test value of Counter.
Counter = Counter + 1 ' Increment Counter.
Wend ' End While loop when Counter > 19.
Debug.Print Counter ' Prints 20 in the Immediate window.
 
M

most xlent

Hi Don,
This would work if the cell value was constant, however, it is a result of a
formula that changes with variable input in the worksheet. Currently, I
input data into the worksheet and must look at the value of the cell. Then I
need to edit the macro with the new cell value for the do loop. This becomes
time consuming with all the data I am processing. Ideally, I would like the
macro to retrieve the cell value automatically. Thanks in advance for your
next response.
 
D

Don Guillett

It could probably be done within a worksheet_change event
right click the sheet tab>view code>go from there
 
K

Kleev

You could do something like this:
Dim ws As Worksheet
Dim i As Integer
Dim x As Integer
Set ws = ThisWorkbook.Worksheets("sheet2")
i = ws.Range("c1").Value
x = 1
Do While x < i
ws.Cells(x, 4).Value = x + i
x = x + 1
Loop
 
Top