CODEWITHSUNDEEP
excelvba
Évariste Galois
Évariste Galois

Reputation: 1033

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?

Upvotes: 0

Views: 93

Answers (2)

BigBen
BigBen

Reputation: 50008

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.

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.

Use another name, e.g. For each rng in parentRange.

EDIT:

Note that this Compile error specifically arises because:

  1. You've not declared Parent, and
  2. This won't compile due to the confusion with the Parent property - Parent is not a Variant in this case - hence "variable required, can't assign to this expression."

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.

Upvotes: 2

Vityata
Vityata

Reputation: 43585

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:

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:

enter image description here

To avoid all these kinds of problems - make sure that you always have Option Explicit on the top of your module/worksheet/class.

As a second thought, naming a variable parent is really not a great idea, but it works.

Upvotes: 2

Related Questions