Code Improvement - Use array to select multipul ranges on multiple sheets?

[Upex]

Hi All,

Once again I have confused myself and wonder if you can offer a shining light.

I have the following code:

Sub BulkSelectForPeriodInsert()
    Sheets("forecast of 1st Period").range("B2:C2").Value = range("np1").Value
    Sheets("review of 1st period").range("B2:C2,B12:C12").Value = range("NP1").Value
    Sheets("member progression 1st period").range("B2:C2").Value = range("Np1").Value
    Sheets("observations 1st Period").range("B2:C2,C78:D78,C148:D148,C218:D218,C288:D288,C358:D358,C428:D428,C498:D498,R498:S498,R428:S428,R358:S358,R288:S288,R218:S218,R148:S148,R78:S78,AG78:AH78,AG148:AH148,AG218:AH218,AG288:AH288,AG358:AH358,AG428:AH428,AG498:AH498,AV498:AW498,AV428:AW428").Value = range("NP1").Value
    
    Sheets("forecast of 2nd Period").range("B2:C2").Value = range("np2").Value
    Sheets("review of 2nd period").range("B2:C2,B12:C12").Value = range("NP2").Value
    Sheets("member progression 2nd period").range("B2:C2").Value = range("Np2").Value
    Sheets("observations 2nd Period").range("B2:C2,C78:D78,C148:D148,C218:D218,C288:D288,C358:D358,C428:D428,C498:D498,R498:S498,R428:S428,R358:S358,R288:S288,R218:S218,R148:S148,R78:S78,AG78:AH78,AG148:AH148,AG218:AH218,AG288:AH288,AG358:AH358,AG428:AH428,AG498:AH498,AV498:AW498,AV428:AW428").Value = range("NP2").Value
    
    'etc up to 13th period
End Sub

I basically need all these cells to have the same value - the named range 'NP1'. This code will be repeated 13 times for my ranges NP1 - NP13 for all 13 periods within the document.

I have been reading and wonder if I can perform this action, but using an array. If so, how as I cant seem to figure that out and do you think this offer any advantages over running the code above.

Many thanks for any suggestions. I'm fairly new to this and am eager to learn better methods.

Kind regards, Upex

 

[Boller]

I don't think the code you posted will work ( I think .value = .value needs to be contiguous ranges of the same size).

Do each of your named ranges consist of one cell only?

 

[Weaver]

What the OP is trying to do will work if all you're doing is seeding a range with a value from 1 cell. which is the same as if you tried to seed the range with the value of a variable or a constant.

Upex

I think this might work. Not tested though.

Sub test()
    Dim a As Integer, i As String
    For a = 1 To 13
        Select Case a
            Case 1
                i = a & "st"
            Case 2
                i = a & "nd"
            Case 3
                i = a & "rd"
            Case Else
                i = a & "th"
        End Select
        Sheets("forecast of " & i & " Period").Range("B2:C2").Value = Range("np" & a).Value
        Sheets("review of " & i & " period").Range("B2:C2,B12:C12").Value = Range("NP" & a).Value
        Sheets("member progression " & i & " period").Range("B2:C2").Value = Range("Np" & a).Value
        Sheets("observations " & i & " Period").Range("B2:C2,C78:D78,C148:D148,C218:D218,C288:D288,C358:D358,C428:D428,C498:D498,R498:S498,R428:S428,R358:S358,R288:S288,R218:S218,R148:S148,R78:S78,AG78:AH78,AG148:AH148,AG218:AH218,AG288:AH288,AG358:AH358,AG428:AH428,AG498:AH498,AV498:AW498,AV428:AW428").Value = Range("NP" & a).Value
    Next a
End Sub

 

[Boller]

Upex

In Excel 2007 and later versions, the Names "NP1", "NP2", etc. are not valid names (because they are the same as cell addresses).

You might want to re-name these so you do not have problems in the event that you upgrade Excel in the future.

 

[Upex]

Hi All, Many thanks for your time to reply here.

Boller - I use '07 at home and didn't even think of that one, so a rename will be on the cards (great as now 39 rather than the original 13), thanks for refreshing my memory and stopping me from further balding when I sent it to an 07 user.

Regards the first post, yes each of these ranges is 1 cell and all the destinations are 1 cell also. At document start up, the user enters a required date. I then have code look at a validation list of periods/years, match the user entry and then complete the 39 named ranges via offset method, i.e. so they all follow on. This gives all 39 periods the correct year and allows the user to start the document at whatever point they like (was the only way I know how it could be done ).

Weaver - Thanks for your effort to produce the code. I will have a go at this in the week. I have expanded the document to cover 3 years now, so have 39 of each of the 4 sheets, and a few other for good measure, all of which need these 'date/periods' entered at various points, so the code I have wrote is very long indeed and this will help immensly. Thanks, and I'll post back to let you know how I get on.

 

[Upex]

Weaver,

I have tried your code and it seems to work as it should, many many thanks.

this is the code i've used:

Sub test()
    Dim a As Integer, i As String
    For a = 1 To 13
        Select Case a
            Case 1
                i = a & "st"
            Case 2
                i = a & "nd"
            Case 3
                i = a & "rd"
            Case Else
                i = a & "th"
        End Select
        Sheets("forecast of " & i & " Period").Range("B2:C2").Value = Range("npi" & a).Value
        With Sheets("review of " & i & " period")
            .Range("B2:C2,B12:C12,b70:c70").Value = Range("NPi" & a).Value
            '.Range("D12:E12,D70:E70").Value = Range("Npi" & (a - 1)).Value
            '.Range("f12:g12,f70:g70").Value = Range("Npi" & (a - 2)).Value
        End With
        Sheets("member progression " & i & " period").Range("B2:C2").Value = Range("Npi" & a).Value
        Sheets("observations " & i & " Period").Range("B2:C2,C78:D78,C148:D148,C218:D218,C288:D288,C358:D358,C428:D428,C498:D498,R498:S498,R428:S428,R358:S358,R288:S288,R218:S218,R148:S148,R78:S78,AG78:AH78,AG148:AH148,AG218:AH218,AG288:AH288,AG358:AH358,AG428:AH428,AG498:AH498,AV498:AW498,AV428:AW428").Value = Range("NPi" & a).Value
    Next a
End Sub

I wonder if you can offer another hand? I'm trying to reduce my code and be more efficient, however, I'm pretty much making it up as I go and testing lots. Has worked in some areas, but this I'm stuck on.

The 2 commented lines within the code above:

            '.Range("D12:E12,D70:E70").Value = Range("Npi" & (a - 1)).Value
            '.Range("f12:g12,f70:g70").Value = Range("Npi" & (a - 2)).Value

Have presented me with a problem. These lines add the previous period and the second previous period for historic data labelling, so I tried the (a-1) and (a-2) - didnt work. Is there anyway that I can have it insert a period 1 before current and then 2 before current, but only on the sheets that need it? I.e. period 1 contains just npi1, period 2 contains npi2 and npi1, period 3 contains npi3, npi2 and npi1 and then the current and previous 2 for all remaining (now increased to 39 from 13).

Many thanks for any help you may be able to offer, and thanks again for your help already, it has saved a lot of code (especially as I was going to type it all for 39 periods rather than 13).


Boller - Thanks for your reminder, I have now amended the ranges to NPi then the number. I think this is OK with 07 onwards?

Thanks All,

Upex.

 

[Boller]

Boller - Thanks for your reminder, I have now amended the ranges to NPi then the number. I think this is OK with 07 onwards?

The last Column in Excel 2007 is XFD, so NPi plus a number would not be a valid name.

 

[Upex]

The last Column in Excel 2007 is XFD, so NPi plus a number would not be a valid name.

Thanks Boller, think I'm getting too waylayed with the specifics of the various things I'm trying to fix, and forgetting the simple basics. May be back to the begining for me

Many thanks for taking the time to enlighten me again, you've saved another posting on here when I got stuck down the line.