Getting Column Address of Cell Representing the First Instance of a String (VBA)

Question

I want to get the column address of the first cell in a particular worksheet that contains a certain string.

Here is code I have written to do so:

Dim StringInQuestion As String
Dim ColumnRange1 As Range
NamedSheet.Select
Range("A1").Select
On Error Resume Next
Set FinalCell = Cells(1, 1).SpecialCells(xlLastCell)
FinalCellAddress = Cells(FinalCell.Row, FinalCell.Column).Address
Range(ColumnRange1).Select
Selection.Copy
Set ColumnRange1 = NamedSheet.Cells
Dim ColumnRange2 As Range
ColumnRange2 = ColumnRange1.Find(StringInQuestion)
Dim ColumnComposite As Variant
ColumnComposite = ColumnRange1.Address(ColumnAbsolute:=True)
Dim Column As Variant

'Format column address procured for further use (remove any numbers)

Dim intColumn As Integer
ColumnComposite = Trim(ColumnComposite)
For intColumn = 1 To Len(ColumnComposite)
    If Not IsNumeric(Mid(ColumnComposite, intColumn, 1)) Then
        Column = Column & Mid(ColumnComposite, intColumn, 1)
    End If
Next intColumn
'Column = Column

Although this code compiles without errors, the 'Column' variable remains undefined. Any ideas? Please share. Thanks!

Update:

Thank you @ScottHoltzman for your help.

do you just want to return the column letter (or number) of the first time a certain string appears in a cell in a worksheet? — Scott Holtzman, Jun 29 '16 at 15:49
Hi @ScottHoltzman ---The column letter. Thanks for your help. — PBG, Jun 29 '16 at 15:53
Does your code not work? Do you get errors (if so, what error and where)? — BruceWayne, Jun 29 '16 at 15:54

score 5 · Accepted Answer · answered Jun 29 '16 at 15:54

There are many built-in objects and methods that can make the code much simpler:

Dim ws as Worksheet
Set ws = Worksheets("mySheet")

Dim rngFound as Range
Set rngFound = ws.Cells.Find(StringInQuestion, lookat:=xlPart) 'assumes can be part of cell, change to xlAll if need exact match

If not rngFound is Nothing Then 
    Dim sCol as String
    sCol = Split(rngFound.Address,"$")(1) 'letter
    'sCol = rngFound.Column 'to get number
Else
    Msgbox StringInQuestion & " not found in " & ws.Name & "."
End If

Getting Column Address of Cell Representing the First Instance of a String (VBA)

1 Answers1