How can I retrieve the column number of a cell in a specific row.
ie i want to get the column number of a cell in row 3 that holds the
value "successful" for instance
Thanks
Faby

Hi Faby,

Try:

'=============
Public Sub Tester()
Dim rng As Range
Dim col As Long
Const rw As Long = 3

Set rng = Rows(rw).Find(What:="successful", _
After:=Rows(rw).Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)
If Not rng Is Nothing Then
col = rng.Column
MsgBox col
End If

End Sub
'<<=============



---
Regards,
Norman



"Faby" wrote in message
ups.com...
How can I retrieve the column number of a cell in a specific row.
ie i want to get the column number of a cell in row 3 that holds the
value "successful" for instance
Thanks
Faby

When retrieving the column of a cell in these types of technques is there a
way to retrieve the column as a text character? I am trying to create names
in a loop and the column is dynamic so i use the activecell.column function
to retrieve the column. The name function gives me an error that the formula
you typed has an error. If I hard code my variable to be a letter instead of
a number it works.

thanks!
hckygirloh


"Norman Jones" wrote:

Hi Faby,

Try:

'=============
Public Sub Tester()
Dim rng As Range
Dim col As Long
Const rw As Long = 3

Set rng = Rows(rw).Find(What:="successful", _
After:=Rows(rw).Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)
If Not rng Is Nothing Then
col = rng.Column
MsgBox col
End If

End Sub
'<<=============



---
Regards,
Norman



"Faby" wrote in message
ups.com...
How can I retrieve the column number of a cell in a specific row.
ie i want to get the column number of a cell in row 3 that holds the
value "successful" for instance
Thanks
Faby

Hi Hckygirloh,

It should not be necesasary to convert the column number.

Post your problematic code.


---
Regards,
Norman


"Hckygirloh" wrote in message
...
When retrieving the column of a cell in these types of technques is there
a
way to retrieve the column as a text character? I am trying to create
names
in a loop and the column is dynamic so i use the activecell.column
function
to retrieve the column. The name function gives me an error that the
formula
you typed has an error. If I hard code my variable to be a letter instead
of
a number it works.

thanks!
hckygirloh


"Norman Jones" wrote:

Hi Faby,

Try:

'=============
Public Sub Tester()
Dim rng As Range
Dim col As Long
Const rw As Long = 3

Set rng = Rows(rw).Find(What:="successful", _
After:=Rows(rw).Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)
If Not rng Is Nothing Then
col = rng.Column
MsgBox col
End If

End Sub
'<<=============



---
Regards,
Norman



"Faby" wrote in message
ups.com...
How can I retrieve the column number of a cell in a specific row.
ie i want to get the column number of a cell in row 3 that holds the
value "successful" for instance
Thanks
Faby

Here is my code. If i hardcode the tmpTankCol variable to be 'A' or 'B' or
any other letter it works, when i run the code this way i get the "the
formula you typed has an error" message


tmpTankCol = ActiveCell.Column
Names.Add Name:=newName, RefersTo:="='POLARIS Tank Temp Input'!$" &
tmpTankCol & "$2:$" & tmpTankCol & "$13"

Thanks!!!
HckyGirlOH






"Norman Jones" wrote:

Hi Hckygirloh,

It should not be necesasary to convert the column number.

Post your problematic code.


---
Regards,
Norman


"Hckygirloh" wrote in message
...
When retrieving the column of a cell in these types of technques is there
a
way to retrieve the column as a text character? I am trying to create
names
in a loop and the column is dynamic so i use the activecell.column
function
to retrieve the column. The name function gives me an error that the
formula
you typed has an error. If I hard code my variable to be a letter instead
of
a number it works.

thanks!
hckygirloh