array question

[Gary Keramidas]

i have sheets name jan, feb, mar and so on.

i want to set a column letter based on the sheetname

just wondering if some sort of array would work instead of a bunch of if
statements

something like this

arr = Array(ActiveSheet.Name("Jan", "Feb", "Mar")
col = Array("K", "L", "M")

just wondering

 

[Guest]

Hi, Gary, if we consider the prerequisite that you actually have 12 tabs
named "Jan", "Feb", etc. you can use the following code to associate a letter
to each tab and (in my exemple), put this letter in cell 1,1 of each tab.

I hope I understood your problem all right.

Cheers - Yannick.

Sub TabColLetters()
Dim arr, col As Variant
Dim i As Integer

arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _
"Aug", "Sep", "Oct", "Nov", "Dec")
col = Array("K", "L", "M", "N", "O", "P", "Q", _
"R", "S", "T", "U", "W")

For i = 0 To 11
Worksheets(arr(i)).Cells(1, 1).Value = col(i)
Next i
End Sub

 

[Guest]

Hi Gary,

The code would look something like this:

Dim vntMonth As Variant
Dim vntColumn As Variant

vntMonth = Array("Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
vntColumn = Array("A", "B", "C", "D", "E", "F", _
"G", "H", "I", "J", "L")

It might be better to have them combined into one array. I guess it depends
on how you're using them. A single array might be something like:

Dim strMonthColumn(1 to 2, 1 to 12) As String
....

Then you could loop through the array to fill it. So when you refer to it
like this:
strMonthColumn(1,1) would be month "Jan" and
strMonthColumn(2,1) would be column "D"

Hope that helps.

Regards,
James

 

[Gary Keramidas]

what i was trying to accomplish was this:

if the active sheet is jan, look at the array and set a the column variable
to K, if the active sheet was sep, the column variable would be S.

it's probably more trouble than it's worth, since if statements would do
the job, just looking ofr othr ways to accomplish task.


thanks

 

[Gary Keramidas]

ended up using this:

Sh = ActiveSheet.Name
Select Case Sh

Case Is = "Jan"
cNum = "K"
Case Is = "Feb"
cNum = "L"

and so on
-



Gary

 

[Guest]

OK. I had misunderstood your initial plan...

ended up using this:

Sh = ActiveSheet.Name
Select Case Sh

Case Is = "Jan"
cNum = "K"
Case Is = "Feb"
cNum = "L"

and so on
-



Gary