How to insert a formula in a dynamic range using VBA

[bbuk9999]

Hi all,
I am trying to fill a formula in dynamic cells. I am attaching the VBA file with an example to show what I want to do. In the attached file, ("Container Yard") sheet is the dynamic table and ("Yard Map") sheet includes the cells that I want to be filled with the formula. The formula that I want to use is in the ("Yard Map") sheet. I want this formula to be generated according to the generated dynamic table. The example in the file: the dynamic table is generated with 10 rows and 15 columns in ("Container Yard") sheet with values. In ("Yard Map") sheet the formula should be generated in cells with 15 columns and 5 rows, as this formula is used to see how much percent are the cells equipped by vales. Your assistance is appreciated.

[PAB]

Good evening bbuk9999,

Do you mean something like this?


Sub Cpy_Form()
' =========================================================================== ============
' Populate a 10 rows by 15 columns table with formulas.
' =========================================================================== ============
With Sheets("Yard Map")
Range("C2:Q" & Range("B" & Rows.Count).End(xlUp).Row).Formula = _
"=((COUNTA(" & _
"'Container Yard'!C2," & _
"'Container Yard'!C12," & _
"'Container Yard'!C22," & _
"'Container Yard'!C32," & _
"'Container Yard'!C42))/5)*100"
End With
End Sub

I hope this helps!

[bbuk9999]

Thanks PAB , but this is fixed, the one that I want should be dynamic as in the attached file. Thanks

[PAB]

I am not following you bbuk9999,

Are you trying to work out a percentage of the values covered or something else?

[PAB]

I think you are going to have problems because each section in the Container Yard sheet relates to FIVE different format types.

The first section appears to be formatted as number values not rounded from two decimal places to six decimal places.
The second section appears to be formatted as hours, minutes and seconds.
The third section appears to be formatted as years.
The fourth section appears to be formatted as number values rounded to whole numbers.
The fifth section appears to be formatted as text.

I think you need to explain this in MORE detail along with the expected results!

[bbuk9999]

Yes PAB, I am trying to work out a percentage of the values covered. Thanks

[bbuk9999]

I am trying to work out a percentage of the values covered in cells. Thanks for any assistance