VBA - range selection on repeating data
VBA - range selection on repeating data
I have the following sheet of data:
https://ethercalc.org/zeacfw3jskc3
The important thing about this data is that every column is repeated twice, one for type-A data, one for type-B. If there is no data in the first set of columns (up until revision), I want to create my range using the second rather than having the code fail. This is the code that attempts to do so:
Set w1 = wbk.Sheets("PositionsDB")
Set w2 = wbk.Sheets("Performance")
num_rows = w1.Cells(Rows.Count, 1).End(xlUp).row
'If there's no parentName column, we can't continue.
If w1.Rows(1).Find("portfolioName") Is Nothing Then Exit Sub
'find first instance
Set f1 = w1.Rows(1).Find("portfolioName", lookat:=xlWhole)
If Not f1 Is Nothing Then
'find second instance
Set f2 = f1.Offset(0, 1).Resize(1, w1.Columns.Count - f1.Column).Find("portfolioName", lookat:=xlWhole)
If Not f2 Is Nothing Then
'set range based on f2
Set parentRange = w1.Range(f2.Offset(1, 0), w1.Cells(Rows.Count, f2.Column).End(xlUp))
End If
End If
'If there's no Root level, how do we know where to start?
If parentRange.Find("Main") Is Nothing Then Exit Sub
Debug.Print "test1"
Debug.Print parentRange.Width
Debug.Print parentRange.Height
Debug.Print "test2"
For Each parent In parentRange
If Not dict.Exists(parent.Value) Then
childCount = Application.WorksheetFunction.CountIf(parentRange, parent.Value)
Set childrenRange = parent.Offset(, 2).Resize(childCount, 1)
dict.Add parent.Value, Application.Transpose(Application.Transpose(childrenRange.Value))
End If
Next
However, when I run this on using the data in the sheet, I get the following error:
Variable Required - Can't assign to this expression
For Each parent In parentRange
It is strange, because I get this exception without any of my Debug.Prints showing up in the immediate console. Is there an issue with my code?
Parent
rng
Personally, I'd stick the two different ranges in two different arrays and loop through Entity ID column of one. If ISNULL(arr(i, whatevercolumnNumberEntityIdwas)) = True then arr2(i, whatevercolumnNumberEntityIdwas). I think you get the point. It would read a lot better I think. this is of course assuming the rows line up perfectly that is.
– Doug Coats
Sep 6 '18 at 14:18
@BigBen Parent is a reserved word. Explain what you mean under reserved? This code works fine:
Dim Parent As Range: Set Parent = Range("A1"): MsgBox Parent.Address.– JohnyL
Sep 6 '18 at 18:15
Dim Parent As Range: Set Parent = Range("A1"): MsgBox Parent.Address
2 Answers
2
Parent is not declared. Thus, VBA considers it to be of type Variant. To loop through a range, you need an object, and not a variant:
Parent
Variant
Sub TestMe()
Dim parent As Range
For Each parent In Range("A1:A5")
Debug.Print parent.Address
Next parent
End Sub
If parent is declared as a Variant, it is casted to an object immediately by VBA:
parent
Variant
To avoid all these kinds of problems - make sure that you always have Option Explicit on the top of your module/worksheet/class.
Option Explicit
As a second thought, naming a variable parent is really not a great idea, but it works.
parent
"To loop through a range, you need an object, and not a variant" - this is incorrect. Take off
As Range and your code will still work, even though parent then is a Variant– BigBen
Sep 6 '18 at 17:08
As Range
parent
Variant
@BigBen - see the screenshot.
– Vityata
Sep 6 '18 at 17:11
in OP -
Parent is not declared, but is not a Variant - there's a Compile error because of the confusion with the Parent property.– BigBen
Sep 6 '18 at 17:14
Parent
Variant
Parent
And even if OP had
Option Explicit, if Parent was not defined then the code would still not compile. I.e. Option Explicit wouldn't catch an undefined variable, rather you'd have the same Compile error.– BigBen
Sep 6 '18 at 17:32
Option Explicit
Parent
Option Explicit
@BigBen - the idea of mentioning
Option Explicit was that it would say immediately that there is a non-declared. But indeed, if the parent is non-declared, it is considered reserved word, hence getting the error from there.– Vityata
Sep 6 '18 at 17:40
Option Explicit
Parent is a reserved word. The Parent property of an object refers to that object's parent, e.g. a Range object has a Worksheet parent, and a Worksheet object has a Workbook parent.
Parent
Parent
Range
Worksheet
Worksheet
Workbook
The error:
Variable required - Can't assign to this expression
arises because the For Each...Next expects a variable used to iterate through the elements of the collection or array, and Parent is not a variable.
For Each...Next
Parent
Use another name, e.g. For each rng in parentRange.
For each rng in parentRange
EDIT:
Note that this Compile error specifically arises because:
Parent
Parent
Parent
Variant
As pointed out elsewhere, adding Option Explicit and Dim parent as Range, or even Dim parent, would eliminate the compile error, but might be misleading and is advised against.
Option Explicit
Dim parent as Range
Dim parent
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Parentis a reserved word. Use some other term, e.g.rng.– BigBen
Sep 6 '18 at 14:11