Paste special in vba (pste formula by creating an increasing series)

[keyra]

Dear reader,
I have a table with multiples columns from which the last one contains formulas. I have this macro(below) which helps me to make sure that this column with formulas has no blank cells. It copies and paste right with formula format but i need to paste them as an increasing series. For ex in the very first cell with formula located on row 11, i have cell BC11 as reference. for next rows 12, 13, 14 ,in the formula i need to have BC12, BC13, BC14. Could you please advise me how can i do it?

"dim name as string
For each c in range("B11:B" & cells(rows.count,2).end(xlup).row)
If cells(c.row,55)>" " Then
Name = cells(c.row,55).formula
Cells(c.row, 55).copy
Else
Cells(c.row,55).value=name
Cells(c.row,55).pasteSpecial (xlpasteformats)
End if
Next
end sub

Thank u so much for your help.

 

[baitmaster]

I can't see where you're pasting your formulas, only your formats

If you want to copy and then pasteSpecial(xlPasteAll) then your formulas will go across as well; correcting any "locking" (i.e. the use of $ to lock to certain cells) will ensure your formulas paste correctly

 

[keyra]

I can't see where you're pasting your formulas, only your formats

Let's take2 columns for example. A : has always values. B : has formulas but also it may have blank cells. For one blank cell from column B, i need to copy the formula from above cell. Right now it does that but if it takes the formula from row 11 it keeps me row 11 for each paste. I need to change it depending on what row the blank cell is. Can i do that?