0

I've got the below to copy data based on two variables to the appropriate row in another sheet. It works (HOORAY!). However what I'm struggling to get my head around is the if logic I have used. I understand as it saying "If we find both cells, we redefine the Found to be nothing in order to end the loop". However as I now have set Found to Nothing, how is the code then able to find the address previously stored in Found and paste the data to it?

Sub copy_transpose()    
    Dim rng_source As Range
    Dim Found As Range, Firstfound As String
    Dim rngSearch As Range
    Dim Criteria As Variant
    
    Set rng_source = ThisWorkbook.Sheets("KPI").Range("H6:H100")
    Set rngSearch = Sheets("Table").Range("A:A")
    
    Criteria = Sheets("KPI").Range("C2:D2").Value
    
    Set Found = rngSearch.Find(What:=Criteria(1, 1), _
                                   LookIn:=xlValues, _
                                   LookAt:=xlWhole, _
                                   SearchOrder:=xlByRows, _
                                   SearchDirection:=xlNext, _
                                   MatchCase:=False)
            
    If Not Found Is Nothing Then   
        Firstfound = Found.Address
            
        Do
            If Found.EntireRow.Range("B2").Value = Criteria(1, 2) Then Exit Do 'Match found
            Set Found = rngSearch.FindNext(After:=Found)
            If Found.Address = Firstfound Then Set Found = Nothing       
        Loop Until Found Is Nothing 
    End If
        
    If Not Found Is Nothing Then
        Application.Goto Found
        rng_source.Copy
        Sheets("Table").Range(found.Offset(0, 1), found.Offset(0, 7)).PasteSpecial Transpose:=True   
    Else        
        MsgBox ("Error")
    End If
End Sub
PeepDeep
  • 53
  • 9
  • `how is the code then able to find the address previously stored in Found and paste the data to it` - I don't see your code doing that. Your code pastes into a range [incorrectly](https://stackoverflow.com/q/17733541/11683) defined via an [undeclared variable `cell`](https://stackoverflow.com/a/66151181/11683). – GSerg Feb 12 '21 at 12:06
  • could you please elaborate on your comment? I will add I did accidentally have the old code above, which used "cell" instead of "found" as the paste location. – PeepDeep Feb 12 '21 at 12:10
  • Now your code will display "Error" when `Found` is `Nothing`. It will still not use it for pasting in such case. – GSerg Feb 12 '21 at 12:13
  • If it is not using the if loop to find the location, then how is it pasting in the correct place each time? – PeepDeep Feb 12 '21 at 12:16
  • You can easily find the answer to that by running your macro with F8 rather than with F5. – GSerg Feb 12 '21 at 12:17
  • So using F8, goes through the loop once, then when it gets to the Do if it exits. Am I correct in understanding it as follows. Finds variable A in list sets it as found, then look to see if "found" matches criteria 2. It does not so then using find next it is looked for in next column. This new found doesn't match the first, so it loops up to the Do/If. As the new found matches the criteria the do is exited? – PeepDeep Feb 12 '21 at 12:33
  • I think the part you're missing is the "Exit Do". There are two ways to exit this Do While loop. One is that you go though all FindNext possibilities, and then exit because You've gone back to FirstFound. The other is that you've found what you're looking for and exit the Do While. If you exit the Do While in this manner, Found still contains a valid reference to a range. That would then make the next If expression true, because Found isnt nothing. – Toddleson Feb 12 '21 at 15:35

0 Answers0