Conditionally move data from two columns to another column

[JJS]

Hi All,

I have a Sheet where columns B,C and D will have a person's name based on the look up of data from another source. Not every cell is filled (if lookup does not return a match it leaves it blank) in each column.

I originally had this in Column A to compile the data:

=IF(AND(B2>0,C2="",D2=""),B2,IF(AND(B2="",C2>0,D2=""),C2,IF(AND(B2="",C2="",D2>0),D2,IF(AND(B2>0,C2>0,D2>0),C2,IF(AND(B2>0,C2>0,D2=""),C2,IF(AND(B2="",C2>0,D2>0),C2,IF(AND(B2>0,C2="",D2>0),D2,""))))))

I need to incorporate something like this into a macro but the formula is obviously too long to be recorded.

How can I accomplish the same thing in a macro?

Jeff

 

[John Davis]

Hi All,

I have a Sheet where columns B,C and D will have a person's name based on the look up of data from another source. Not every cell is filled (if lookup does not return a match it leaves it blank) in each column.

I originally had this in Column A to compile the data:

=IF(AND(B2>0,C2="",D2=""),B2,IF(AND(B2="",C2>0,D2=""),C2,IF(AND(B2="",C2="",D2>0),D2,IF(AND(B2>0,C2>0,D2>0),C2,IF(AND(B2>0,C2>0,D2=""),C2,IF(AND(B2="",C2>0,D2>0),C2,IF(AND(B2>0,C2="",D2>0),D2,""))))))

I need to incorporate something like this into a macro but the formula is obviously too long to be recorded.

How can I accomplish the same thing in a macro?

Jeff

Why not copy the formula and paste it in a macro:

Dim lr as long

lr = cells(rows.count,1).End(xlUp).row

With Range("A2:A"&lr)
.Formula = "=IF(AND(B2>0,C2="""",D2=""""),B2,IF(AND(B2="""",C2>0,D2=""""),C2,IF(AND(B2="""",C2="""",D2>0),D2,IF(AND(B2>0,C2>0,D2>0),C2,IF(AND(B2>0,C2>0,D2=""""),C2,IF(AND(B2="""",C2>0,D2>0),C2,IF(AND(B2>0,C2="""",D2>0),D2,""""))))))"
.value = .value
End With