Changing location of array code from Sheet to Module

[spydey]

So I have some code located on Sheet1(Testing). It allows me to merge certain ranges on that sheet. Here it is:

Private Sub Merge()    
    Dim Ary As Variant
    Dim Rng As Range
    Dim a As Variant
    
    Ary = Array("B23:B24", "B25:B26", "B27:B28", "B29:B30", etc etc etc ......)
    For Each a In Ary
        Range(a).Merge
    Next a
    
End Sub

Works great.

However, I want to clean things up a bit and so inserted a module. I would like to move the code from the sheet to the module and have it target just Sheet1(Testing) when run.

If sheet1(testing) is the active sheet, then it runs the code just fine. But if any other sheet is active, it runs that code on the active sheet instead of sheet1(testing), which is to be expected.

So I tried to change the code to only run on sheet1(Testing) but I believe that I am running into some syntax issues. I am still learning syntax for VBA and how everything is related.

My change was this:

.....
Ary = Array(ThisWorkbook.Worksheets("Testing").Range("B23:B24", "B25:B26",etc etc etc......))
.....

But I am getting a runtime error 450, Wrong number of arguments or invalid property assignment.

Where am I messing up and why?

I ask "why" because I don't just want to be given an answer. I want to understand the reasoning behind it and be able to solve similar things in the future.

I have been doing a bunch of reading and based upon what I am reading, it appears to me that I have it correct, but I am obviously missing something.

Any thoughts?

-Spydey

 

[Fluff]

The array is simply a list of addresses & therefore should not be qualified.
try

Worksheets("Testing").Range(a).Merge

 

[spydey]

The array is simply a list of addresses & therefore should not be qualified.
try

Worksheets("Testing").Range(a).Merge

So what you are saying is that by using:

[COLOR=#333333]Ary = Array(ThisWorkbook.Worksheets("Testing").Range("B23:B24", "B25:B26",etc etc etc......))[/COLOR]

I am quantifying the array, which shouldn't be done, right?

So the array is just a list of addresses. We then pipe those addresses into the 'Range.(a).Merge' but by preluding that with 'Worksheets("Testing").', we are indicating where the list of addresses reside which need to be merged. Is that right?

So if I understand that correctly, and please correct me if I don't, if I need to do the same thing for those same exact ranges found in the array, but on various sheets, I could simply add multiple lines of code such as this:

For Each a In Ary
        Worksheets("Testing").Range(a).Merge
        Worksheets("Other1").Range(a).Merge
        Worksheets("Other2").Range(a).Merge
        etc
        etc
        etc
    Next a

Although looking at it, I bet that could be made even simpler if we array the known worksheets and then utilizing that array we work through each worksheet and range .... granted, that is if each range that needed to be manipulated was the same in each worksheet ..... right?

Also, thanks so very much for your help. I learn something new every day here. Sometimes my brain hurts as I try to digest it all .... hahahahaha

-Spydey

 

[Fluff]

You've got it correct, if you wanted to merge the same cells on various sheets you'd do it like this

Private Sub Merge()
    Dim Ary As Variant
    Dim Shts As Variant
    Dim Sht As Variant
    Dim Rng As Range
    Dim a As Variant
    
    Shts = Array("Sheet1", "Sheet2", "Sheet3")
    Ary = Array("B23:B24", "B25:B26", "B27:B28", "B29:B30", etc etc etc ......)
    For Each Sht In Shts
      For Each a In Ary
          Sheets(Sht).Range(a).Merge
      Next a
   Next Sht
    
End Sub

One thing. Merged cells can be a massive problem, especially with VBA. I'd suggest you take a look at "Center across selection" instead https://excelexposure.com/alternative-to-merged-cells-quick-tip/

 

[spydey]

Fluff,

Question for you, does Excel/VBA "know" that 'Sht' represents each singular item in the 'Shts' array? Is it just a given unless we purposefully set Sht as a different value? Because 'Shts' is plural, are we constrained to use the singular form of that, i.e. 'Sht', so that the coding works? What if I used a different variable instead of 'sht', but didn't set it's value? Would that make a difference?

Example:

Private Sub Merge()    Dim Ary As Variant
    Dim Shts As Variant
    Dim [SIZE=3][B]Otr[/B][/SIZE] As Variant
    Dim Rng As Range
    Dim a As Variant
    
    Shts = Array("Sheet1", "Sheet2", "Sheet3")
    Ary = Array("B23:B24", "B25:B26", "B27:B28", "B29:B30", etc etc etc ......)
    For Each [SIZE=3][B]Otr[/B][/SIZE] In Shts
      For Each a In Ary
          Sheets([SIZE=3][B]Otr[/B][/SIZE]).Range(a).Merge
      Next a
   Next [SIZE=3][B]Otr[/B][/SIZE]
    
End Sub

Also, I will have to give that link a good read. Thanks for posting that!! I am trying to digest as much as I can right now, but sometimes going fast causes me to have more questions, which requires more reading, and then more questions, and it becomes this vicious cycle, hahahahahaha.

-Spydey

P.S. Thanks, very much, for taking the time to help me out with this. I very much appreciate it. Thank you.

EDIT: Just got done reading the link you gave me. Very useful!! Thank you. I do have one question regarding it. It mentioned that the Center Across Selection only works horizontally. It also mentioned that there wasn't a known way at the time the article was written, to do so vertically. I tried it horizontally and it worked fine. But alas, no vertical in Excel 2013. Is there a way that you know of that would allow me to do it vertically? Currently I am unmerging via the code I posted in the OP, changing the needed data/cells, then merging again. If I could save those two processes, un-merge & merge, via a vertical Center Across Selection, that would be fabulous!!

 

[Fluff]

For your 1st point. Variable names can be anything you want, just avoid VBA Keywords, as this can cause problems.
In an example like this I tend to use singular/plural as I can then tell "at a glance" that one is a part of the other. I found this very useful in my early days if I had to modify a macro I'd created some months/years before.

For the 2nd point. No, unfortunately, you cannot centre vertically.

 

[spydey]

Awesome. Thanks for the clarification. I agree that using the singular/plural would be beneficial.

Thanks again for your help!

-Spydey

 

[Fluff]

Your welcome