Pasting An Array

[Ark68]

If I have this line in my code:

ElseIf rtarget.Name = "WPEALL" Then
        myDocument.Shapes.Range(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT")).Fill.ForeColor.RGB = RGB(255, 0, 0)

Is it possible for me to take the six values in the array and paste them over to another worksheet? I'd like to use them as a list?

 

[Rick Rothstein]

Do you mean something like this...

Sheets("Sheet1").Range("C1:C6") = Application.Transpose(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"))

 

[Ark68]

Yes, Rick ... that is something along the line of waht I need. But I'm wondering if I can advance this a bit.

Your code has these cells always going in cell c1:c6 for example. Suppose these 6 values are part of an already existing list of values in column c, that start at C2. I would need to transpose this array starting at the next blank cell after C2.

For example, C2:C8 are already populated. I would like to tranpose this array starting at C9.

 

[Rick Rothstein]

Give this a try...

NextRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
Sheets("Sheet1").Range("C1:C" & NextRow) = Application.Transpose(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"))

 

[Ark68]

Thanks Rick for your continued support.
For some reason, it's not performing as expected ... I'm wondering if it's related to the yet unanswered http://www.mrexcel.com/forum/excel-questions/694607-shape-ranges-not-performing-100%.html

Sub toggle()

    Dim myDocument As Worksheet
    Dim wshvar As Worksheet
    Dim rtarget As Variant
    Dim nextrow As Integer

    Set wshvar = Worksheets("varhold")
    Set myDocument = Worksheets("Workorders")
    Set rtarget = myDocument.Shapes(Application.Caller)
    
    
    With rtarget
        If .Fill.ForeColor.RGB = RGB(255, 255, 255) Then 'OFF to ON
            .Fill.ForeColor.RGB = RGB(255, 0, 0)
            MsgBox "Caller: " & rtarget.Name
        Else
            .Fill.ForeColor.RGB = RGB(255, 255, 255) 'ON to OFF
            MsgBox "OFF"
        End If
    End With

    If rtarget.Name = "CUEALL" Then
        myDocument.Shapes.Range(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
        nextrow = Cells(Rows.Count, "T").End(xlUp).Row + 1
        wshvar.Range("T3:T" & nextrow) = Application.Transpose(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT"))
    ElseIf rtarget.Name = "CULALL" Then
        myDocument.Shapes.Range(Array("CULDR", "CULDT", "CULFR", "CULFT", "CULCR", "CULCT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
    ElseIf rtarget.Name = "HPEALL" Then
        myDocument.Shapes.Range(Array("HPEDR", "HPEDT", "HPEFR", "HPEFT", "HPECR", "HPECT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
    ElseIf rtarget.Name = "HPLALL" Then
        myDocument.Shapes.Range(Array("HPLDR", "HPLDT", "HPLFR", "HPLFT", "HPLCR", "HPLCT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
    ElseIf rtarget.Name = "RPEALL" Then
        myDocument.Shapes.Range(Array("RPEDR", "RPEDT", "RPEFR", "RPEFT", "RPECR", "RPECT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
    ElseIf rtarget.Name = "RPLALL" Then
        myDocument.Shapes.Range(Array("RPLDR", "RPLDT", "RPLFR", "RPLFT", "RPLCR", "RPLCT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
    ElseIf rtarget.Name = "WPEALL" Then
        myDocument.Shapes.Range(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
    Else
        myDocument.Shapes.Range(Array("WPLDR", "WPLDT", "WPLFR", "WPLFT", "WPLCR", "WPLCT")).Fill.ForeColor.RGB = RGB(255, 0, 0)
    End If

End Sub

The six values of the array are being placed in the proper location with your suggestion, however, they are followed by a series of 7 #N/A. These cells were blank prior to the transposing, so they aren't a result of any worksheet formula, nor coded formula. I'm not sure where the #N/As came from, but they can't stay. As the purpose was to add other values to the column to form a list, it will be a problem.

Excel 2010
	T
1	Reports to
2	print
3	CUEDR
4	CUEDT
5	CUEFR
6	CUEFT
7	CUECR
8	CUECT
9	#N/A
10	#N/A
11	#N/A
12	#N/A
13	#N/A
14	#N/A
15	#N/A
16	#N/A
17	#N/A
18	#N/A
varhold

 

[Ark68]

I think I resolved the issue with the extraneous #N/A. "nextrow" was getting it's value from the wrong worksheet, so ...

nextrow = wshvar.Cells(Rows.Count, "T").End(xlUp).Row + 1

appears to fix it.

However ... now only the 1st value in the array, "CUEDR" is populating the destination at T3. The remaining 5 are not displayed.

nextrow = wshvar.Cells(Rows.Count, "T").End(xlUp).Row + 1
    If rtarget.Name = "CUEALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            MsgBox "Caller: " & rtarget.Name
            myDocument.Shapes.Range(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT", "CUEALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T3:T" & nextrow) = Application.Transpose(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT"))
        Else
            myDocument.Shapes.Range(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT", "CUEALL")).Fill.ForeColor.RGB = RGB(255, 255, 255) 'ON to OFF
            MsgBox "OFF"
        End If

The code, as a reminder is to paste the array starting at the next open cell after T3.