PDA

View Full Version : [SOLVED] Trouble using selected range to define a for loop



davidmnix
12-29-2016, 01:13 PM
I am trying to select a range of cells, count the number of cells with data in that range, and then use that value to define a for loop. I am receving an error on the "k =" line.
I know this is probabaly an elementary issue, but I am new to VBA. Thank you for any help you can offer

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "FLlist"
ActiveSheet.Range("FLlist").RemoveDuplicates Columns:=1, Header:= xlYes
Dim k As Integer
k = Value.CountA(Range("FLlist"))
For j = 1 To k
Sheets("YTD Work Orders").Range("$A$11:$AK$1260").AutoFilter Field:=6, _
Criteria1:=Sheets("Summary by FL").Cells(1 + j, 1).Value
Cost_YTD = Sheets("YTD Work Orders").Range("C6")
Sheets("Summary by FL").Cells(1 + j, 2) = Cost_YTD
Next j

NoSparks
12-29-2016, 02:46 PM
CountA is not a VBA function
try this...


k = Application.WorksheetFunction.CountA(Range("FLlist"))

Aussiebear
12-30-2016, 12:32 AM
CountA is not a VBA function

As NoSparks quite rightly suggested, CountA is an Excel function rather than a VBA function

davidmnix
01-03-2017, 10:17 AM
NoSparks, Thank you for the help That worked perfectly!