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
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