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)

29257


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

29258

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

29259

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.