Mavis Beacon
03-12-2013, 06:47 AM
Hello,
I have excel 2007.
I am trying to copy the values of 3 columns into one separate column with 2 conditions:
1) copy to active column if cell is not empty
2) copy to active column if cell value is under 2
The code runs but it only puts in the values of one column and then it stops. I have tried using 'loop' instead of 'loop until' but it still runs only on the first column. I can't seem to get it to move onto the next one??
This is the code I'm using
If ActiveCell.Offset(0, -5) <> vbNullString Then
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-5]>2,"""",RC[-5])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -5) = vbNullString
ElseIf ActiveCell.Offset(0, -4) <> vbNullString Then
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>2,"""",RC[-4])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -4) = vbNullString
ElseIf ActiveCell.Offset(0, -3) <> vbNullString Then
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-3]>2,"""",RC[-3])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -3) = vbNullString
End If
Any help would be greatly appreciated!
I have excel 2007.
I am trying to copy the values of 3 columns into one separate column with 2 conditions:
1) copy to active column if cell is not empty
2) copy to active column if cell value is under 2
The code runs but it only puts in the values of one column and then it stops. I have tried using 'loop' instead of 'loop until' but it still runs only on the first column. I can't seem to get it to move onto the next one??
This is the code I'm using
If ActiveCell.Offset(0, -5) <> vbNullString Then
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-5]>2,"""",RC[-5])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -5) = vbNullString
ElseIf ActiveCell.Offset(0, -4) <> vbNullString Then
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>2,"""",RC[-4])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -4) = vbNullString
ElseIf ActiveCell.Offset(0, -3) <> vbNullString Then
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-3]>2,"""",RC[-3])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -3) = vbNullString
End If
Any help would be greatly appreciated!