Need help for seach marco

[Xineq]

Hey mr.Excel

I'm trying to make a marco that seach the active sheet for the value of the sheet FAM cell A1. Is this possibel to do??.

Dim i, lastrow
For i = 1 To lastrow
If ActiveSheet.Cells(i, "G").Value = Worksheets("FAM").Cells(A1).Value Then
    ActiveSheet.Cells(i, "G").EntireRow.Copy Destination:=Sheets("Counting").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i

Hoping to can help me see what is wrong with me code

 

[Momentman]

I have uploaded a test sheet here https://www.dropbox.com/s/i59qsvqt1bnb36v/ForTest.xlsm?dl=0

The value in FAM!A1 is Jane, Run the code and see if the row from Sheet6 is copied to the counting sheet(that should be row 9)

I guess the you always set the sheet you want to run the code on as the active sheet before running the code

 

[Xineq]

I have uploaded a test sheet here https://www.dropbox.com/s/i59qsvqt1bnb36v/ForTest.xlsm?dl=0

The value in FAM!A1 is Jane, Run the code and see if the row from Sheet6 is copied to the counting sheet(that should be row 9)

I guess the you always set the sheet you want to run the code on as the active sheet before running the code

Okay this is abit wierd your code is working perfectly, and also the modified one to. I have also tried setting up a new doc for some testing and it works there to getting abit pissed :/

 

[Momentman]

Fair enough.

So the culprit is your workbook right? Well, that takes us one step closer to the solution or so i think

 

[Momentman]

Same as above

 

[Xineq]

Okay i got it to work, bye restarting excel and transfer my data to a new master dok. I think it was the workbook that was bugged or somthing, anyway thanks alot for the help really made my work lot alot esayer

 

[Arithos]

How bout this version?

Sub findcell()
    Dim i As Integer, FAM As Worksheet, LastRow As Integer, ActSht As Worksheet, NRow As Integer
    Dim Fi As String
 On Error GoTo errhandler
    Set FAM = Sheets("FAM")
    Set ActSht = ActiveWorkbook.ActiveSheet
    
    Fi = FAM.Cells(1, 1).Value
    
    LastRow = ActSht.Cells(Rows.Count, 7).End(xlUp).Row + 1
    For i = 1 To LastRow
       If Cells(i, 7).Value = Fi Then
       NRow = Sheets("Counting").Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
        Sheets("Counting").Rows(NRow).Value = Rows(i).Value
       End If
    Next
Exit Sub
    
errhandler:
    MsgBox "Error occurred"
End Sub

 

[Arithos]

Ok, just saw you got it solved.

 

[Xineq]

@Arithos Thanks anyway man, maybe i'm going to be able to use your code anyway

 

[Momentman]

Ok, just saw you got it solved.

It never hurts to have more than one working solution An sure the OP would keep it in his archives

 

[Arithos]

@Arithos Thanks anyway man, maybe i'm going to be able to use your code anyway

Glad to help, This way of coding is, for me, very easy to understand (logical even! :P). And similar ways of sorting/moving data using this approach is easy to build up, and understand.