Vba look for certain value and copy certain cell value in the same row

[Goldyyy]

Hello!
So I looking for a value "25" in column L, then according to the value I want to copy the values that's in Column A and paste it in different sheet in cell value "G1". It works, but the problem is that there are only two cells in column L with a value 25, but it pastes 2 things that are correct ones and then 3 random values and I have no idea why. The values that are pasted should be only "SR 1" and "SR 1.1" I have no idea where the numbers 4 came out and also the text.


https://i.stack.imgur.com/7vKoO.png (https://i.stack.imgur.com/7vKoO.png) https://i.stack.imgur.com/TTqYr.png (https://i.stack.imgur.com/TTqYr.png)

Would love If someone could help me out with this. Thank you in advance!





Public Sub FindingValues()
Dim val As Integer, result As String, firstAddress As String
Dim a As Range

val = 25
Set a = Sheets("riskuregistrs").Range("L:L").Find(val, LookIn:=xlValues, MatchCase:=False)


If Not a Is Nothing Then
firstAddress = a.Address


Do
If Len(result) > 0 Then
result = result & "," & a.Offset(0, -11).Text

Else
result = a.Offset(0, -11).Text
End If


Set a = Cells.FindNext(a)
Loop While Not a Is Nothing And a.Address <> firstAddress
End If


Sheets("Riskukarte").Range("G1").Value = result
End Sub

[p45cal]

include Lookat:=xlwhole as an argument of .Find

[Goldyyy]

Hi p45cal! Thank you so much for the help! It now works as intended, thank you! :hi::yes

[snb]

Use autofilter


with columns(11).specialcells(2)
.autofilter 25
.offset(1,-10).copy Sheets("Riskukarte").Range("G1")
.autofilter
End with