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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.