Thread: How turn this excellent code into a macro

=MATCH(A1,B1:B5,0)
=ADDRESS(A2,2)
=HYPERLINK("#"&A3,"this message text is not required in the macro")

The above, when put into worksheet goes to cell where data matches.
eg A1=eggs. In col b eggs is in cell B4, so clicking on hyperlink takes one to cell b4

So how to make a macro out of this to copy data from any other cell into the matched
cell, eg cell B4 if it is a match. Reference data, obviously, in cell A1 is variable.
The hyperlink code above has a message area but that would not be necessary for a macro.

If it is not possible doing it this way, suggestions please.

Hi alfiebaby
can you run that past me again

to add to pike's request:

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Well, first, here's a ONE-CELL version of your three-cell process so you don't the values in A2 and A3...this formula does it all:

=HYPERLINK("#"&ADDRESS(MATCH(A1,B1:B5,0), 2), "this message text is not required in the macro")

Thanks for this! What a great little trick, I can think of several applications for this already.

Can you first of all explain your title? You have posted a formula not code, why do you need a macro?

Originally Posted by royUK

Can you first of all explain your title? You have posted a formula not code, why do you need a macro?

Okay, dunno the terminology as yet, only started writing a program a few months ago.
need macro so that i can copy data from a set range to any location in the spread sheet
after it has found where the data is.
a1=eggs (the reference data)

Find row eggs is in copy data from B1:Z1 starting at row and coll where eggs was found. eg (R20C4)

the next search and replace (or update of data in the row) may be dogs and in completely different row and so on eg (R40,C2). The constant is B1:Z1 (as an example)

The ability to do this with a macro opens up all sorts of time saving code writing possibilities.
I think that many would find this extremely useful code/formula

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Do not repost,but you haven't added a file. Check it is not too big for attaching. If it is remove unnecessary formatting and/or zip it

Not sure if this is what you mean