Why For each rw is empty?

[vangog]

Code like this:

' Definition of variable Nemocnice (in Nemocnice Sheet)
Sheets("Nemocnice").Select
Selection.CurrentRegion.Select
Set Nemocnice = Range(Selection.Address)

Nemocnice.jpg

' Source table named as "OriginalTable" contains columns NID and Nemocnice.
OriginalSheet.Select
Selection.CurrentRegion.Select
Set OriginalTable = Range(Selection.Address)

NID.jpg

And here I have the problem:

NID_nemocnice = OriginalTable.Offset(1, NemocniceCol - 2).Resize(OriginalTable.Rows.Count - 1, 2)
For Each rw In NID_nemocnice
        Set MyVar = rw.Cells(1).Value
        m = Application.Match(rw.Cells(1).Value, Nemocnice.Columns(2), 0)
        If Not IsError(m) Then
            OriginalTable.Cells(m, NemocniceCol - 1).Value = rw.Cells(2).Value
        End If
Next rw

watch.jpg

So when I enter the For Each loop, the variable rw is empty. As a result it crashes on the yellow line:

Set MyVar = rw.Cells(1).Value

Error 424 Object required.

But I can see in the Watch (see image) that the nemocnice is filled with values (almost 40,000 items).

[Bob Phillips]

Try

For Each rw In NID_nemocnice.Rows

BTW, using address is redundant when you already have the range

' Definition of variable Nemocnice (in Nemocnice Sheet)
Sheets("Nemocnice").Select
Selection.CurrentRegion.Select
Set Nemocnice = Range(Selection.Address)

could simply be

' Definition of variable Nemocnice (in Nemocnice Sheet)
Set Nemocnice = Worksheets("Nemocnice").CurrentRegion

[vangog]

 	For Each rw In NID_nemocnice.Rows

Error Object Required

[vangog]

Modification:

NID_nemocnice = OriginalTable.Offset(1, NemocniceCol - 1)

I expected there will be array of two items in rw. But there is "Fakultní nemocnice Plzeň"... I think I should rather use for loop instead for each

My corrected code so far:

    For i = 2 To OriginalTable.Rows.Count
        ' NID_nemocnice.cells(i)
        m = Application.Match(OriginalTable.Cells(i, NemocniceCol).Value, Nemocnice.Columns(2), 0)
        If IsError(m) Then
          MsgBox "Item not found in Nemocnice, please fix the sheet data." ' How to add variable?
          Exit Sub
        End If
        Set NID = Nemocnice.Cells(m, 1)
        Set NName = Nemocnice.Cells(m, 2)
        OriginalTable.Cells(m, NemocniceCol - 1).Value = NID
        ' OriginalTable.Cells(m, NemocniceCol).Value = NID ' change this to refer the cells(m,2)
    Next i

[vangog]

Any idea how to fix this:

OriginalTable.Cells(m

Instead m I need to use the row, where the original table starts + i. How to return that value?

[Bob Phillips]

I am getting a bit confused as to what you are trying to do, but have you tried the numeric 1 instead of m in that line?

[vangog]

That was stupid, the problem had simple solution. Solved.