PDA

View Full Version : Conditional selection in excel VBA?



silentsound
11-08-2009, 03:55 PM
Hi
I am new to VBA so consequently am not sure how to select cells based on the values of other cells.

I have a spreadsheet with values of 1 or 2 in column C. I would like to create a macro to copy A1 and B1 if the value of C1 is 1, or F1 if it is 2, and so on until the first blank cell in column C is reached.

Hope that's clear and thank you very much for any help!

Bob Phillips
11-08-2009, 04:27 PM
For i = 1 To Range("C1").End(xlDown).Row
If Cells(i, "C").Value = 1 Then
NextRow = NextRow + 1
Cells(i, "A").Resize(,2).Copy Worksheets("Sheet2").Cells(NextRow, "A")
ElseIf Cells(i, ""C").Value = 2 Then
NextRow = NextRow + 1
Cells(i, "F").Copy Worksheets("Sheet2").Cells(NextRow, "A")
End If
Next i

silentsound
11-11-2009, 12:36 PM
For i = 1 To Range("C1").End(xlDown).Row
If Cells(i, "C").Value = 1 Then
NextRow = NextRow + 1
Cells(i, "A").Resize(,2).Copy Worksheets("Sheet2").Cells(NextRow, "A")
ElseIf Cells(i, ""C").Value = 2 Then
NextRow = NextRow + 1
Cells(i, "F").Copy Worksheets("Sheet2").Cells(NextRow, "A")
End If
Next i


Thanks for that, although I don't think I explained what I needed clearly enough. The values in column C are grouped, i.e. all the 1s and 2s are together. What I need is to copy all the cells in columns A and B which have corresponding values of 1 in column C so that they can be manually pasted into another program. After that I then need to copy the cells in column F with corresponding values of 2 in column C so that they can also be manually pasted into another program. I presume this is only possible with some user interaction with the macro between the two e.g. MsgBox
Thanks again

geekgirlau
11-11-2009, 04:42 PM
It sounds like you could do this fairly simply with an advanced filter, where you opt to copy the resulting cells to another location.