Why R/T 438?

[jim may]

Sub Foo()
Dim MyRow As Integer, MyColNum As Integer
Dim MyColLtr As String
MyValue = InputBox("Enter text value")
    For Each C In Range("Rng")
        If C.Row > 1 And C.Column > 1 Then
            If MyValue = C Then
                MyRow = C.Row
                MyColNum = C.Column
                MyColLtr = Application.WorksheetFunction.Address(MyRow, MyColNum, 4)
            ActiveCell.Value = "Row " & MyRow & " and Column " & MyColLtr
            Exit Sub
            End If
        End If
    Next C
End Sub

 

[VoG]

VoG not thinking. Did you try the others?

 

[Norie]

Couldn't post one earlier but why not.

MyColLtr = Mid(Replace(Cells(1, MyColNum).Address, "$1", ""), 2)

 

[Rick Rothstein]

Couldn't post one earlier but why not.

MyColLtr = Mid(Replace(Cells(1, MyColNum).Address, "$1", ""), 2)

That would work fine as would...

MyColLtr = Replace(Replace(Cells(1, MyColNum).Address, "$1", ""), "$", "")

and, perhaps, a dozen other such string manipulation methods.

 

[Norie]

Jim

Here's a list of all the functions that will work, alledgedly anyway, with Application.WorksheetFunction.

http://msdn.microsoft.com/en-us/library/ff847330.aspx

it's for Excel 2010 but if you'll find lists for other versions there as well, and a few other thousand places on the web too.

 

[jim may]

Thanks Norie;
Jim

 

[Norie]

No problem, and apologies for the spelling/punctuation/grammar.