request for a loop in VBA

[vimala]

hi,

i have a blank sheet and i printed 'X' in column AA5 by macro. now i need a loop in this macro which can print 'X' continuosly till AZ5.

Please help me on this.

regards,
Vimala

 

[Boller]

You don't need a loop :-

[AA5:AZ5]= "X"

 

[vimala]

Hi,

Thanks for your reply. I know that we can directly type X in selected area by giving from and to cells.

But I wanted to learn a loop function by using activecell.offset so that it will print X in next column and it should continue till where I want to.

Thanks,
Vimala

 

[Boller]

Hi,

Thanks for your reply. I know that we can directly type X in selected area by giving from and to cells.

But I wanted to learn a loop function by using activecell.offset so that it will print X in next column and it should continue till where I want to.

Thanks,
Vimala

Here's a copy of one of the results of looking up LOOP in VBE Help :-

Looping Through a Range of Cells

When using Visual Basic, you often need to run the same block of statements on each cell in a range of cells. To do this, you combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation.
One way to loop through a range is to use the For...Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable <CODE>counter</CODE> is substituted for the row index. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01.




<TABLE><TBODY><TR><TD>

<CODE>Sub RoundToZero1()</CODE>
<CODE>For Counter = 1 To 20</CODE>
<CODE>Set curCell = Worksheets("Sheet1").Cells(Counter, 3)</CODE>
<CODE>If Abs(curCell.Value) < 0.01 </CODE><CODE>Then </CODE><CODE>curCell.Value = 0</CODE>
<CODE>Next Counter</CODE>
<CODE>End Sub</CODE>






</PRE></TD></TR></TBODY></TABLE></P>Another easy way to loop through a range is to use a For Each...Next loop with the collection of cells specified in the Range property. Visual Basic automatically sets an object variable for the next cell each time the loop runs. The following procedure loops through the range A1:D10, setting to 0 (zero) any number whose absolute value is less than 0.01.




<TABLE><TBODY><TR><TD>

<CODE>Sub RoundToZero2()</CODE>
<CODE>For Each c In Worksheets("Sheet1").Range("A1:D10").Cells</CODE>
<CODE>If Abs(c.Value) < 0.01 Then </CODE><CODE>c.Value = 0</CODE>
<CODE>Next</CODE>
<CODE>End Sub</CODE>






</PRE></TD></TR></TBODY></TABLE></P>If you do not know the boundaries of the range you want to loop through, you can use the CurrentRegion property to return the range that surrounds the active cell. For example, the following procedure, when run from a worksheet, loops through the range that surrounds the active cell, setting to 0 (zero) any number whose absolute value is less than 0.01.




<TABLE><TBODY><TR><TD>

<CODE>Sub RoundToZero3()</CODE>
<CODE>For Each c In ActiveCell.CurrentRegion.Cells</CODE>
<CODE>If Abs(c.Value) < 0.01 Then </CODE><CODE>c.Value = 0</CODE>
<CODE>Next</CODE>
<CODE>End Sub</CODE>






</PRE></TD></TR></TBODY></TABLE></P>

 

[vimala]

The below is my actual script and it's printing X only in 25,26 columns of the sheet its not going to 27th column to print X.

So can you please change the script to contine print X till 35th column.

Sub test()
Dim row As Integer

Cells(5, 25).Select
ActiveCell.FormulaR1C1 = "X"

a = 5

Do

If Cells(a, 25) <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "X"
Else
a = a + 1
End If
Loop Until Cells(5, 35) = ""
End Sub

 

[Boller]

Did you read the extract from the Help file that I posted. The solution is there.

 

[Boller]

What is it exactly you want to do?

Put X in all cells from AA5 to AJ5 (or maybe some other cells?) only if they are blank ?

 

[JackDanIce]

There's a problem in your code

Sub test()
Dim row As Integer
 
Cells(5, 25).Select
ActiveCell.FormulaR1C1 = "X"
 
a = 5
 
Do
 
If Cells(a, 25) <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "X"
Else
a = a + 1
End If
Loop Until Cells(5, 35) = ""
End Sub

You're starting in cell Y5, printing an X and then testing cell Z5 and printing another X. Then you're testing cell AI5 (where I have coloured red) to see if it's empty. I am guessing it is empty which is why no more X's are printed because you've stopped looping

Another comment is you really should try to avoid using .select in VBA code where possible.

Try running this code instead:

Sub PuppyPower ()
 
Dim a as Integer
 
a = 25
Do
  cells(5,a) = "X"
  a = a + 1
Loop Until a = 53
 
End Sub

 

[JackDanIce]

Although, generally speaking if you know you the last value for your loop, you're better of using a FOR NEXT loop, e.g

Sub PuppyPower()
 
Dim a as Integer
 
For a = 25 to 52
  Cells(5,a) = "X"
Next a
 
End Sub

This is covered in Boller's postings too

 

[vimala]

Thanks!!!!!!!!!!!!!!!!!!! its working.

Regards,
Vimala