Loops with numbers and text

**v2jtb** · 02-01-2010, 07:38 AM

Hi All,

I am new to this and trying to understand loops and arrays

I have my first loop that adds 1 to 10 on column A1 and works.....

I can move the colum with second 1 to say C1 etc but I don't know how to move the start to say A12?

Also I want to add text say "test" so the loop would add 1 test, 2 test etc, can this be done? I knoiw this is a simple loop but I need to get my head around the programming.

Sub first()
    For i = 1 To 10
        Cells(i, 1) = i
    Next i
End Sub

**D_Rennie** · 02-01-2010, 07:57 AM

hello.

A loop like that will go through each set [i] valve in the above case 1-10 these numbers can be anythink like i = 20 to 40

so say you want cells A12-A22 fulled

sub Fullcells ()
dim i as long ' always best to allocate the variable data type
for i = 12 to 22
cells(i,1).value = 1 & " Test"
next i 
end sub

hope this helps

**DonkeyOte** · 02-01-2010, 08:05 AM

D_Rennie, I think you need to account for the numeric prefix (ie i-11).

There are obviously countless ways to do things like this (loop / loop-less) but I agree that understanding loop construct is key.

v2jtb, please use CODE tags around VBA lines at all times.

**D_Rennie** · 02-01-2010, 09:14 AM

DonkeyOte, not much passes you up.

v2jtb. it brings up a good point though using i as a variable can lead to typos, Well atleast for those of us that lack the donkey vison. personally i use X at number variables that are to be used in loops. Though whatever rocks you boat i guess.

you also mentioned arrays here is a quick look at an array loop

Sub ArrayLoop()
Dim MyArray As Variant
Dim X As Long
'This is only one way too full an array, you can use a sheet range and other ways.
MyArray = Array("1stItem", "2ndItem", "3rdItem", "KeepAdding")
'LBound is the lowest item number in array, UBound is the upper item number in an array.
'This case 0 to 3 as the 1st array item is always 0
For X = LBound(MyArray) To UBound(MyArray)
    With ThisWorkbook.Sheets(1) 'always declare what sheet you want to work with and then use a parathanes? . for the sheet objects
        Cells(X + 1, 1).Value = MyArray(X) 'note the cell had to have +1 added from it as there is no cell A0
    End With
Next X
End Sub

hope this helps.
david

**v2jtb** · 02-01-2010, 12:48 PM

Thank you for your help

**v2jtb** · 02-01-2010, 01:01 PM

Hi D_Rennie thank you for your help

The Loop adds 1 Test, to row 12 to 22, how do you get it to count up to sat 10. Like 1 Test, 2 Test etc

i have tried to add +1 but I am still trying to get the syntax correct. Thank you again


dim i as long ' always best to allocate the variable data type
for i = 12 to 22
cells(i,1).value = 1 & " Test"
next i 
end sub

**v2jtb** · 02-01-2010, 01:03 PM

Sorry forget that I got it thank you

**v2jtb** · 02-01-2010, 01:12 PM

Hi All

Brilliant I can now add text in front and behind the Loop.
I now wonder is it possible to add Text in the row above and below the loop can this be done? Like below?

Image
Test 1 Test
Image/
Image
Test 2 Test
Image/

Sub Fullcells()
Dim i As Long ' always best to allocate the variable data type
For i = 12 To 22
Cells(i, 1).Value = "Test" & i & " Test"

Next i
End Sub

**D_Rennie** · 02-02-2010, 08:56 AM

hello.

when looping you can use steep in the loop this can also be used to loop backwards eg for x = 10 to 1 step -1

Sub StepLoop()
Dim X As Long, Counter As Long
With ThisWorkbook.Sheets(1)
Counter = 1
    For X = 1 To 10 Step 2: 'Note use of counter as x loop will go 1,3,5,7,9
        .Cells(X, 1).Value = Counter & " Test"
        .Cells(X + 1, 1).Value = "Testing"
        Counter = Counter + 1
    Next X
End With
End Sub

there are many ways to do the same thing in vba some stick in the head easer than other thats all.
If you have any libarys around you see if they can get some books on VBA, VB programming once the basics are understood i found trying to answer question on here realy helps to learn.
hope this helps
cheers