lundi 11 septembre 2017

IF vba array sheetname

I have three types of sheets:

  1. Data sheet for each product (with the same structure). I mark with a code (at the example S or C or blank) the discount I want apply for each data sheet (at the example Black, Red, Shine ,.... sheets)
  2. Sheet Asign to mark the sheets it must consider into each group (at the example only two values at column B, but it can be from 2 to 25 or more)
  3. Summary Page (at the example Diary) to get the data summary (at reality I have one for group plus Diary.

I want concatenate sheet name from Data Sheets I indicate to consider at Assign page if it have the code I determine for this group

For example this sheets for Red and Black product (into the photo first a part of Asign sheet and the two sheets called as value of B1 cell value) Asign, Red, Black sheets

And I want to concatenate at Diary. What I get at the left of the image and what I want and at the right what I get What I want/get I think into add an If and if and if condition achieved create one second array for it and concatenate it or include the iff directly when I take values for DataSheetName array ... but I don't know why do that :(

Sub Diary()
Dim p As Integer, lRow As Integer
Dim q As String
'Check for the last row
With Worksheets("Asign")
    lRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

' Declare and chage array with all sheet names at column B os Asign sheet, excluding the first (ID of group)
p = 1
Dim DataSheetName() As String
ReDim DataSheetName(lRow)
Do
    DataSheetName(p) = Worksheets("Asign").Cells(p + 1, "B").Value
    p = p + 1
Loop While p < lRow
' Activo y situo el cursor en la pagina de sumario
With Worksheets("Diary")
    .Activate
    .Range("C7").Select
End With

' Inicialize i at 3 to count from C column
i = 3

Do
    Cells(7, i).Select
        For r = 1 To UBound(DataSheetName)
        'Concatenate with &:
           varConctnt = varConctnt & ", " & DataSheetName(r)
           Next r
        'remove the "&" before the first element:
        varConctnt = Mid(varConctnt, 2)
        q = varConctnt
        varConctnt = ""
    i = i + 1
    ActiveCell.Value = q
Loop While i < 11
Range("C7:J7").Select
    Selection.AutoFill Destination:=Range("C7:J12"), Type:=xlFillDefault
End Sub

Aucun commentaire:

Enregistrer un commentaire