Copy all data in Column K to Column J, BUT only if adjacent value in Column A = "Bananas"

[richardcarter]

Totally stuck with a macro which I thought would be easy at first.. but have tried lots of different ways code and Googling to find similar solved problem but not getting very far.. so any help appreciated,,, All I am trying do is just copy all the data in one column (Col K) to another column (Col J) BUT ONLY if another column (A) has a particular word in it, otherwise leave the data in Column K

Have tried:

If Range("A3:A48").Value = "Bananas"
Then
Range("J3:J48).Value = Range("K3:K48").Value
End If

But VBA not accepting that as valid code...

 

[VoG]

Try with a loop

For i = 3 To 48
If Range("A" & i).Value = "Bananas" Then
    Range("J" & i).Value = Range("K" & i).Value
End If
Next i

 

[richardcarter]

Thanks.. I tried that out on a few rows in a worksheet.. very zippy and fast to do that.. and was impressed! However, when I replicated the code in my actual worksheet (which has 3,500 rows)... it took quite a while (2 minutes to run)... and I need to run this process on about 10 columns.. so Im still open to any other code that might make this a bit zippier!

 

[VoG]

This should be faster

With Range("J3:J48")
    .Formula = "=IF(A3=""Bananas"",K3,"""")"
    .Value = .Value
End With

 

[richardcarter]

It is indeed!!! Many thanks for that... this formula method makes sense to me... although I havent quite figured out the correct usage of all the """""...'s

I'm just thinking ahead, and maybe for the benefit of other people that might need this, how would I change the code to move not one, but TWO.. or even THREE.. columns of data to the left (in other words Column K data goes to column J, and Column L data goes into J (as you can see, all I am doing is shifting columns of data to the left each time). .. thanks

 

[VoG]

There may be some looping method to do this in a few lines of code but you can essentially just repeat the code:

With Range("J3:J48")
    .Formula = "=IF(A3=""Bananas"",K3,"""")"
    .Value = .Value
End With
With Range("K3:K48")
    .Formula = "=IF(A3=""Bananas"",L3,"""")"
    .Value = .Value
End With
With Range("L3:L48")
    .Formula = "=IF(A3=""Bananas"",M3,"""")"
    .Value = .Value
End With

In VBA when you enter a formula with " present you have to double up all the " as ""

 

[richardcarter]

Perfect.. thanks Peter. I too was thinking along the lines of repeating the code as you mention for each column transfer, but I hadn't thought of slotting them all in between 'With.. End With'.... so Thanks again!

 

[richardcarter]

Hmmm.... just double checked the formulas.. it seems that if Column A DOESNT have Bananas written in it, then the cell values actually get deleted (I assume replaced with the phrase " ") rather than remain with their existing values. Ive tried playing around with the formula to rectify it, but nothing seems to work based on different configurations of "" and .value = .value... am I doing something wrong?