PDA

View Full Version : loop + array: complex for me :(



choubix
06-03-2008, 03:00 AM
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:


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


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
06-03-2008, 03:45 AM
What about this one :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 SubCharlize

Bob Phillips
06-03-2008, 03:51 AM
Conditional formatting doesn't set the cell interior colour.

choubix
06-03-2008, 09:58 PM
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?

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


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


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)

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



really appreciate your help :)
thanks!

Charlize
06-03-2008, 11:42 PM
wsstart.Range("G1:G" & _
wsstart.Range("G" & Rows.Count).End(xlUp).Row)
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.
With rCl
Union(.Offset(, -6), .Offset(, -5), .Offset(, -3), .Offset(, -1), _
.Offset(, 0), .Offset(, 4), .Offset(, 5)).Copy '-6 = A
End WithNo 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 Offset(,-6).Resize(1,7).
wsdest.Range("A" & wsdest.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).Row).PasteSpecial xlPasteAll
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