VBA Excel trouble specificing range/selection from another worksheet

Yasuo Main 0
Viewed 13

I have two worksheets: one with a table containing rows of new employees and another one with a table containing rows of their family members besides their related employee name.

I want to check whether the joining employees /whose first row always starts from D11/ have family but I am unable to make a selection (keep getting several different errors like 1004) from the first worksheet unless I hard code it.

Here's what I have currently:

Sub checkFamily()
    
    Dim ws As Worksheet
    Dim peopleRange As Range
    Dim peopleCount, i As Integer
    
    Set ws = Sheets("List HI Persons Employees")
    
    peopleRange = ws.Range("D11", Range("D11").End(xlDown))
'   peopleRange = ws.Range("D11", Range("D11").End(xlDown)).Address

    [E1] = peopleRange

'    '
'    peopleRange = ws.Range("D11:D" & Cells(Rows.Count, "D").End(xlUp).Row)
'    ''CANT .SELECT IF WS ISNT ACTIVE
    
    'count all joiners
    peopleCount = WorksheetFunction.CountA(peopleRange)
    [D1] = peopleCount
    
   'for loop to check all people
    For i = 1 To peopleCount
    
    'check EGN from employee sheet
    'Range("D11:D" & Cells(Rows.Count, "D").End(xlUp).Row).Select
    ''With Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)
    
    'if EGN not found = no family => print no
    
    
    'if EGN found => print yes => print family data
    
    Next i
    
    
End Sub
vba excel
1 Answers

Not sure why *perhaps I was missing a Set in my original code.. but I found a solution:

Set peopleRange = ws.Range("D11:D" & Cells(Rows.Count, "D").End(xlUp).Row)

I am interested if there's a way/function to easily check intersections between ranges and return the discrepancies between them (what I'm trying to do with a for loop currently).

Yasuo Main0
Related