Changing location of array code from Sheet to Module

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
So I have some code located on Sheet1(Testing). It allows me to merge certain ranges on that sheet. Here it is:

Code:
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:

Code:
.....
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
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
The array is simply a list of addresses & therefore should not be qualified.
try
Code:
Worksheets("Testing").Range(a).Merge
 
Upvote 0
The array is simply a list of addresses & therefore should not be qualified.
try
Code:
Worksheets("Testing").Range(a).Merge

So what you are saying is that by using:
Code:
[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:

Code:
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
 
Last edited:
Upvote 0
You've got it correct, if you wanted to merge the same cells on various sheets you'd do it like this
Code:
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/
 
Upvote 0
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:

Code:
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!!
 
Last edited:
Upvote 0
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.
 
Upvote 0
Awesome. Thanks for the clarification. I agree that using the singular/plural would be beneficial.

Thanks again for your help!

-Spydey
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top