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

 

[Norie]

You can't use the worksheet function ADDRESS with Application.WorksheetFunction.

 

[jim may]

You can't use the worksheet function ADDRESS with Application.WorksheetFunction.

I just LOVE how Excel "sometimes" works, or doesn't work.. grrrrr
Thanks for pointing out this exception. Can you suggest a workaround?

 

[VoG]

Somewhat inelegant but maybe (untested)

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 = Cells(MyRow, MyColNum).Address(False, False)
                MyColLtr = Left(MyColLtr, InStr(MyColLtr, ":") - 1)
            ActiveCell.Value = "Row " & MyRow & " and Column " & MyColLtr
            Exit Sub
            End If
        End If
    Next C
End Sub

 

[p45cal]

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

or
MyColLtr = Split(c.Address, "$")(1)

 

[Rick Rothstein]

Or alternately....

MyColLtr = Split(Columns(MyColNum).Address(, 0), ":")(0)

 

[jim may]

It's times like this when being a part of such a professional "community" comes home!!!
Thanks to each of you for your input - I rarely go to Thread Tools - Show Printable Version, but your inputs are so good I was compelled - to further study each (off-line).

Googling - I also came across this one-liner, which also works...

Again thanks,,

Jim

MyColLtr = Left(Cells(MyRow, MyColNum).Address(1, 0), InStr(1, Cells(MyRow, MyColNum).Address(1, 0), "$") - 1)

 

[Rick Rothstein]

@Jim May,

There are lots of ways to achieve this using String manipulations. Here is another one using an InStr method similar to what you posted...

MyColLtr = Left(Columns(MyColNum).Address(, 0), InStr(Columns(MyColNum).Address(, 0), ":") - 1)

Note I used Columns rather than Cells so that there is no need to specify a Row number that will never be used in the evaluation.

 

[theozz]

Suggest this...

Option Explicit

Sub x()
Dim MyRow, MyColNum
MyRow = 5
MyColNum = 7
MsgBox Cells(MyRow, MyColNum).Address(0, 0)

End Sub

 

[jim may]

VoG:

MyColLtr = Cells(MyRow, MyColNum).Address(False, False) ' produces H14 (without the ":")

Jim