VBA - For each cell in Named Range

Question

VBA - For each cell in Named Range

Anonymous

Hi everyone,

I've got a named range (IL_FileName) and I want to loop through each cell within that range to match up the value in a UserForm control. Here's my code, but it doesn't work?

Dim wsISV As Worksheet

Dim rCell As Range

Set wsISV = Worksheets("I_SELECTED_VESSEL")

For Each rCell In wsISV.Range("IL_FileName")

If Cell.Value = Me.I_SelectVessel_L.Value Then

Cell.Offset(0, -2).Value = "Selected"

GoTo ContinueProcedure

End If

Next rCell

ContinueProcedure:

wsISV.Calculate

This makes perfect sense to me but it errors on the "For Each rCell..." line. I know it happens on "IL_FileName" because when I replace that with the actual range it works fine.

IL_FileName formulas is below. It's a "dynamic" range and automatically adjusts based on what data is in the column:

=OFFSET(IMPORTED_LIST!$C$2,0,0,COUNTA(IMPORTED_LIST!$C:$C)-1,1)

Any help would be appreciated.

Many thanks,

Daniel

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

Hi,

If you have defined your range "IL_FileName" as

Set IL_FileName = WORKBOOK.Sheet.Range("????")

Then instead of writing your For Each statement as

For Each rCell In wsISV.Range("IL_FileName")

try writing it as

For Each rCell In IL_FileName

......

Hope this Helps.

Answer 2

Hi,

I am also guessing but it looks strange to me that you address a named range in sheet "I_SELECTED_VESSEL" while the named range refers to sheet "IMPORTED_LIST".

Try

For Each rCell In Range("IL_FileName")

Note that you to use rCell further.

If Cell.Value = should be If rCell.Value ...

and so on.

Answer 3

Might be a problem entering a dynamic range using

Set IL_FileName = WORKBOOK.Sheet.Range("????")

The sub in my snip below works so what is wrong with the OP's?

Maybe \Daniel could put a sample file on OneDrive or Dropbox and tell us the link. Then we could experiment.

best wishes

Share via

VBA - For each cell in Named Range

3 answers