Loop through list of ranges with numbers in the names (VBA)

[cbaker1991]

Hi,

Long time lurker first time poster here. I have been doing some basic work with vba to aid in some of my regular excel tasks. This one is where I rollover a previous months file ready for input with the next month's data.

What I am doing is copying the "current month's data" into the "last month's" table and then clearing the current month data.

I have 46 different sets of tables each one relates to a single business area. All sets of data have a named range BU1-BU46 (business unit 1 - 46) each BU* has 5 variants e.g. "BU1ONCM", "BU1OFFCM", "BU1ONLM", "BU1OFFLM", "BU1OT" which stand for business unit 1 onshore current month, then offshore current month, onshore last month, offshore last month and overtime.

My current code is:
Range("BU1ONLM").Value = Range("BU1ONCM").Value
Range("BU1OFFLM").Value = Range("BU1OFFCM").Value
Range("BU1ONCM").ClearContents
Range("BU1OFFCM").ClearContents
Range("BU1OT").ClearContents

I have this code repeated for the amount of business units I have.

I have tried to use a For each to loop through the different BU* named ranges but could not figure out how to get it to work.

Can anybody help with how to make a loop to go through these with the variable being the number after "BU" in the named ranges?

My current code works perfectly fine it is just extremely long due to repeating 46 times my main aim is to reduce the length of the code to allow for it to be easier to maintain as I am leaving my current role soon and am trying to make all the spreadsheets more user friendly.

Many Thanks

 

[Joe4]

Welcome to the Board!

Here is a little loop example that shows you how to loop through all BU1ONCM - BU46ONCM ranges and return their address to a message box.

    Dim i As Long
    Dim rngName As String

    For i = 1 To 46
        rngName = "BU" & i & "ONCM"
        MsgBox Range(rngName).Address
    Next i

So, you should be able to build off of this structure to do what you want.

 

[Kenneth Hobson]

As usual, always test code on a backup copy. I added a bit of error checking but if you don't make any typos and the ranged names do exist, you can skip the If()'s and make it even more simple.

Sub Main()
  Dim i As Integer, r1 As Range, r2 As Range
  For i = 1 To 46
    Set r1 = Range("BU" & i & "ONLM")
    Set r2 = Range("BU" & i & "ONCM")
    If Not r1 Is Nothing And Not r2 Is Nothing Then
      r1.Value = r2.Value
      r2.ClearContents
    End If
    
    Set r1 = Range("BU" & i & "OFFLM")
    Set r2 = Range("BU" & i & "OFFCM")
    If Not r1 Is Nothing And Not r2 Is Nothing Then
      r1.Value = r2.Value
      r2.ClearContents
    End If
    
    Set r1 = Range("BU" & i & "OT")
    If Not r1 Is Nothing Then r1.ClearContents
  Next i
End Sub

 

[cbaker1991]

Thanks, both work perfectly