find worksheet based on string in a cell

[JWebb]

Hello all, and thanks in advance
I'm writing a small macro and am beating my head against the wall on a simple problem. (Excel 2016, PC, been 3 years since I've done any VBA).
I have data from 2 cells (A1 and B1) that I have concatentated into cell A3. The string in A3 now matches the name of one of the worksheets in the workbook. The number of worksheets is dynamic. I simply want to find and select the applicable worksheet so the user can view it.

How I remembered how to do everything up to this point and yet forget how to do this is beyond me.

 

[Tetra201]

Try this ("Your_Sheet" is where the cell A3 is):

Sheets(Sheets("[COLOR=#ff0000]Your_Sheet[/COLOR]").Range("A3").Value).Select

 

[JWebb]

That was awesome. I love this.

Thank you very much!!!!

 

[JWebb]

...if I may trouble you again...
My next step is to find a cell in column c on the activesheet we've just selected that matches the string in column d on "Sheet2" (which is also the value of ComboBox3 in a user form - whichever is easier). I want to find the cell and make it active.

I've tried the following code and get no errors, but I get no results either.
'active stands for active ingredient

Dim active as String
active = Application.WorksheetFunction.Vlookup(ComboBox3.value, ActiveSheet.Range("C:C"), 1, False)

fyi-i'm new to, and horrible at, Vlookup

Again much thanks to any who read this

 

[JWebb]

it actually doesn't have to make the cell active, it can make the entire row active. This will set me up for the next step which is going to read something like this:

With ActiveSheet
If Textbox1.value . ActiveSheet.Range("F(Activerow)") Then
Userform2.Label7.ForeColor = vbRed
Else
Useerform2.Label6.Forecolr = vbGreen
End IF
End With

 

[Tetra201]

...My next step is to find a cell in column c on the activesheet we've just selected that matches the string in column d on "Sheet2"...

it actually doesn't have to make the cell active, it can make the entire row active...

See if this works for you (adjust D4 to suit your needs):

...
ActiveSheet.Range("C:C").Find(Sheets("Sheet2").Range("[COLOR=#FF0000]D4[/COLOR]"), _
    LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).EntireRow.Select
...

 

[JWebb]

again awesome! So, how will I address the "Active row" in my next piece of code (I posted it a few minutes ago - incorrectly)
Basically i'm checking to see if the value in a textbox is greater than or less than the value of the ActiveRow in Column F

 

[JWebb]

so I'm on a little bit different topic than the original post but not sure if I should start a new thread or not....
Using the above help from Tetra101 (who has been a fantastic helper) I've come to the following issue - How to reference an "Activerow" in a range? I think I've learned to use "Target" instead.On the following code:

With Activesheet
If Textbox1.Value > activesheet.range("J" & Target.row) Then
Userform.label7.forecolr = vbRed
Else
Userform2.Label6.Forecolor = vbGreen
End If
End With

when I run this I get a Run Time error '424 Object required

Any help is greatly appreciated

 

[JWebb]

Figured it out.
Instead of using "target" I used "ActiveCell".

Thanks to all for everything along the way!!