Select cell based on value of another cell

[1Marte]

My problem is probably very simple...I'm about to pull my hair out! I am using Excel2007.
I want to use the value that is in one cell to determine which of two cells to select in the worksheet. No copy, delete, just SELECT.
When I hover over the code it has the correct value, but the code doesnt work.
The bad code follows. I left the comments in so you can see what I've tried. nothing works.
THANK YOU in advance for any help. I have never posted before in my life, I have always found answers on this forum.

Sub FigFedWHcht()
'select the correct chart (Married or Single)
Sheets("Chart").Select '.Range("P6").Select
Dim rng As Range
'Set MCht = Sheet2.Range("B11")
'Set SCht = Sheet2.Range("R11")
Set rng = Sheet2.Range("P6")
With rng
'rng = rng.Value
Select Case rng
Case M
'MCht.Select
'Sheet2.Range("B11").Select
Sheet2.Range("FirstCellMarriedChrt").Select
Case S
'SCht.Select
'Sheet2.Range("R11").Select
Sheet2.Range("FirstCellSingleChrt").Select
'Case Else
' MsgBox ("There seems to be a problem") 'FOR TESTING
' GoTo xit
End Select
End With
Call FedFindResult
xit:
End Sub

 

[jeffreybrown]

Hello and Welcome to the Board,

Here is a sample which will look at P6

If M goes to B11

If S goes to R11

Is this what you were asking?

Sub Test()
    Dim rng As Range
    Set rng = Sheet2.Range("P6")
    Select Case rng
    Case "M"
        Application.Goto Sheet2.Range("B11")
    Case "S"
        Application.Goto Sheet2.Range("R11")
    End Select
End Sub

 

[1Marte]

WOW! what a quick reply.

P6 has a value of M or S

if P6 value is M I want to select R11
if P6 value is S I want to select B11

then, additional code will run to select the proper Federal Withholding Amount in the chart to with hold from their pay.
I hope this is clearer.

 

[jeffreybrown]

Yes it is clear and that is what the code does. What is it not doing for you that you need?

I did get the B11 and R11 backwards.

 

[1Marte]

its simply not selecting anything. Do you think I have other code interfering with this? When I hover, it says the value of P6 is M but it does not select the B11 cell. Doesnt work with S value either. Soo... I'm not as crazy as I thought I was.

 

[jeffreybrown]

Please post the code you are using. Also, use code tags as in my post.

[ Code ] **** [ / Code]

without the spaces

 

[1Marte]

it may take me a few to do this, as I have to read how. It kinda looks like HTML. I know just enough to be dangerous.

 

[jeffreybrown]

Posting code isn't anything extreme. All you have to do is copy the code from the VBE and paste it onto your post.

At the beginning of the code type [ Code ] and at the end of the code [ /Code ] without the spaces.

Look at the link in my sig block and you can see some examples. Even further, on the forum there is a test area where you can test before posting if you want to see the results.

 

[1Marte]

Thank you, Jeff for you patience.

Here is the code:

Dim rng As Range
Set MCht = Sheet2.Range("B11")
Set SCht = Sheet2.Range("R11")
Set rng = Sheet2.Range("P6")
With rng
rng = rng.Value
Select Case rng
    Case M
         MCht.Select
'        'Sheet2.Range("B11").Select
'        Sheet2.Range("FirstCellMarriedChrt").Select
    Case S
         SCht.Select
'        'Sheet2.Range("R11").Select
'        'Sheet2.Range("FirstCellSingleChrt").Select
    Case Else
         MsgBox ("There seems to be a problem") 'FOR TESTING
         GoTo xit
End Select
End With
Call FedFindResult
xit:
End Sub

 

[jeffreybrown]

This works for me.

Sub Test()
Sheets("Chart").Select
Dim rng As Range: Set rng = Sheet2.Range("P6")
Dim Mcht As Range: Set Mcht = Sheet2.Range("B11")
Dim Scht As Range: Set Scht = Sheet2.Range("R11")

Select Case rng
    Case "M"
        Application.Goto Mcht
'         Mcht.Select
'        Sheet2.Range("B11").Select
'        Sheet2.Range("FirstCellMarriedChrt").Select
    Case "S"
        Application.Goto Scht
'         SCht.Select
'        Sheet2.Range("R11").Select
'        Sheet2.Range("FirstCellSingleChrt").Select
    Case Else
         MsgBox ("There seems to be a problem") 'FOR TESTING
         GoTo xit
End Select
Call FedFindResult
xit:
End Sub