using a cells value as part of vba formula

[*benj]

Hi,
I need vba to perform a match function in one cell and to then use the value produced in this cell to identify the row from which to copy a number of cells.

Getting vba to write the match code into a cell is no problem, but how do i then use this value as the row number in the below range( ).select

something like... range("R"& cell(A10).value"C2:R"& cell(A10).value"C11").select ...only this doesn't seem to work

Please Login or Register  to view this content.

thanks in advance for any help with this

[Bob Phillips]

Try this

Please Login or Register  to view this content.

[DonkeyOte]

Assuming you do need the formula in A10

Please Login or Register  to view this content.

You don't specify the sheet on which the A10 formula resides - in the above have assumed this is 2nd sheet - modify as nec.

It's also not clear what the remainder of the routine is and whether or not use fo clipboard is required.

[royUK]

Seems over complicated. Why not attach ann example workbook with what you want to do & I'm sure we can post a better way

[*benj]

thanks donkeyote that worked perfectly... I'm beginning to get the hang of macros but now and then something trips me up!

bob phillips your solution looks pretty similar tho it came up with an error (expected list separator) when I inserted it into my code so I guess some ) or " missing?

Thanks for all the responses
ben

[Bob Phillips]

thanks donkeyote that worked perfectly... I'm beginning to get the hang of macros but now and then something trips me up!

bob phillips your solution looks pretty similar tho it came up with an error (expected list separator) when I inserted it into my code so I guess some ) or " missing?

Thanks for all the responses
ben

Missing a " after C2