CODEWITHSUNDEEP
excelvba
Lee Read
Lee Read

Reputation: 39

Excel VBA - Search column for text in a String, Copy text into adjacent cells down

I'm attempting to simplify an excel sheet I work with on a weekly basis, my vba is improving however this problem has completely stumped me. I'm trying to create a VBA Macro that would do the following:

1 - Search Column B for the text "Associate", then when its found I want to copy the entire cell contents into an adjacent column filling it down where the data is related..aghh???

Essentially I want 1 table with rows that have complete information, so I can pivot it etc. Please see screenshots of before and after.

Before:
before After:
after

That is just a small sample of data that I have with 3 people, however I have nearly 100 people and cant carry on doing it manually as its driving me bonkers.

Any advice or help would be greatly appreciated, as I have no idea where to start with this problem.

Thanks

Upvotes: 0

Views: 704

Answers (1)

Tim Williams
Tim Williams

Reputation: 166136

Untested:

Dim c as range, a

for each c in Range(Range("B1"),cells(rows.count,2).End(xlUp))
    If c.Value like "Associate:*" then 
        a = c.value
    else 
        if c.value<>"" then c.offset(0,-1).value=a
    End If
next c

Upvotes: 0

Related Questions