Copy and Paste cell value based on another cells value

[DK340]

I need a macro that will loops through each value in column (K:K) and if the value, for example K1, is equal to "Credit Card", it needs to find the value in column B of the same row (in this example B1) and copies this value and then pastes this into cell A19. As it goes down through column K and finds more instances of "Credit Card" it pastes the associated data from column B in the A20,A21,A22 etc...until all the data in column B that is associated with "Credit Card" is in column A.

I hope this is clear enough to get some help.

Thanks!

 

[kevin lazell]

hello dk340
i have not tested the following give it a try and see if it is what you are looking for

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
y = 19: Z = 5000
Do
x = x + 1
If Range("k" & x) = "credit card" Then Range("c" & y) = Range("b" & x): y = y + 1
If x = Z Then Exit Sub
Loop
End Sub

cheers

kevin

 

[DK340]

That worked perfectly, thank you so much. I do have one additional question if you have the time. I now need to add to this a section where the code will look for "Debit Card" as well that the "Credit Card" section but if it is a Debit Card, have it paste in the same cell location but on Sheet2 and not on the sheet where I am pasting the Credit Card data.

Thanks in advance for your help with this.

 

[kevin lazell]

hello dk340
i have not tested the following give it a try and see if it is what you are looking for

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
y = 19: Z = 5000
Do
x = x + 1
If Range("k" & x) = "credit card" Then Range("c" & y) = Range("b" & x): y = y + 1
If Range("k" & x) = "debit card" Then sheets("sheet2").Range("c" & y) = Range("b" & x): y = y + 1

If x = Z Then Exit Sub
Loop
End Sub

cheers

kevin

 

[DK340]

Thanks for the help. I am getting an error on the follwing line:

If Range("k" & x) = "debit card" Then sheets("sheet2").Range("c" & y) = Range("b" & x): y = y + 1

The red area is highlighted in yellow in the debug process. I have messed around with this and I have not been able to get this fixed. If you have the time, I would appreciate your assistance as this, when fixed, is exactly what I need.

Thanks again.

 

[kevin lazell]

hi dk340
i messed around with that line of code and i got the same problem
i then realised that i had not given the y variable a value
when i corrected this it worked fine for me
so maybe you can check if your x and/or y variables have a value

cheers

kevin

 

[DK340]

Thank you very much for all your help. I have found the error and it works perfectly.

 

[koamapping]

Thank you very much for all your help. I have found the error and it works perfectly.

May I trouble to explain how to use your solution?
Is it a macro?
If so, will you please direct me to a tutorial on using macros?
Thank you for any guidance you provide

 

[Dkeller12]

The solution would be placed inside the Visual Basic Editor in excel and then can be run. This automates steps that would otherwise take hours manually.
Yes, this is macro (VBA) coding used in Excel.
Just google "How to create macros in excel" and you should have many links to learning macro coding.
Hope that helps!

 

[koamapping]

Thank you very kindly for your response.