CODEWITHSUNDEEP
excelvba
PeepDeep
PeepDeep

Reputation: 53

Find specific row based on two criteria and then copy paste range into row

I'm trying to copy data from a column in a sheet called "KPI", in cells H6:H100, to a specific row in a sheet named "table". The row depends on two variables in the KPI sheet which user selects from drop downs in C2:D2.

I have managed to get the code to find the right row each time by searching columns A then B in the "data" sheet.But when it comes to the copy paste/transpose column H from "KPI" sheet into the right row on the "table" sheet it throws up a 424 error.

I might be missing something really obvious so any help is appreciated.

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(cell.Offset(0, 1), cell.Offset(0, 7)).PasteSpecial Transpose:=True   
    Else        
        MsgBox ("Error")
    End If
End Sub

Upvotes: 0

Views: 58

Answers (1)

PeepDeep
PeepDeep

Reputation: 53

I needed more coffee. I hadn't spotted that is was referencing "cell" instead of "found". Today I learned that "cell" is not a vba function, and was actually something I had dimensioned in my older code, and was the equivalent of "found".

Upvotes: 1

Related Questions