Editing existing macro help

Hi, I have tried some modifcations based on your test book.

You can test the example book I posted and see how it looks when you press the button.

Let us know, Thank You Mike.

Hello Mike many thanks for the help, It doesnt work 100%,in the example/macro you provided it inputs the target word in colomn C. Where as for the representative data (row 110 onward) it needs to input in colomn D. It doesnt matter if the macro messed up the non representative data, as this was only provided to show how the first macro provided worked.

Hope that makes sence.

Many thanks

Alan

Hi, Try this updated version.

Let me know.

Thank You,
Mike

Hello real nice guy, amazing work! Love it, is there anyway you can simplify it so that it doesnt do the top data (the top data was provided purly to show what data the orignal macro had been programmed to work for.)

I have reattatched a spreadsheet with only the data that is representative of my problem.

Many many thanks,

Alan

Ok...

Lets try again, let me know how this works for you...

Just press the button on the page.

Thank You, Mike

Hello RealNiceGuy, many thanks i have opened the macro that is used in the provided document. I notice that the "title pulling" is triggered after recognition of the word "Target." Or at least this is how i understand it. If this is the case, this will not work for my actual spreadsheet, as these words represent different words each time. I thought the generic "target word" would make this obvious I was hoping that recognition of this cell would be achieved by knowing that the cell 3 down/1 to the right would be "Title."

Also the target word is more of a target phrase, in that it may be multiple words and even contain brackets. But it will always be separated from the Miscellaneous junk text by greater than 5 spaces. :S Is it possible to recognise this as the end of the "Target Word"

I have seen coding recognising patterns in next before. (Simply wish I knew what it meant) See example below.

Sub snb()
    ReDim sn(6)
    
    c00 = [A38]
    sq = Split(c00)
    
    sn(0) = sq(0)
    sn(1) = Trim(Replace(Split(c00, "(")(0), sn(0), ""))
    sn(2) = Join(Filter(Filter(sq, "("), "+", False), "")
    sn(3) = Join(Filter(sq, "+)"), "")
    sn(4) = Trim(Split(Split(c00, sn(3))(1), "-")(0))
    sn(5) = Trim(Split(Split(Split(c00, sn(3))(1), "-")(1), "£")(0))
    sn(6) = Join(Filter(sq, "£"), "")
    
    Cells(40, 4).Resize(, 7) = sn
End Sub

Many thanks Alan

Hi Alan,

See how this modifcation works for you, Keep me posted.

Thank You, Mike

This code seems to do everything I want, but I have since found that it doesn't seem to exclusively look for if 3 rows down one column to the right = "title" If you look in the attachment i have edited one of the titles which also contains formatting similar to the "target word." It seems to then recognise this as the target object despite the cell 3 down, 1 to the right not being "title."

The attached spread displays this problem better than I can explain it.

I do believe this will be the final hoop through which we will be needing to jump, as manually moving these false targets one cell to the right makes it work perfectly on my real spread.

Cant thank you enough, (obviously your reputation will be amended as such)

All the best

Alan

p.s.

I have highlighted the cells which cause the macro to not work, interesting to note it only causes this undesired effect if the highlighted erroneous cells are two cells below a new "targetword"

Its too easy to say but realniceguy, your a real nice guy. Many thanks for the help, problem solved. And reputation boosted for sure!

Cant thank you enough

Alan