How to find column with matching value and assign it to the textbox1 in user form VBA

Question

i am having problem loading data from matched cell to current date into the texbox in the user form

I have

Private Sub NSheet_Click()
    MoveSheet (1)
    txtBox2.Text = "1st: " & " 2nd: " & " 3rd: "
End Sub

and when next is selected moveSheet will switch to the next sheet in the workbook then i have function

Sub FindtheDay()
    Dim rFind As Range
    Dim kolona As Long
    Dim today As Date
    today = Date

    With ActiveSheet.Range("F2:AJ2")
        Set rFind = .Find(What:=today, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            kolona = CStr(rFind.Column)
            KolLN = kolona
        End If
    End With
End Sub

that is looking for cell that match to the current date (date only)

then i need that column number as reference to the cell that is 48 row below the one where it was found.

basicli, when Next button is pressed i whant it to switch sheet and then to find column with matching date and then to point to cell Range(ColumNumber & "50") and send that value into textbox

• also i have three cels ColumnNumber,50,51 and 52 that i whant to put in textbox on user form

All I have tried is giving me error about wrong type

Also I am using public variables because some of them i need i other Sub procedures

Thanks, D

Answer 1

So you cant create a Range of Columns like that. It has to be like this:

.Range(Columns(1), Columns(50))

If you want to show some value in a userform then you can assign it with addressing it like that.

Sub Test() 
UserForm1.TextBox1.Text = Worksheets("Sheet1").Range("A1").Value
UserForm1.Show
End Sub