Change cell formula & assign to VBA variable...

[Kevin Lyons]

Hello,

I need some help changing my code to assign a current cell's formula to a
VBA variable:

Cell formula:

=ADDRESS(MATCH(VALUE(RIGHT(I17,1)),'1st'!$C4:'1st'!$C13,0)+3,MATCH(VALUE(RIGHT(I16,1)),'1st'!$D3:'1st'!$M3,0)+3,4)

VBA variable assignment:

Dim name1st As Range
Dim name2nd As Range
Dim sheet1st As Range
Dim sheet2nd As Range

Set name1st = Range("I17")
Set name2nd = Range("I16")
Set sheet1st = Worksheets("1st").Range("C4:C13")
Set sheet2nd = Worksheets("1st").Range("D3:M3")

'When I attempt to rewrite the following code, I am getting an 'Object
required' error on this line:

color1st = Address(Match(Value(Right(name1st, 1)), sheet1st, 0) + 3,
Match(Value(Right(name2nd, 1)), sheet2nd, 0) + 3, 4)

I appreciate any help in getting this written correctly.

Thanks much,

Kevin