Excel VBA loop & cell value match

Question

What I am trying to do seems basic enough, however I don't know where I am going wrong with the code.

I want to run the selected cell through a loop of the worksheets and select the worksheet that matches the selected cell located in cell B1.

Dim SelectedCell as Range
Dim ws As Worksheet
Set SelectedCell = Range(ActiveCell.Address)
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("B1").Value = SelectedCell.Value Then
ActiveSheet.Select
End If
Next ws
End Sub

Thanks in advance for all the help!

Matt Cremeens · Accepted Answer · 2014-06-13 17:03:30Z

1

Try instead

Dim ws As Worksheet
SelectedCell = ActiveCell
For Each ws In ActiveWorkbook.Worksheets
  If ws.cells(1,2) = SelectedCell Then
    ws.Select
  End If
Next ws
End Sub

edited Jun 13, 2014 at 17:03

answered Jun 12, 2014 at 19:00

Matt Cremeens

5,2118 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

L42 Over a year ago

I think you need to use Set in this line SelectedCell = Activecell since you dimensioned it as Range?

Matt Cremeens Over a year ago

Good point. Perhaps it is best to not even dimension it at all. That is part of the beauty of vba after all, isn't it? :)

juanvan · Accepted Answer · 2014-06-13 00:31:49Z

0

Select cell run macro will select the sheet name that matches the selected cell. (Case sensitive)

Dim SelectCell As String
Dim ws As Worksheet
SelectCell = ActiveCell.Value2
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = SelectCell Then
        ws.Select
    End ID  
    Next ws

edited Jun 13, 2014 at 0:31

answered Jun 12, 2014 at 19:25

juanvan

6818 silver badges21 bronze badges

Collectives™ on Stack Overflow

Excel VBA loop & cell value match

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related