macro and autofill?

[awsumchillicrab]

I'm trying to record a macro that autofilters, then puts a formula into the top cell of a column and autofill down. But this doesn't seem to work.

Is it possible?

 

[Weaver]

In vb, you rarely need autofill if you already know what the formula should be, you can write it directly to the range of cells as required.

If you want the formula to only appear in filtered (i.e visible) rows then you need to use the .specialcells(xlcelltypevisible) object.

Publish your code to give some idea of how this could be done.

HTH

 

[awsumchillicrab]

i don't know how to write vb code, so I was using the record function instead.

the grand scheme i'm trying to fulfill is this:

I have a column A with a Countif function. I want my macro to autofilter the Countif column to "1", then apply my formula in column B's first visible cell and autofill down. Then I want my macro to select "2" in the Countif column and apply a different formula to column C...(or if it's easier, the insert column between A and B, and put my 2nd formula in the new column B). Wanna go on until all my Countif values are accounted for.

right now, a test macro I tried looks like this:

***
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$F$21").AutoFilter Field:=1, Criteria1:="1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "a"
Range("B2").Select
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("B2").AutoFill Destination:=Range("B2:B" & LastRow)
Range("B2:B17").Select
***

The red text wasn't actually recorded. I found it in the forum and put it in myself, though it just autofills everything ignoring the autofilter.

 

[Weaver]

Just wondering, is the formula actually "a" or have you dropped that in as an example? If it is, this isn't the way I'd go.

Try this instead:

Sub countif()
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    rs = "R2C1:R" & lr & "C1"
    With Range("B2", Cells(lr, "B"))
        .FormulaR1C1 = "=CHAR(COUNTIF(" & rs & ",RC[-1])+95)"
        .Value = .Value
    End With
End Sub

If the "a" is just an example, a slightly different approach might be required, but I still think we can avoid the filtering.

 

[awsumchillicrab]

I'm not sure how to modify that formula, so i put the whole thing in as a macro. It filled all my cells in column B with "c", up til the last row where column A has a value.

And yeah, the "a" is just a test actually. The real one would be a bunch of IF statements which gives me another headache since the company's Excel 2003 only supports 7 nested IFs and I gotta figure a way round that.

I tried recording the macro a different way and I think it worked a bit better. I didn't use autofill, but copied and pasted into visible cells. My code looks like this:

Sub Macro1()

Range("I1".Select
ActiveCell.FormulaR1C1 = "count = 1"
Rows("1:1").Select
Range("C1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:="1"
Sheets("sg").Select
Range("I531").Select
ActiveCell.FormulaR1C1 = "=RC[-4]"
Range("I531").Select
Selection.Copy
Range("I531").Select
Range(Selection, Selection.End(xlDown)).Select
Range("I531:I52056").Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Range("J531").Select
Application.CutCopyMode = False
Selection.AutoFilter

End Sub


Doesn't feel like an elegant way, and definitely has one problem: The I531 and I52056 cells won't always be those numbers. I'd need them replaced by "First visible cell of Column I" and "Last visible cell of Column I which has a non-blank in Column H".

 

[Weaver]

I'm not sure how to modify that formula, so i put the whole thing in as a macro. It filled all my cells in column B with "c", up til the last row where column A has a value.

That suggests to me most of your items appear 3 times.

I still don't think the filter method is the right way to go with this in any case. What you should do is create a table in another sheet with col A showing the result of the countif and column b containing the formula, but without the = at the beginning, so it does'nt calculate

Then run down the list 1 row at a time and insert the appropriate formula from this lookup table.