Why For each rw is empty?

Code like this:

Code:

---

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

---

Attachment 29257

Code:

---

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

---

Attachment 29258

And here I have the problem:

Code:

---

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

---

Attachment 29259

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

Code:

---

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).

Try

Code:

---

For Each rw In NID_nemocnice.Rows

---

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

Code:

---

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

---

could simply be

Code:

---

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

---

Code:

---

        For Each rw In NID_nemocnice.Rows

---

Error Object Required

Modification:

Code:

---

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:

Code:

---

    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

---

Any idea how to fix this:

Code:

---

OriginalTable.Cells(m

---

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

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?

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