Trying to search for a column with a certain occurrence and then set it as a variable which can be a-CodePudding

This is the code I have so Far. I get a object required error at the first line. I am unsure how to do this/what I did wrong. Essentially I want to look through all of the columns of a sheet and all of the rows until the first occurrence of the value . Then I want to set the column at which this value occurs in as a variable accessible to all sub functions.

 Property Get col() As Long
    Count = 1
    Do
        Set col = wsSrc.Columns(Count).Find(What:="Value", LookIn:=xlValues, _
                                      LookAt:=xlPart, MatchCase:=False)
Count = Count  1
Loop Until Not col Is Nothing
        
End Property

In addition, I have another Issuer where I want the worksheet that is currently selected and that I am working on to be the source of data for my function. Currently this is what I have to define the value.

 Property Get wsSrc() As Worksheet
    Set wsSrc = Workbooks("SourceBook").Worksheets("SourceSheet")
End Property

However I want to change it so that wsSrc is just the workbook i am working on. I tried to make the below code execute this, however I am getting an error for subscript out of range.

Property Get wsSrc() As Worksheet
    Set wsSrc = ThisWorkbook.Worksheets("MBI DSCR")
End Property

CodePudding user response：

Not sure exactly what you are trying to accomplish with the col variable. But if you just wanted to return the column index and not the column value then use .column at then end. And the wsSrc works good with no issue for me.

Sub test()
    wsSrc.Range("A1") = col
End Sub

Property Get wsSrc() As Worksheet
    Set wsSrc = ActiveWorkbook.Worksheets("sheet1")
End Property

Property Get col() As Long
    col = wsSrc.Columns.Find(What:="Value", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False).Column
End Property