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]

First of all, does your code not work?

From what I see, you are searching in column G for the value in sheets FAM cell A1, and on whichever row it is found, it copies that entire row to a sheet named Counting. Is this what you want to achieve?

I know the range.find method might be a better option but i cant say much about which will be more suited to your needs until i have gotten answers to the above questions

Victor

 

[Xineq]

My code don't work, i and i dont know why.

Yes i'm searching in column G for the value in sheets FAM cell A1, and on whichever row it is found, it copies that entire row to a sheet named Counting that is correct.

This range.find method you talk about how does that work??

 

[Momentman]

My code don't work, i and i dont know why.

Yes i'm searching in column G for the value in sheets FAM cell A1, and on whichever row it is found, it copies that entire row to a sheet named Counting that is correct.

This range.find method you talk about how does that work??

COlumn G of which sheet?

 

[Xineq]

COlumn G of which sheet?

It is seaching in Column G in the active sheet

 

[Momentman]

Give this a try

Sub findcell()
    Dim I As Integer, ws As Worksheet, foundrow As Integer, wks As Worksheet
    Set ws = Worksheets("FAM")
    Set wks = ActiveWorkbook.ActiveSheet
    On Error GoTo errhandler
    foundrow = wks.Range("G:G").Find(ws.Range("a1"), wks.Range("G1")).Row
    Rows(foundrow).EntireRow.Copy Sheets("Counting").Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row)
errhandler:
    MsgBox "Value being sought not found"
End Sub

Or your code slightly modified

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

Texts in Red are what i altered

 

[Xineq]

Give this a try

Sub findcell()
    Dim I As Integer, ws As Worksheet, foundrow As Integer, wks As Worksheet
    Set ws = Worksheets("FAM")
    Set wks = ActiveWorkbook.ActiveSheet
    On Error GoTo errhandler
    foundrow = wks.Range("G:G").Find(ws.Range("a1"), wks.Range("G1")).Row
    Rows(foundrow).EntireRow.Copy Sheets("Counting").Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row)
errhandler:
    MsgBox "Value being sought not found"
End Sub

Or your code slightly modified

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

Texts in Red are what i altered

Thanks man trying it out

 

[Xineq]

Okay i have tryede them both. the findcell sub only gives me the error msgbox, and ville not print to the Counting sheet. the slightly modified code of my still dont give me a result :S

 

[Momentman]

Interesting, 'cos I tried both codes and they both worked

The findcell will give you that messagebox if no value is found, are you sure the value you seek is in the activesheet and in column G? Does your modified code give any error or it doesnt just work?

 

[Xineq]

Interesting, 'cos I tried both codes and they both worked

The findcell will give you that messagebox if no value is found, are you sure the value you seek is in the activesheet and in column G? Does your modified code give any error or it doesnt just work?

I have tried several different values or names, with no result. The modified code just run it, and gives no output