Getting and Saving a Cell Reference

[Guest]

I am searching in a table for a specific heading, for example ABS. Now as I
am receiving this information from a 3rd party, that ABS could be in column
10 on one file, but in column 54 in the next. I would like to retreive the
column number of that heading, store it within the macro to use for sorting
and other funtions I am creating. How can I get just the column, or just the
row, or both if I need it. The address function is what I would use in Excel.

Thanks.

 

[Guest]

hi,
try working something like this into your macro..
Sub arc()
Dim a As String
Dim b As String
Dim c As Long
Dim d As Long
a = InputBox("enter something")
Cells.Find(What:="a", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
b = ActiveCell.Address
c = ActiveCell.Row
d = ActiveCell.Column
MsgBox b
MsgBox c
MsgBox d

End Sub
the code is wordy but only to show your the 3 items you mentioned. row,
column and/or address.

regards
FSt1

 

[Dave Peterson]

You could use the same thing as Edit|Find in code:


Dim FoundCell as range
Dim WhatToFind as String

whattofind = "ABS"

With someworksheetvariablehere

Set FoundCell = .Cells.Find(What:=whattofind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

end with

if foundcell is nothing then
msgbox "not found
else
msgbox foundcell.address & vblf & foundcell.row & vblf & foundcell.column
end if

If you know it's in row 1, you could be more specific:

With someworksheetvariablehere
with .rows(1)
Set FoundCell = .Cells.Find(What:=whattofind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with
end with
.....

 

[Guest]

This should do it. Thanks for taking the extra time to show me what the code
will do.

Cheers.

 

[Guest]

Thank you for your help.

I found the reply from FTs1 a bit easier to follow and will probably go with
that one.

Cheers.