# Solved: EXCEL and VBA: Dynamic array containing sheet names



## Keebellah (Mar 27, 2008)

HI, 
I need some help on the following: 
I have 2 questions: 
Below is a recorded macro that selectes a number os shteets and formats the selected area as a 2-decimal percentage:

*1st. question: *
Sub SelectSheets() 
' SelectSheets Macro 
' De macro was recorded on 14/08/2008 by Hans Hallebeek. 
Sheets(Array("GBR", "green", "blue", "red", "clear", "yellow").Select 
Sheets("GBR").Activate 
Range("B2:M1002").Select 
Selection.NumberFormat = "0.00%" 
Range("B2").Select 
Sheets("GBR").Select 
End Sub

The number of sheets I have to select can vary, there is a list in a main sheeft where the names of the active sheets is listed.

I can read the list into an array with a a routine like 
for x = 1 to range("A35").end(xlup).row 
arr(x) = range("A" & x).Value 
next x

but how can a replace the Array("GBR", "green", "blue", "red", "clear", "yellow") part in the line below 
Sheets(Array("GBR", "green", "blue", "red", "clear", "yellow")).Select

by the contents of the array arr()?

*2nd. question:* 
The following part Selection.NumberFormat = "0.00%" 
depends on the system's International settings. 
I have my system set for a . chr(46) as a decimal sympor but most of the other users have the system set to , chr(44) as decimal format

I have a routine the is based on 
Case select Asc(Application.DecimalSeparator) 
case = 44 
Range("N2:N1002").Select 
Selection.NumberFormat = "0,0" 
Range("B2:M1003").Select 
Selection.NumberFormat = "0,00%" 
Range("O1").Select 
Selection.NumberFormat = "0,00%" 
Range("B2").Select 
case = 46 
Range("N2:N1002").Select 
Selection.NumberFormat = "0.0" 
Range("B2:M1003").Select 
Selection.NumberFormat = "0.00%" 
Range("O1").Select 
Selection.NumberFormat = "0.00%" 
Range("B2").Select 
end Select

Is there another way to do this, the reason is there are more places where the format is used.

Thanks for any tip


----------



## bomb #21 (Jul 1, 2005)

1st answer.

The following assumes that the main sheet is named "Master" and the list of sheet names starts in A1 & continues downwards.

Sub test()
x = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
For Each Sheet In ActiveWorkbook.Sheets
res = Application.Match(Sheet.Name, Range("Master!A1").Resize(x, 1), 0)
If IsError(res) Then
Else
Sheet.Range("B2:M1002").NumberFormat = "0.00%"
End If
Next Sheet
End Sub

Question: why did you use x = 1 to range("*A35*").end(xlup).row?


----------



## Keebellah (Mar 27, 2008)

Hi,
I'll put it to use and see what happens.
To answer your question,

I used the range("A35").end(xlup).row to determine the last row in the range 1 through 34
because this is where the extra sheet name is set when it is added.

I allow a max of 34 sheets to be added for this section.

What the excel sheet does is read all the hobbit logs (Netwrok monitoring program) which are put for me in a particular folder, I can choose the logs I want to report on and for each different status (which is color coded, thats why red, green, buel etc) the sheet is filled with the % of that particular status during the past 12 months (array also changes with every month change)
The GBR sheet is identic but combines all the results adding and subtracting showing server or service availability, green = +%, blue= +% (blue dot), red = -% etc.
Quite a chore but...
it works.


----------



## bomb #21 (Jul 1, 2005)

Keebellah said:


> I used the range("A35").end(xlup).row to determine the last row in the range 1 through 34 because this is where the extra sheet name is set when it is added. I allow a max of 34 sheets to be added for this section.


What's below A35?


----------



## Keebellah (Mar 27, 2008)

Hi Bomb,
There is other data below 33 which I use for other things,
I looked at your code but cannot make it work, firts I had to change the syntax to WorksheetFunction.Match(.....) but to no effect.
If I interpret it correctly it does not do what I need.
I have to slect Sheet("GBR") and fpormat range B2:M1002, then sheet("blue"), green etc,
I wnated to select these as oneas I did to reocr the macro but then edit it and build the array depending on what is entered in A16 though A34 ( I use A1 through A35 as an example but the list starts in A16 through A34 thatá why I select A35 and the xlup in case 34 is filled.
I hope I explained it in a understanding manner.
I can't send the sheet since it is quite big and will not function if the log files to be read aren't available some place.
I'll see if I can put a working example together to zip and mail)


----------



## bomb #21 (Jul 1, 2005)

Keebellah said:


> Hi Bomb, There is other data below 33 which I use for other things ...




That's a fundamental no-no, design-wise, and *very probably* the cause of "cannot make it work".

What's in A15/column B?


----------



## Keebellah (Mar 27, 2008)

I have to disagree that it's not working, below the working code I am using now:

Function FormatPer100()
Dim Upd As Boolean
Upd = Application.ScreenUpdating
If Upd = True Then Application.ScreenUpdating = False
Select Case Asc(Application.DecimalSeparator)
Case Is = 46
Call Formatper1002
Case Is = 44
Call FormatPer1001
End Select
Sheets("Admin").Activate
Application.ScreenUpdating = Upd
End Function

Function FormatPer1001()
Dim xRow As Integer, y As Integer
For xRow = 16 To Sheets("ADmin").Range("A34").End(xlUp).Row
Sheets(Sheets("Admin").Cells(xRow, 1).Value).Activate
Range("N2:N1002").Select
Selection.NumberFormat = "0,0"
Range("B2:M1003").Select
Selection.NumberFormat = "0,00%"
Range("O1").Select
Selection.NumberFormat = "0,00%"
Range("B2").Select
Sheets(Sheets("Admin").Cells(xRow, 1).Value).Tab.ColorIndex = SetTabColor(Sheets("Admin").Cells(xRow, 1).Value)
Next xRow
Sheets("GBR").Select
End Function

Function Formatper1002()
Dim xRow As Integer, y As Integer
For xRow = 16 To Sheets("ADmin").Range("A34").End(xlUp).Row
Sheets(Sheets("Admin").Cells(xRow, 1).Value).Activate
Range("N2:N1002").Select
Selection.NumberFormat = "0.0"
Range("B2:M1003").Select
Selection.NumberFormat = "0.00%"
Range("O1").Select
Selection.NumberFormat = "0.00%"
Range("B2").Select
Sheets(Sheets("Admin").Cells(xRow, 1).Value).Tab.ColorIndex = SetTabColor(Sheets("Admin").Cells(xRow, 1).Value)
Next xRow
Sheets("GBR").Select
End Function


As you can see I even included a routine to color the sheet tabs to the name, red, blue, purple, etc.

What I was looking for instead of the 16 through 34 was the way to program the varying range and then the formatting to have this all in one function.

As the code is above it works 100%, even the formatting check on the decimal value


----------



## bomb #21 (Jul 1, 2005)

Keebellah said:


> If I interpret it correctly it does not do what I need ... I wnated to select these as oneas I did to reocr the macro but then edit it and build the array depending on what is entered in A16 though A34


It does what you need, just differently. It loops through all sheets, processing the listed ones but skipping the unlisted ones.

See the attached. As long as the list (a) starts in A16 & carries on down with no breaks (b) is "isolated" (i.e. the yellow cells are kept clear to facilitate *CurrentRegion*), it's all good.

Amended code to accommodate data layout:

Sub test()
For Each Sheet In ActiveWorkbook.Sheets
res = Application.Match(Sheet.Name, Range("Master!A16").CurrentRegion, 0)
If IsError(res) Then
MsgBox "Don't format sheet " & Sheet.Name & "."
Else
MsgBox "Do format sheet " & Sheet.Name & "."
End If
Next Sheet
End Sub


----------



## bomb #21 (Jul 1, 2005)

Keebellah said:


> I have to disagree that it's not working, below the working code I am using now:


You misunderstand me. "No-no" means "not recommended", not "not working".


----------



## Aj_old (Sep 24, 2007)

Keebellah said:


> *2nd. question:*
> The following part Selection.NumberFormat = "0.00%"
> depends on the system's International settings.
> I have my system set for a . chr(46) as a decimal sympor but most of the other users have the system set to , chr(44) as decimal format
> ...


On you second question is a simple answer ( I think).
before setting the numberformat you wanna, put this code:

```
With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = " "
        .UseSystemSeparators = False
    End With
```
This will make excel use the dot "." as decimal separator and space (or if you wanna change it to comma ",") as thousands separator!
After applying the format you need, put this code:

```
With Application
        .UseSystemSeparators = True
    End With
```
It will make the excel to return to it's initial settings and use the regional settings!
HTH!


----------



## Aj_old (Sep 24, 2007)

Or you could use a code like this:

```
Sub test()
Dim sheet As Worksheet, x, res As Integer, sep As String
x = Sheets("Master").Range("A35").End(xlUp).Row
sep = Application.DecimalSeparator
For Each sheet In ActiveWorkbook.Sheets
    With sheet
        res = WorksheetFunction.CountIf(Worksheets("Master").Range("A16:A" & x), .Name) 
        If res >= 0 Then
            .Range("B2:M1002").NumberFormat = "0" & sep & "00%"
            .Range("N2:N1002").NumberFormat = "0" & sep & "0"
            .Range("B2:M1003").NumberFormat = "0" & sep & "00%"
            .Range("O1").NumberFormat = "0" & sep & "00%"
        End If
    End With
Next sheet
End Sub
```
Using something like this is fine, in case the list o sheets on your Admin sheet is always uptodate, and there's no chance that you'll have a name in that list, but won't have any sheet with the same name, because in this case you'll end up with an error!

But in the end is your decision what code to use


----------



## bomb #21 (Jul 1, 2005)

OK, here's *a* way to group sheets according to a list of sheet names. Thanks to Tushar Mehta MVP for the info that the Select method has an optional parameter ("False").

Note that while it allows global operations (such as Range("A1") = "This sheet was selected."), you'd still have to loop through for local stuff, such as Range("A1) = Sheet.Name, AFAIK.

As Tushar points out (and all the experts agree), "selecting and activating objects is *a step of last resort*".

Sub test()
Sheets(Range("Master!A1").Value).Select
For Each Cell In Range("Master!A1").CurrentRegion
If Cell.Address <> "$A$1" Then
Sheets(Cell.Value).Select *False*
End If
Next Cell

Range("A1").Select
Selection = "This sheet was selected."

Sheets("Master").Select

End Sub


----------



## Keebellah (Mar 27, 2008)

I put all the tips and suggestions together and kept those in mind.
I solved it running through the list of available sheets.
Since everything is laced together for other reasons that was the best solution.
Even the delimiters and international settings work.
Thanks, that's team work.


----------

