PDA

View Full Version : Loop Conditional Statement through 2 columns and Copy/Paste from corresponding column



dmjackal_200
08-26-2008, 12:10 PM
Hello all,

First of all I am a newby programmer who is trying to teach myself Excel VBA. I have a problem at work where I would like to create a macro to perform the steps listed below. Bascially running a conditional statement down the spreadsheet and if columns R and Q meet the condition copy/paste corresponding data from column F to the first open cell in Temp worksheet. I would really appreciate any help. This would be my first program and I cannot get it to work. Thanks

Sub NewNumber()
'Run Loop Through Column Q and R in Active Sheet, If column Q cell is
' "blank" and column R cell is "blank" or contains a "N" copy and paste
' Corresponding Column F cell into temp worksheet.

Dim MyRange As Range
'Varaible my range containing columns Q,R,and F


For Each cell In MyRange("Q:Q" & "R:R" & "F:F")
'For each cell in MyRange run loop

If (Qcell = "" & Rcell = "N" Or "") Then Fcell.Select
Selection.Copy
Sheets("Temp").Range("A1").Select
ActiveSheet.Paste

'Paste contents into first cell in temp
Next

End Sub

Thanks
dmjackal_2000@yahoo.com

Bob Phillips
08-26-2008, 01:26 PM
Sub NewNumber()
'Run Loop Through Column Q and R in Active Sheet, If column Q cell is
' "blank" and column R cell is "blank" or contains a "N" copy and paste
' Corresponding Column F cell into temp worksheet.

Dim cell As Range
Dim Target As Range

Set Target = Sheets("Temp").Range("A1")
For Each cell In Range(Range("F1"), Range("F1").End(xlDown))

'Offset(0, 11) is Q, Offset(0,12) is Q
If cell.Offset(0, 11).Value = "" And _
(cell.Offset(0, 12).Value = "N" Or cell.Offset(0, 12).Value = "") Then

cell.Copy Target
Set Target = Target.Offset(1, 0)
End If
Next

End Sub