Toddzilla24
New Member
- Joined
- Dec 18, 2017
- Messages
- 14
Hey guys,
I created a macro to pull a count of unique values from a list. I've been toying with it a bit, but can't seem to get it to function reliably. I just recently came back to working on it and continuously get this error: "The extract range has a missing or invalid field name." Basically I have a master sheet that contains all of the values, as well as a hidden sheet that pulls those values from the master and combines the values from separate cells into a single value in a different cell. None if this part is done in VBA. The VBA uses the advanced filtering function to spit out only the unique values, then another column uses a non-VBA countIF formula to get the quantities of these unique values. Then VBA again, pastes the count and unique values into the master sheet and re-hides the originally hidden sheet etc.
I've attached my code below for this. The red is where I'm getting the error currently. The only common solution I can find is that the headings are not set up properly. I know that this should be the case for me, since I have been successful with this, the way the headings are currently set up (the extract and output columns have the exact same header).
I'm also wondering if maybe I'm doing this in a way that's more complicated than it needs to be. Any advice would be appreciated. Thanks in advance.
I created a macro to pull a count of unique values from a list. I've been toying with it a bit, but can't seem to get it to function reliably. I just recently came back to working on it and continuously get this error: "The extract range has a missing or invalid field name." Basically I have a master sheet that contains all of the values, as well as a hidden sheet that pulls those values from the master and combines the values from separate cells into a single value in a different cell. None if this part is done in VBA. The VBA uses the advanced filtering function to spit out only the unique values, then another column uses a non-VBA countIF formula to get the quantities of these unique values. Then VBA again, pastes the count and unique values into the master sheet and re-hides the originally hidden sheet etc.
I've attached my code below for this. The red is where I'm getting the error currently. The only common solution I can find is that the headings are not set up properly. I know that this should be the case for me, since I have been successful with this, the way the headings are currently set up (the extract and output columns have the exact same header).
Code:
Sub AHUValveCount() Application.ScreenUpdating = False
'
' AHUValveCount Macro
'
'
Dim VCWS As Worksheet
Dim VOWS As Worksheet
Dim rownumber As Integer
Dim copyrng As Range
Set VOWS = Worksheets("AHU Valves - Master")
Set VCWS = Worksheets("AHU Valve Counter")
With VOWS
.Unprotect Password:="###"
End With
With VCWS
.Visible = True
.Unprotect Password:="###"
.Range("I5:I50").ClearContents
[COLOR=#ff0000] .Range("G4:G50").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _[/COLOR]
[COLOR=#ff0000] "I3:I50"), Unique:=True[/COLOR]
End With
rownumber = VCWS.Application.WorksheetFunction.CountIf(Range("H5:H55"), "<>0") - 1 + 4
Set copyrng = VCWS.Range("H5:I" & rownumber)
copyrng.Copy
With VOWS
.Range("C62:D111").ClearContents
.Range("C62").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Protect Password:="###"
End With
Application.CutCopyMode = False
With VCWS
.Unprotect Password:="###"
.Visible = False
End With
Application.ScreenUpdating = True
End Sub
I'm also wondering if maybe I'm doing this in a way that's more complicated than it needs to be. Any advice would be appreciated. Thanks in advance.