trying to activate sheet based on cell value

rvkcools · Apr 21st 2013

i want to activate the sheet based on my cell value
but the prog is not working

Code

Sub emptest()
Dim tempname As String
Worksheets("team").Select
Range("c24").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = tempname Then
Worksheets(tempname).Select
Else
MsgBox "no sheet"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Display More

please help me

HaHoBe · Apr 21st 2013

Re: trying to activate sheet based on cell value

Hi, rvkcools,

assign a value to tempname otherwise the variable will show ab emnpty sting which can´t be found to to contents in the cells compared.

Maybe try it like this

Code

Sub emptest()
Dim tempname As String
Dim lngCounter As Long
tempname = "test"
With Worksheets("team")
  For lngCounter = 24 To .Cells(Rows.Count, "A").End(xlUp).Row
    If .Cells(lngCounter, "A").Value = tempname Then
      Worksheets(tempname).Select
      Exit For
    Else
      MsgBox "no sheet " & .Cells(lngCounter, "A").Value
    End If
  Next lngCounter
End With
End Sub

Display More

Ciao,
Holger

trying to activate sheet based on cell value

Participate now!

Share