PDA

View Full Version : Putting Values of 3 columns into one column using If ElseIf statements



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!

patel
03-12-2013, 09:21 AM
attach please a sample file with the current columns and the desired column

Mavis Beacon
03-12-2013, 10:37 AM
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
03-12-2013, 11:49 AM
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.)


'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)


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:
If A is empty then check B, if B is empty then check C.
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.