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:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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
 

richardcarter

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

Legend
Joined
Jun 19, 2002
Messages
63,651
This should be faster

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

richardcarter

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

Legend
Joined
Jun 19, 2002
Messages
63,651
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 ""
 

richardcarter

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

Board Regular
Joined
Dec 10, 2003
Messages
77
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?
 

Forum statistics

Threads
1,082,334
Messages
5,364,681
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top