VBA copy formula from one cell to the next one

[mitenikolov]

Hello,

This is my Currently Excel Sheet
Cell DP3 contains a Formula that is "CONCATENATE" from another sheet with name "CODICI".

What I would like to do is

1) Copy the formula from the cell DP3 and paste it in cell DQ3 with the numbers + 1.
2) So i the cell DQ3 I would have the following formula: "=CONCATENATE(CODICI!$F$144;" - ";CODICI!$D$144)"

This is what I tried so far but it doesn't seem to work properly

Range("DP3").Select
Selection.Copy
Range("DQ3").PasteSpecial xlPasteFormulas

Is there a way to get manipulate the formula thought code? I already know how to store it into a String, but I don't know how to change it properly.
Thank you.

 

[MARK858]

Easier just to amend the formula then you can copy or drag across...

Book1
	DP	DQ
3	BBB - AAA	DDD - CCC
Sheet2

Cell Formulas
Range	Formula
DP3:DQ3	DP3	=CONCATENATE(INDIRECT("CODICI!F"&COLUMNS($A$1:EM1))," - ",INDIRECT("CODICI!D"&COLUMNS($A$1:EM1)))

Book1
	D	E	F
143	AAA		BBB
144	CCC		DDD
CODICI

 

[mitenikolov]

I've come accross an idea: Since I'm using VBA I would like to use this formula

Worksheets("Sheets").Range(myRange).formula = "=CONCATENATE(CODICI!""" & stringCodiceCella & """ """ & stringDesignazioneCella & """ )"

but I can't make it work. Note that "stringCodiceCella" and "stringDesignazioneCella" are Strings. Can you help me out with it. Thank you