Convert a portion of a formula from absolute to relative

[mjl78]

I'm writing a macro and am copying a formula, that is absolute, from a cell one worksheet, to another cell on another worksheet, The formula is as follows
=IF(AND(Input!$C$3="",Input!$D$3=""),"",IF($F$3="cash","cash",IF(Input!$C$3>0,Input!$C$2,Input!$D$2)))

Once pasted I need to convert the formula to the following
=IF(AND(Input!c3="",Input!D3=""),"",IF(F3="cash","cash",IF(Input!C3>0,Input!$C$2,Input!$D$2)))

Basically I need to convert characters C3, D3, and F3 to relative, while leaving characters $C$2 and $D$2 as absolute. Does anyone know the vba code to accomplish this (using excel 2007)

Thanks so much

Marty

 

[Domenic]

The following macro copies the formula from one cell to another, then it converts all references to a relative reference, and then it converts references to C2 and D2 to absolute references...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] SourceCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] DestCell [color=darkblue]As[/color] Range
    
    [color=darkblue]Set[/color] SourceCell = Sheets("Sheet1").Range("A1")
    [color=darkblue]Set[/color] DestCell = Sheets("Sheet2").Range("A1")

    SourceCell.Copy Destination:=DestCell
    
    DestCell.Formula = Application.ConvertFormula(DestCell.Formula, xlA1, , xlRelative)
    
    DestCell.Formula = Replace(Replace(DestCell.Formula, "C2", "$C$2"), "D2", "$D$2")
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[mjl78]

THANKS...Worked like a charm

 

[Domenic]

You're very welcome! Thanks for the feedback!