using a cells value as part of vba formula

*benj · 07-30-2010, 04:22 AM

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

  Range("A10").Select
    ActiveCell.FormulaR1C1 = _
        "=MATCH(R[-7]C[10],'Competitors and Irradiance'!C[12],1)"
   
    Sheets(1).Select
    range("R"& cell(A10).value"C2:R"& cell(A10).value"C11").select
    Selection.Copy

thanks in advance for any help with this

**Bob Phillips** · 07-30-2010, 04:31 AM

Try this

  Range("A10").FormulaR1C1 = _
  	"=MATCH(R[-7]C[10],'Competitors and Irradiance'!C[12],1)"
  
  Sheets(1).Cells(Range(A10).Value, "C2).Resize(, 10).Copy

**DonkeyOte** · 07-30-2010, 04:31 AM

Assuming you do need the formula in A10

Sheets(1).Cells(Sheets(2).Range("A10"),2).Resize(,10).Copy

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** · 07-30-2010, 04:33 AM

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 · 07-30-2010, 04:52 AM

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** · 07-30-2010, 08:17 AM

Originally Posted by *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

Missing a " after C2