how to make part of Array contents as parameters? 1

[feipezi]

Hi,

I have an array like tabary=Array("a","b","c"). I tried to do this followings but failed.

Any idea how I should handle the situation? I don't want to write more than one Array process, if I can put those strings as Array arguments.

Thanks in advance.


Sub Week17By17(contnts As String)
Dim wk17 As Variant
Dim i, j As Long
wk17 = Array(contnts)
For q = LBound(wk17) To UBound(wk17)
Cells(10, wk17(q)).Activate
...
...
Next
End Sub

Sub testit
Week17By17 ("a", "b", "c")
Week17By17 ("x", "y", "z")

 

[SkipVought]

I'd just pass "a,b,c"

Then use the Split() function delimited by comma "," to parse into a variant variable.

Wk17 = Split(contnts,",")

 

[feipezi]

Hi Skip,

I tried your way. But the macro won't run. I'm trying to figure out what's going on. Here is the code. The code is to write something like "01-24-2014 to 05-16-2014" in one cell, and "05-23-2014 to 09-12-2014" the next cell, with 17-18 weeks apart. On Column 'J', I write the date above; and Col. "cn", "cf" and "cj" with a different date range.

The problem now is, Excel has no response. No error message either. Thanks again for your help.


Sub Week17By17(swk As String, cntnts As String)
Dim wk17 As Variant, wkj As Variant
Dim i, j As Long
wk17 = Split(contnts, ",")
For q = LBound(wk17) To UBound(wk17)
MsgBox q
Cells(3, wk17(q)).Activate
rw = ActiveCell.Row
col = ActiveCell.Column
For i = 0 To 112 Step 7
If i = 0 Then
dummy = "'" & Format(CDate(swk) + i, "mm-dd-yy")
ElseIf i = 112 Then dummz = Format(CDate(swk) + i, "mm-dd-yy")
End If
Next
swk2 = CDate(swk) + i
dummaa = dummy & " to " & dummz
ActiveCell = dummaa
For j = 0 To 112 Step 7
If j = 0 Then
dummbb = "'" & Format(CDate(swk2) + j, "mm-dd-yy")
ElseIf j = 112 Then dummbc = Format(CDate(swk2) + j, "mm-dd-yy")
End If
Next
dummcc = dummbb & " to " & dummbc
ActiveCell.Offset(, 1) = dummcc
Next q
End Sub

Sub together(wk4 As String, wk17a As String, wk17b As String, wkdate As String, m1 As String, m2 As String, dumm As String, _
dumm2 As String)
'Week4By4 wk4
'Week17By17 wk17a, dumm2
Week17By17 wk17b, dumm
'DoWeeks wkdate
'DoMonths m1, m2
End Sub

Sub SemiFinal()
together "jul-25-14", "24-jan-2014", "21-mar-14", "12-Sep-14", "jun-14", "jul-14", "cf, cj, cn", "j"
End Sub

 

[SkipVought]

Would you mind explaining the relationship between your original code and your most recently posted code? I addressed one issue and your new code is....well I don't get the connection!!!

 

[feipezi]

Sorry about confusing you.

I simplified the code and please see the following. The idea is to set up 2 time ranges and each has 17 weeks apart at 3 different locations: Col. cf, cj and cn. What I need is to make the location variable: the new locations can be Col. a, d, g (corresponding to Array("cf", "cj", "cn")) with different starting week (swk). That's why I used "cntnts" as an argument which can be any column or columns specified by the user.
Hope you don't mind if you test it on a plain worksheet. There is no input data involved.

Thanks again. Hope I made myself clear this time.


Sub Week17By17(swk As String)
Dim wk17 As Variant, wkj As Variant
Dim i, j As Long
wk17 = Array("cf", "cj", "cn")
For q = LBound(wk17) To UBound(wk17)
'MsgBox q
Cells(3, wk17(q)).Activate
rw = ActiveCell.Row
col = ActiveCell.Column
For i = 0 To 112 Step 7
If i = 0 Then
dummy = "'" & Format(CDate(swk) + i, "mm-dd-yy")
ElseIf i = 112 Then dummz = Format(CDate(swk) + i, "mm-dd-yy")
End If
Next
swk2 = CDate(swk) + i
dummaa = dummy & " to " & dummz
ActiveCell = dummaa
For j = 0 To 112 Step 7
If j = 0 Then
dummbb = "'" & Format(CDate(swk2) + j, "mm-dd-yy")
ElseIf j = 112 Then dummbc = Format(CDate(swk2) + j, "mm-dd-yy")
End If
Next
dummcc = dummbb & " to " & dummbc
ActiveCell.Offset(, 1) = dummcc
Next q
End Sub

Sub SemiFinal()
Week17By17 "21-mar-14"
End Sub


If I do the following, then I'll come across some errors:


Sub Week17By17(swk As String, cntnts As String)
Dim wk17 As Variant
Dim i, j As Long
wk17 = Split(cntnts, ",")
For q = LBound(wk17) To UBound(wk17)
MsgBox wk17(q)
Cells(3, wk17(q)).Activate
rw = ActiveCell.Row
col = ActiveCell.Column
For i = 0 To 112 Step 7
If i = 0 Then
dummy = "'" & Format(CDate(swk) + i, "mm-dd-yy")
ElseIf i = 112 Then dummz = Format(CDate(swk) + i, "mm-dd-yy")
End If
Next
swk2 = CDate(swk) + i
dummaa = dummy & " to " & dummz
ActiveCell = dummaa
For j = 0 To 112 Step 7
If j = 0 Then
dummbb = "'" & Format(CDate(swk2) + j, "mm-dd-yy")
ElseIf j = 112 Then dummbc = Format(CDate(swk2) + j, "mm-dd-yy")
End If
Next
dummcc = dummbb & " to " & dummbc
ActiveCell.Offset(, 1) = dummcc
Next q
End Sub

Sub SemiFinal()
Week17By17 "21-mar-14", "cf, cj, cn"
End Sub

 

[mintjulep]

I really can't follow what you are trying to do, either from your code or your explanation.

However, I'm pretty sure that with this FAQ faq68-6900 you can accomplish it with no VBA needed.

 

[SkipVought]

Clear as mud!