Macro to loop through multiple rows of cell references

[paul_sykes00]

Hi there,

I have a excel sheet1 in which various cells reference cellvalues from the first row of cells on sheet2. (eg. Cell B15 on sheet1 mighthave the formula =sheet2!A1)

I am trying to create a macro that will run my existingmacro (lets call it macroX) then change the references in sheet1 to the secondrow of cells on sheet2 and repeat through all rows of cells until no more datais included on the row in sheet2.

Hope that makes sense. Thanks in advance for your help

 

[StephenCrump]

Welcome to the Forum!

I'd do it like this:

Sheet1!B15: =INDEX(Sheet2!A:A,No)
where No is a range name, which you can set manually to 1 or 2 or 3 ....

Sub MyLoop()

    Dim i As Long
    
    Application.ScreenUpdating = False
    
    For i = 1 To Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
        Range("No").Value = i
        Call MacroX
    Next i
    
    Application.ScreenUpdating = True

End Sub

 

[paul_sykes00]

Thanks for your response Stephen. Marco just seems to stop wen it gets to Range("No").Value = i

Any idea what I am doing wrong?

 

[StephenCrump]

Marco just seems to stop wen it gets to Range("No").Value = i

Did you create a range name called "No"?

Or try: https://app.box.com/s/896kljkli3cygjsn2iu283lpotdjfowh

 

[paul_sykes00]

Thanks so much. That works perfectly