Script needed

[kelly mort]

Hello ,

I need a script for some task:

I have a list of items in a range say A2:A50. I need a script that when I run it, it will change the cell B2 to the value in A2 then A3 in that order till I get to the last cell in the range A2:A50 that has no value. The cells don't have any formulas.

I need this script because I will use it for printing a sheet and the data on the sheet is determined by the value in B2 . So I need a script that can help me take care of take.


So after changing the cell value in B2 to the content in A2, I call my print macro then change the value to the content of A3 then call my macro again in that order.

Thank you in advance
Kelly

 

[PatOBrien198]

something like:

Sub LoopIt()

Dim rng As Range
Dim r As Long
Set rng = ActiveSheet.Range("A2")

r = 0
Do While rng.Offset(r, 0) <> ""
    Range("B2") = rng.Offset(r, 0)
    
    ' call your printing routine here

    r = r + 1
Loop
End Sub

 

[MickG]

Try this:-
Each Time you run the code the value in "B2" will change

Option Explicit
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Sub[/COLOR] Newname()
[COLOR="Navy"]If[/COLOR] rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] rng = Range("A2")
    Range("B2") = rng
    [COLOR="Navy"]Set[/COLOR] rng = rng.Offset(1)
[COLOR="Navy"]If[/COLOR] rng = "" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] rng = Range("A2")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kelly mort]

Wow. Faster than I thought. Thanks to you all for the great assistance. I am grateful.

Both codes worked just fine.