VBA Get the number of the column that contains a specific value-CodePudding

Hello I am trying to get a number of the column that contains the value "#" in row 1. The code I have written is:

Dim ColumnNumber_Number As single
ColumnNumber_Number = Cells(ThisWorkbook.Worksheets("BOM").Cells(1, Cells.Find("#", lookat:=xlWhole).Column)).Column

E.G. That column is number 2 and I need to get that value into my variable But it returns an error "Invalid procedure call or argument". What's the mistake I'm making?

CodePudding user response：

If I correctly understood your question (to return the column in discussion searching in the first row), the next code will do what you need:

  Dim ColumnNumber_Number As Long, wsB As Worksheet
  Set wsB = ThisWorkbook.Worksheets("BOM")
  ColumnNumber_Number = wsB.rows(1).Find("#", lookat:=xlWhole).Column

Your construction is not working because of a strange construction. First Cells( does not have any meaning, and searching in all the sheet cells is not the most appropriate way, I think. No nice to construct a new cell to return the column from this one, too... Anyhow, the cells where to search for should be fully qualified.

CodePudding user response：

What about this? You don't have to give a search range that way

Sub test_macro()

    Set ColumnNumber_Number = Cells.Find(What:="#", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                        False)
 
 If Not ColumnNumber_Number Is Nothing Then
 MsgBox ColumnNumber_Number.Column
 End If
    
End Sub