Get Column Letter from Header Text Value

[DanG]

Hello,

I have not been here for quite some time, but I just got stuck on this one and would love any help I can get...

I have the following in some VBA and want to refine it a bit

"=IF(OR($bf1=""At Risk"",$Bf1=""Default: Please Reassign""),1,0)"

I would like to colunm reference "$Bf1" tends to change as from time to time. But the column header is always labled "50/50", so I thought I'd like to find the column number by the header name and just can't find much on the subject as it relates to what I want to do.

It would be great to find the column number based on "50/50" and make that value a variable and use the variable in my "if" statement in the vba I currently have above.

Any help would be appreciated!

Thank you!

 

[Brianwarnock]

Can you search along the header row , row 1 I presume until you find it
Something like, having sorted syntax and dim


C=1
Do until cell(1,c)="50/50"
C=c+1
Loop

Brian

 

[DanG]

Thank you for the reply!

Yes, row 1.

So I can see where that would find the cell, then I would need to get the column letter into a variable. Then I need to use that variable in my if statment...

Dim MyCol as string
MyCol = the column letter from the cell that contains the results of the loop

"=IF(OR($[B]MyColf[/B] & 1 =""At Risk"",$[B]MyCol[/B] & 1=""Default: Please Reassign""),1,0)"

 

[Brianwarnock]

Well you did say column number, I don't know how to iterate for the letter, but then I also do not recognise your code as VBA. Code, it looks like worksheet code , then again things may be different in the latest releases.

Sorry cannot help

Brian

 

[DanG]

Thanks for the help anyway!

That was psuedo code just to get the point accross and the hope that someone could take the psuedo code and make it into VBA

 

[Brianwarnock]

In VBA you would use a Range function such as Cells(rowindex , colindex) so the column number could be used, out all day today but will look again later.

Brian

 

[Brianwarnock]

As I said that code makes no sense in VBA, I also don't understand what this is

$MyCol & 1

, if it were correct syntax it would be looking at row one in the column , which we know contains 50/50 .

I have put together a simple bit of code that may or may not get you started, but to do anymore i would need a decent explanantion of what you are trying to do.

Brian

Sub dancheck()
Dim c As Long
Dim ws As Worksheet
' find col with 50/50 in row 1
Set ws = Sheets("sheet1")
c = 1
Do Until ws.Cells(1, c) = "50/50"
c = c + 1
Loop

'test row 2 for values and put 1 or 0 in cell A1

If ws.Cells(2, c) = "at risk" Or ws.Cells(2, c) = "default" Then
    ws.Range("A1") = 1
Else
    ws.Range("A1") = 0
End If

End Sub

 

[DanG]

That got me going in the right direction, thank you very much!