PDA

View Full Version : Solved: Loop with Go to steps?



doctortt
03-30-2011, 12:50 PM
Hi, Can someone shed me some light on how to write this?

I have a few instructions
-Write 15 in cell A1
-Write 25 in cell A2
-Write 99 in cell A3
etc.

If a loop runs, I want to loop to execute the first line, then increment the cell by one, and then execute the second line.

Thanks all

mdmackillop
03-30-2011, 12:56 PM
Option Explicit

Sub test()
Dim arr(), a
Dim i As Long

arr = Array(15, 25, 99)
For Each a In arr
i = i + 1
Range("A" & i) = a
Next
End Sub

Paul_Hossler
03-30-2011, 01:05 PM
increment the cell by one,


Increment each of the the cells's VALUEs by 1, or go to the next cell in your list?

In other words, do you want to end up with

A1 = 15
A2 = 25
A3 = 99

or

A1 = 16
A2 = 26
A3 = 100


or something else?

Paul

Wireless Guy
03-30-2011, 09:28 PM
There are probably a million ways to do this

I personally prefer using this Format to loop through cells

R=1

Do
Cells (R,1) = XXXXX ' Cells (Row, Column)

R=R+1
Loop until R = YYY

In your specific case you need to fill up an array with your results
If it's only 3 values you can use a Case Select or use that array thing like mdmackillp suggested up there (That's a new trick for me!)

If there are many values, you can step through and transfer them over one by one or store them all up in an array. Lots of different ways to do it. It all depends on your style.

Good Luck

Kenneth Hobs
03-31-2011, 05:31 AM
Of course if you don't need to do other things that require a loop:
Sub t()
Dim a() As Variant
a() = [{15,25,99}]
Range("A1").Resize(UBound(a)).Value2 = WorksheetFunction.Transpose(a)
End Sub

doctortt
04-03-2011, 12:29 AM
great. thanks all.