array question
 

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


Gary

array question
 

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



"Gary Keramidas" wrote:

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


Gary

array question
 

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" wrote:

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


Gary

array question update
 

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


"Yannick59000" wrote in message
...
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



"Gary Keramidas" wrote:

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


Gary

array question
 

ended up using this:

Sh = ActiveSheet.Name
Select Case Sh

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

and so on
-



Gary


"Yannick59000" wrote in message
...
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



"Gary Keramidas" wrote:

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


Gary