Hi guys, need some help here!

[maramos]

Hi guys,

Ik have found this it does almost what i need to do.

There is a formula row R11:Z11. VBA wil find the last empty rows and paste the formula there. The problem is that it does copy the exact row numbers starting row R11 then R12 etc.
I need it to just copy and use the row numbers where it is copied. I hope someone can help me with this.

Thanks,


Sub Fill_only_empty()

Dim LR As Long

LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

With Range("R12:Z" & LR)
With .SpecialCells(xlCellTypeBlanks)
.Formula = Range("R11:Z11").Formula
End With
.Value = .Value
End With

End Sub

 

[kevin9999]

What is the formula in the range R11:Z11?

 

[maramos]

yES

 

[kevin9999]

yES

That's not a formula. What is the formula in the range R11:Z11?

 

[maramos]

There is several formulas. All cell different formula.

 

[maramos]

Like: R11 =+YEAR(P11)&"-"&IF(MONTH(P11)<10;"0"&MONTH(P11);MONTH(P11))

 

[kevin9999]

Last try. What are the formulas in the range R11:Z11? You can use the XL2BB add in to show them.

 

[maramos]

I am going to add the add-in

 

[maramos]

KPI Carrier	Shipping Month	KPI line	KPI Carrier country	KPI Country	KPI All	KPI Country + Salesorg	KPI Carrier + Salesorg	Inv. Carrier + date
=IFERROR(VLOOKUP(H11;'C:\Users\maramos\Desktop\[FF - KPI T&T - Daily working file V4.xlsm]Carriers'!A:D;3;FALSE);"")	=+YEAR(P11)&"-"&IF(MONTH(P11)<10;"0"&MONTH(P11);MONTH(P11))	=+R11&S11&Q11	=+R11&S11&Q11&F11	=+IF(F11="SM";"IT";F11)&S11&Q11	=+S11&Q11	=+IF(F11="SM";"IT";F11)&S11&Q11&D11	=+T11&D11	=+R11&M11

 

[maramos]

This is the formula that has to be copy. Starting at R11:Z11