Macro - Using a variable number of columns in a Range

[Guest]

I have a range of cells that I need to Autofill, however, the number of rows
changes on an irregular basis based on a different set of information.
Therefore, I need to somehow incorporate this in a macro...

In one cell I have the "CountA" formula to count the number of cells of a
particular type of information in a certain data set. This number will need
to be used in a macro to limit the number of rows the autofill will select
with a different dataset.

so something along the lines of:

Range("B44").Select
Selection.AutoFill Destination:=Range("B4(Sommaire!C10)"),
Type:=xlFillDefault
Range("B4(Sommaire!C10)").Select

The (Sommaire!C10) is where the value of the CountA formula is found.

I hope I made this clear enough,


Chris

 

[Dave Peterson]

Maybe...

Dim HowMany as long
howmany = worksheets("sommaire").range("c10").value

Range("B44").AutoFill Destination:=Range("B4" & howmany, _
Type:=xlFillDefault

You could even replicate the counta formula in your code:

Dim howmany as long
howmany = application.countif(somerangehere,somevaluehere)

and you may want to be specific picking up the data:

howmany = application.countif(worksheets("sommaire").range("a:a"), _
worksheets("someothersheet").range("a1").value)

 

[Dave Peterson]

I used =countif() in my sample code. You'd replicate the actual =counta()
formula you used.

Oopsie.