Aply a formula on a range

[Philippe31]

Hi everyone. I'm new in VBA and i have a issue with VBA Code
I try to copie a formula on a range of cell but it's doesn'T work,
Can you help me.
It a index formula
I want the index formula to apply for the entire range.
I don't know if I went about it the right way.

Sub paste_data()


Dim lastCell As Range
Dim targetRange As Range

Set lastCell = Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1)
Set targetRange = Range(lastCell, lastCell.Offset(200, 0))

targetRange.FormulaR1C1 = "=IFERROR(INDEX(Billing_Summary!R1C1:R999C2,MATCH(RC3,Billing_Summary!R1C1:R999C1,0),14),"""")"

End Sub

Thank for helping
I

 

[Philippe31]

Recheche_valeur_www.xlsm
	A	B	C	D	E	F	G	H	I	J
3		WP	CO		test	Forecast	Forecast	Forecast	Forecast
4		Work Package	Firm	CLEF	BASELINE	mars	Avril	mai	juin
5	2	WP1	xx	2WP1xxert		$123	$123	$123
6	3	ghdfgh	xx	3ghdfghxxert
7	4	WP1	xx	4WP1xxert					$13
8	5	WP1	jlp	5WP1jlpert					$15
9	6	WP1	xx	6WP1xxert					$16
10	7	WP1	xx	7WP1xxert					$17
11	8	WP1	xx	8WP1xxert					$18
12
13
14
WBS

Cell Formulas
Range	Formula
I7:I14	I7	=IFERROR(INDEX(BS!$A$1:$ZZ$17,MATCH($A7,BS!$A$1:$A$17,0),14),"")

 

[Philippe31]

Recheche_valeur_www.xlsm
	A	B	C	D	E	F	G	H	I	J
3		WP	CO		test	Forecast	Forecast	Forecast	Forecast
4		Work Package	Firm	CLEF	BASELINE	mars	Avril	mai	juin
5	2	WP1	xx	2WP1xxert		$123	$123	$123
6	3	ghdfgh	xx	3ghdfghxxert
7	4	WP1	xx	4WP1xxert					$13
8	5	WP1	jlp	5WP1jlpert					$15
9	6	WP1	xx	6WP1xxert					$16
10	7	WP1	xx	7WP1xxert					$17
11	8	WP1	xx	8WP1xxert					$18
12
13
WBS

Cell Formulas
Range	Formula
I7:I13	I7	=IFERROR(INDEX(BS!$A$1:$ZZ$17,MATCH($A7,BS!$A$1:$A$17,0),14),"")

 

[DanteAmor]

Did you try the macro that I put in post #8?
Please, You should try and read what I'm writing.


According to your example, the formula goes in row 5 and takes cell A7 as the first value to look for.
Try this:

Sub paste_data()
  Dim lastCell As Range
  Dim targetRange As Range
  
  Set lastCell = Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1)
  Set targetRange = Range(lastCell, lastCell.Offset(200, 0))
  
  targetRange.Formula = "=IFERROR(INDEX(BS!$A$1:$ZZ$999,MATCH($A7,BS!$A$1:$A$999,0),14),"""")"
End Sub

Change the 999 to the row number you want.


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Philippe31]

Did you try the macro that I put in post #8?
Please, You should try and read what I'm writing.


According to your example, the formula goes in row 5 and takes cell A7 as the first value to look for.
Try this:

Sub paste_data()
  Dim lastCell As Range
  Dim targetRange As Range
 
  Set lastCell = Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1)
  Set targetRange = Range(lastCell, lastCell.Offset(200, 0))
 
  targetRange.Formula = "=IFERROR(INDEX(BS!$A$1:$ZZ$999,MATCH($A7,BS!$A$1:$A$999,0),14),"""")"
End Sub

Change the 999 to the row number you want.


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Oh my god.

It's a long sheet. The formula work, but it's send the data to the last blank cell (coloum DD) and i want to the first blank cell (it's AM column in my sheet)!

Can you help and really sorry !

I have 62 times copies the data to the last blank cell. I propably continue until i dead !

 

[DanteAmor]

Set lastCell = Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1)

That line of the macro looks for the last column with data based on row 5.

If it is not row 5, then change the 5 to the other row number, perhaps 4 where you have your headers.

It is difficult to follow you since you are constantly changing information.

What I can suggest is that you review your sheet and delete everything to the right of column J and try again.