Move data from on column to another

[lucas50nj]

Hello,
I am new to macros and this may be a simple question, hope you can help. PLEASE!! Thanks!

I'm trying to search for any string with a ":" from column M and move it to column D but start at a specific row based on a search i have. For example:

This finds kis emr and returns the row.

Dim rngSearch As Range, rngFound As Range
Set rngSearch = Range("a:a")
Set rngFound = rngSearch.Find(What:="kis emr", LookIn:=xlValues, LookAt:=xlPart)

This finds the ":" and moves it to the row found above but the problem is that when it finds every occurrence of ":" it overwrites the previous move. I need it to increment a row down before it finds the next ":" and move it.

If Range("m" & row).Value Like "*:*" Then 'if found then move it to d column

Range("d" & rngFound.row).Value = Range("m" & row).Value
'Range("d" & row).Value = Range("m" & row).Value
Range("m" & row).Value = ""

End if

 

[lucas50nj]

Can anyone please assist me?

 

[Forever_EX]

I'm sorry I am not advanced enough to help you exactly but I am pretty sure there is a function you can put in the macro called "FindNext" or something?

Maybe this thread can help: https://www.mrexcel.com/forum/excel-questions/584509-pasting-next-blank-row-vba.html

 

[Fluff]

Hi & welcome to the board.

How about

Sub FndCopy()
Dim Fnd As Range, Fnd2 As Range
Dim Qty As Long, i As Long

Set Fnd2 = Range("M1")
Set Fnd = Range("A:A").Find("kis emr", , xlValues, xlPart, , , , , False)
Qty = Application.CountIf(Range("M:M"), "*:*")
For i = 1 To Qty
   Set Fnd2 = Range("M:M").Find(":", Fnd2, , xlPart, , , , , False)
   Fnd.Offset(i - 1, 3).Value = Fnd2.Offset(, -9)
Next i
End Sub

 

[lucas50nj]

Hi Fluff, thank you for your response and welcome.
Your code does locate the items with a : but can you tell me how to move it to column d row number which is labeled KIS EMR in column A? Also please let me know once it writes the first one with a : then move down to cell below so it won't overwrite it?

 

[lucas50nj]

Thank you Forever_EX,
I am familiar with the findnext and tried it but does not work for me. I will look at the link you sent to see if I can use anything there.
Thanks!!!

 

[Fluff]

but can you tell me how to move it to column d row number which is labeled KIS EMR in column A? Also please let me know once it writes the first one with a : then move down to cell below so it won't overwrite it?

My code should already do that. If it's not can you please let me know what it is doing?

 

[lucas50nj]

Fluff,
I'm trying to do a simple find with anything with a : (ex. Weekly: , Monthly: or Ongoing:. Once I find this then I copy whatever pertains to these into another cell (E).
So this is after my macro finds the cells with a : in column M and moves it to column D after is locates KIS EMR on column A.

[TABLE="width: 722"]
<tbody>[TR]
[TD="class: xl63, width: 137"]KIS EMR[/TD]
[TD="class: xl63, width: 137"]Application Development[/TD]
[TD="class: xl63, width: 137"]Susan Kistler[/TD]
[TD="width: 174"]Ongoing:[/TD]
[TD="class: xl66, width: 137"]5/23/18 Activated the monitor account for Marlene. Monitor here 5/23. [/TD]
[/TR]
</tbody>[/TABLE]

The column where it says Ongoing came from col M and the one starting with 5/23 also came from column M which is the content for Ongoing. My problem is when my macro reads and finds the : ( ie weekly: and Monthly first, it sees weekly first copies over than it finds monthly copies that over and lastly finds ongoing and copies that over. monthly overwrites weekly and ongoing overwrites monthly. I need it to go down a cell so it does not overwrite. Hope this is clearer.
Thanks for your help!!!

 

[Fluff]

My code should not be overwriting the previous value, but for some reason I had it copy the value from col D not col M. It should be

For i = 1 To Qty
   Set Fnd2 = Range("M:M").Find(":", Fnd2, , xlPart, , , , , False)
   Fnd.Offset(i - 1, 3).Value = [COLOR=#ff0000]Fnd2.Value[/COLOR]
Next i

 

[lucas50nj]

Awesome this work!! Cool!.
Thank you Fluff!!!!!