do while / VBA

This is very simple example of using  do while. When you have some data in sheet1(named data) and you want to get results in sheet2 (named results) and the data is lying in A and B columns of sheet1.


data

	A	B
1	1	0
2	2	1
3	3	2

results

	A
1	1
2	1
3	1

Sub

Dim i as long

i=1

Do while Sheet1.Cells(i,1)<>""

Sheet2.Cells(i,1)= Sheet1.Cells(i,1)-Sheet1.Cells(i,2)

i=i+1

Loop

End Sub


When the sheets are named it is much easier to use these names. So let's see the same formula but this time the names are used.

Sub SubExample()

Dim i as long

i=1

Do while Sheets("data").Cells(i,1)<>""

Sheets("results").Cells(i,1)= Sheets("data").Cells(i,1)-Sheets("data").Cells(i,2)

i=i+1

Loop

End Sub

Now let's change the sign and instead of "-" let's use "&".  We get similar results when we use CONCATENATE excel function.

Sheets("results").Cells(i,1)= Sheets("data").Cells(i,1)& Sheets("data").Cells(i,2)

results

	A
1	10
2	21
3	32