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

richardcarter

Board Regular
Joined
Dec 10, 2003
Messages
77
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...
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try with a loop

Code:
For i = 3 To 48
If Range("A" & i).Value = "Bananas" Then
    Range("J" & i).Value = Range("K" & i).Value
End If
Next i
 
Upvote 0
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!
 
Upvote 0
This should be faster

Code:
With Range("J3:J48")
    .Formula = "=IF(A3=""Bananas"",K3,"""")"
    .Value = .Value
End With
 
Upvote 0
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
 
Upvote 0
There may be some looping method to do this in a few lines of code but you can essentially just repeat the code:

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 ""
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top