loop + array: complex for me :(

[choubix]

hello,

I still have my array problem...
here is the situation:

I have spreadsheet 1 with column A to AP full of data

in column G I need to find the cells that are filled in pink (conditional formatting, some cells are empty though)

for each cell which is filled in pink I need to copy/paste in spreadsheet 2 the data of colum A, B, D, F, G, K and L.

right now I have this:

[VBA]
Sub ELN_Restructuring()
Dim rCl As Range
Dim lColor As Long

'Set the cell color that you look for
lColor = 38 'pink

'Cycle through the range in R_ELN worksheet
For Each rCl In Sheets("R_ELN").Range("G:G")
If rCl.Interior.ColorIndex = lColor Then

'MsgBox rCl
End If
Next rCl

End Sub
[/VBA]

I used the msgbox to check I was retrieving the correct info. and it works.
can someone point me in the right direction so I can copy/paste the values of A, B, D, F, G, K and L in spreadsheet 2 please?

(I am considering 2 loops -for i and for j - and 1 array (array(i,j) )


thanks!

[Charlize]

What about this one :[VBA]Sub ELN_Restructuring()
Dim rCl As Range
Dim lColor As Long
'Worksheet to start
Dim wsstart As Worksheet
'Worksheet to copy to
Dim wsdest As Worksheet
'Set the cell color that you look for
lColor = 38 'pink
Set wsstart = Worksheets("R_ELN")
Set wsdest = Worksheets(2)
'Cycle through the range in R_ELN worksheet
For Each rCl In wsstart.Range("G1:G" & _
wsstart.Range("G" & Rows.Count).End(xlUp).Row)
If rCl.Interior.ColorIndex = lColor Then
'A, B, D, F, G, K and L
With rCl
Union(.Offset(, -6), .Offset(, -5), .Offset(, -3), .Offset(, -1), _
.Offset(, 0), .Offset(, 4), .Offset(, 5)).Copy '-6 = A
End With
wsdest.Range("A" & wsdest.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).Row).PasteSpecial xlPasteAll
End If
Next rCl
Application.CutCopyMode = False
MsgBox "Necessary moves are done !", vbInformation
End Sub[/VBA]Charlize

[Bob Phillips]

Conditional formatting doesn't set the cell interior colour.

[choubix]

hello!

thanks, that works like a charm

just a couple of quick questions so I can use these techniques later on:

the range starts at G1 but the remaining code is not too clear for me: does it stop when there is a blank cell or does it take into account all the cells?

[VBA] For Each rCl In wsstart.Range("G1:G" & _
wsstart.Range("G" & Rows.Count).End(xlUp).Row) [/VBA]


the effect of the following code is to select the cells in columns A, B, D, F...?
[VBA] With rCl
Union(.Offset(, -6), .Offset(, -5), .Offset(, -3), .Offset(, -1), _
.Offset(, 0), .Offset(, 4), .Offset(, 5)).Copy '-6 = A
End With
[/VBA]

I don't get how the data is pasted in the other worksheet... (code looks a bit similar to the one used to select the column G -first question)

[VBA] wsdest.Range("A" & wsdest.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).Row).PasteSpecial xlPasteAll
[/VBA]


really appreciate your help
thanks!

[Charlize]

[VBA]wsstart.Range("G1:G" & _
wsstart.Range("G" & Rows.Count).End(xlUp).Row)[/VBA]
wsstart = the worksheet from where to copy
.Range or .Cells can be used to define the cells to be processed
The code will run until the last row of column G where something is filled in. You could find the last row first with a find routine to determine the real last cell of the worksheet.
[VBA]With rCl
Union(.Offset(, -6), .Offset(, -5), .Offset(, -3), .Offset(, -1), _
.Offset(, 0), .Offset(, 4), .Offset(, 5)).Copy '-6 = A
End With[/VBA]No selection, just defining what we want to copy. Since you want to copy a non continuous set of columns, you can't use resize.
If you wanted to copy from column A to G you could use [VBA]Offset(,-6).Resize(1,7)[/VBA].
[VBA]wsdest.Range("A" & wsdest.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).Row).PasteSpecial xlPasteAll
[/VBA]wsdest = the destination worksheet
We want to paste to column A. We look for the last row but we need it to paste a row lower. That's where Offset(row, column) comes in.

Charlize