Putting Values of 3 columns into one column using If ElseIf statements

[Mavis Beacon]

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

[VBA] 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[/VBA]

Any help would be greatly appreciated!

[patel]

attach please a sample file with the current columns and the desired column

[Mavis Beacon]

I am trying to put the values of three columns (A, B + C in grey) into one column (F in blue) but I want to use reference cell addresses so I can put it in a loop. Once they are in the blue column, I then want to sort them from smallest to largest. I'm trying to do one bit at a time! Any advice would be a great.

[SamT]

Mavis,

The Select Method does not activate a range, so you are never changing the active cell.

It is bad practice, (because it is slow,) but you can just change the Select Method to the Activate Method.

It would be a faster procedure to declare a variable to hold the working cell and reset it's value to loop thru the column(s.)

[vba]
'Declare the working cell Variable
Dim Cel As Range
'Set the Variable to the starting point.
Set Cel = ActiveCell
.
.
.
'To loop
'Reset the variable to the next working cell.
Set Cel = Cel.Offset(1, 0)
[/vba]

I am not sure what you want to accomplish. Your code appears to be putting formulas into "F" (RT Range) that return values from a cell chosen from of one of the first three columns.

Your If-Then-ElseIf structure says two things at the same time:

1. If A is empty then check B, if B is empty then check C.
2. If the Cell checked, (A or b or C,) is less then 2 then paste formula in F.

This means that if "A" is not empty, don't check "B" even if "A" is greater than 2.

With the data in your sample Columns B and C will never be considered because Column A is never empty.


If that is NOT what you want, let us know.