Weird Selection.Find problem

[mysticmario]

Hi. I am trying to write a code where ComboBox2 value is found in another worksheet and then it is copied to a different sheet somewhere in between E125:E253 range(where the cell is empty).
The first problem i found is with just the Find part of the code where it gets the value if I use ActiveSheet.Columns("C:C").Select but refuse to work if I simply switch to Sheets("sheetsname").Columns("C:C").Select syntax, another problem is obviously gettign the empty cell from E125:E253 and then paste this Combox2 value into it
Here's the code that works with fiinding the value. the other part of functionality never worked for me.

Private Sub add_Click()
Dim cell As Range
Dim lr As Long
Dim cena As String
Dim opis As String
Dim opis2 As String
Dim ilosc As String
Sheets("LISTA MATERIAŁÓW I OKUĆ").Activate
ActiveSheet.Columns("C:C").Select
Set cell = Selection.Find(what:=Me.ComboBox2.Value, after:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    Exit Sub
Else
lr = Sheets("WYCENA").Range("E125:E253").Cells.Count
    Sheets("WYCENA").Range("E125:E253").Cells(lr + 1).Value = Me.ComboBox2.Value
    opis = cell.Offset(0, 1).Value
    cena = cell.Offset(0, 3).Value
    ilość = Me.TextBox3.Value
    opis2 = Me.TextBox2.Value
    'DEBUG PRINT'
    MsgBox opis
    MsgBox cena
    MsgBox ilość
    MsgBox opis2
    
End If
End Sub

Any idea how to make it work as intended?

 

[Fluff]

How about like

Private Sub add_Click()
Dim cell As Range
Dim lr As Long
Dim cena As String
Dim opis As String
Dim opis2 As String
Dim ilosc As String
With Sheets("LISTA MATERIALÓW I OKUC")
Set cell = .Columns("C:C").Find(what:=Me.ComboBox2.Value, after:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
End With
If cell Is Nothing Then
    Exit Sub
Else
lr = Sheets("WYCENA").Range("E125:E253").SpecialCells(xlBlanks)(1).Row
    Sheets("WYCENA").Range("E" & lr).Value = Me.ComboBox2.Value
    opis = cell.Offset(0, 1).Value
    cena = cell.Offset(0, 3).Value
    ilosc = Me.TextBox3.Value
    opis2 = Me.TextBox2.Value
    'DEBUG PRINT'
    MsgBox opis
    MsgBox cena
    MsgBox ilosc
    MsgBox opis2

End If
End Sub

 

[mysticmario]

 

[RoryA]

Remove the after:=activecell argument.

 

[mysticmario]

Remove the after:=activecell argument.

Thank you, works like a charm.

 

[Fluff]

Remove the after:=activecell argument.

Oops, missed that bit.

 

[kokano90]

View attachment 55689View attachment 55688

.colums("C:C")

=

.Range("C:C")

 

[kokano90]

View attachment 55689View attachment 55688

Have you been able to handle it?

 

[mysticmario]

yup