Search Value

[watzmann]

Hi All,

I have a problem with this code , i'm trying to find a value on a sheet and once i find it i offset my selection to a cell of a figure and copy this figure to another cell.

my problem is , i need to resume the search from the last founded figure ,

how to do that ?

Sub findnext()

Cells.Find(What:="ASK Training Managers", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
          
    
ActiveCell.Offset(-5, 2).Select


Selection.Copy
    Range("AC12").Select
    ActiveSheet.Paste
    ActiveCell.Select
    
    
If cell_position(1) <> "" Then
        Range(cell_position(1)).Select
    Else
        MsgBox "No previous cell stored"
    End If


  End Sub

i have recieved this code but it doesn't work

Dim cell_position(0 To 1) As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
cell_position(1) = cell_position(0)
cell_position(0) = Target.Address
end sub

If cell_position(1) <> "" Then
        Range(cell_position(1)).Select
    Else
        MsgBox "No previous cell stored"
    End If


  End Sub

[watzmann]

I have modified my code to this one ,

the new problem is it's just work for one step and stop , that mean i think my loop doens't work fine .

Dim dstrng As Range
Dim firstaddx As String
Dim n As Long
Dim c As Long


Sub findnext()

Set dstrng = Range("AD20")
n = 0
c = 0

Do

Cells.Find(What:="ASK Training Managers", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
   Cells.findnext(After:=ActiveCell).Activate
    
ActiveCell.Offset(-5, 2).Select



    ActiveCell.Copy
   ActiveSheet.Paste Destination:=dstrng.Offset(c, 0)
   Application.CutCopyMode = False
   c = c + 1
   n = n + 1
 Loop While n = 10
   
 
End Sub

[Phil_V]

Hi, that second section of code was from me, and it works fine for what you said you were doing in the post where you asked for it. However for the problem you have here you do not need it.
In your original code you don't need to be activating, or selecting cells to do the core functions, just at the end when you want the selection moved to the cell that was found (for some reason), so it would need to be like this:

Sub findnext()
Dim found As Range

On Error Resume Next
Set found = Cells.Find(What:="ASK Training Managers", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
On Error GoTo 0
        
' Make sure that something was found
If found Is Nothing Then Exit Sub

' Copy from an offset of (-5,2) to "AC12"
found.Offset(-5, 2).Copy Destination:=ActiveSheet.Range("AC12")

found.Select
End Sub

EDIT
I see you have made another post. It doesn't help people to help you when you keep changing the code function!
The first code you posted has no looping, the second code has suddenly got a loop in it, and the absolute destination of "AC12" has turned variable.

Please detail, (in words, not code), what EXACTLY you are trying to achieve, and THEN we can offer assistance.

[watzmann]

but i need also to use this code section because i exepect many values and i need to list all of them in order .

but i don't know why it just goes for one step and stop

ActiveCell.Copy
   ActiveSheet.Paste Destination:=dstrng.Offset(c, 0)
   Application.CutCopyMode = False
   c = c + 1
   n = n + 1
 Loop While n = 10

[watzmann]

now i modified to this but i got an error : " object required "

Dim dstrng As Range
Dim firstaddx As String
Dim n As Long
Dim c As Long
Dim found As Range
Dim found1 As Range



Sub findnext()

Set dstrng = Range("AD20")
n = 0
c = 0

Do

Set found = Cells.Find(What:="ASK Training Managers", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
 Set found1 = Cells.findnext(After:=ActiveCell).Activate
    
ActiveCell.Offset(-5, 2).Select



    found.Offset(-5, 2).Copy Destination:=dstrng.Offset(c, 0)

found1.Select

   c = c + 1
   n = n + 1
 Loop While n = 10
   
 
End Sub

[Phil_V]

I'll ask again, WHAT are you trying to achieve?

Find each occurance of "ASK Training Managers", and for each occurance copy the value that is at an offset of -5 rows, and + 2 columns into a list that starts at "AD20", and works it's way down for each value?

[watzmann]

Find each occurance of "ASK Training Managers", and for each occurance copy the value that is at an offset of -5 rows, and + 2 columns into a list that starts at "AD20", and works it's way down for each value?

exactly that's what i'm doing

[Phil_V]

In that case try the following. Using 'Select' and 'Activate' the way that you are will only serve to tie you in knots

Sub findnext()
Dim found As Range, dest_rng As Range
Dim first_found_addr As String

With ActiveSheet
    On Error Resume Next
    Set found = .Cells.Find(What:="ASK Training Managers", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    On Error GoTo 0
            
    ' Make sure that something was found
    If found Is Nothing Then Exit Sub
    
    first_found_addr = found.Address
    
    ' Loop until we either come back to the first cell we found, or for some reason we can't find anything
    Do
        ' Copy from an offset of (-5,2) to "AD20" list
        If .Range("AD20") = "" Then ' If AD20 is blank then put the result there, otherwise add to the list
            Set dest_rng = Range("AD20")
        ElseIf .Range("AD21") = "" Then
            Set dest_rng = Range("AD21")
        Else
            Set dest_rng = .Range("AD20").End(xlDown).Offset(1)
        End If
        found.Offset(-5, 2).Copy Destination:=dest_rng
        ' Find the next occurance and set 'found' to be that cell
        Set found = .Cells.findnext(found)
    Loop While found.Address <> first_found_addr
End With
End Sub

[watzmann]

thanks a lot , it works very well

[Phil_V]

Please remember to mark the thread as 'Solved'.
Edit your first post, click [Go Advanced], then use the drop down next to the subject to select [Solved]