excel vba find option

[GovindRajan]

hi i have a written a macro to search and find set of variables from one sheet( here give as reportsheet) with another (datasheet) and copy value next to it.
search works fine if all the search items are in datasheet but if something is not available i want reportsheet cell(k,5)to have value "need manual verification" i have tried many ways but since i am new to excel vba everything goes wrong
my code without option for search result is as follows. it would be great if someone could help me in modifying this. Thanks

Option Explicit
Sub search_and_find()


Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim variablename As String
Dim finalrow As Integer
Dim i As Integer
Dim j As String
Dim k As Integer
Dim finalrsheet As Integer


Set datasheet = Sheet1
Set reportsheet = Sheet2


reportsheet.Select
Range("a1").Select
With ActiveSheet
finalrsheet = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = 2 To finalrsheet
Cells(i, 1).Select
Selection.Copy
Sheet3.Activate
Range("a1").PasteSpecial
variablename = Range("a1")
datasheet.Activate
j = Range("h1:q3000").Find(What:=variablename).Select
Selection.Offset(0, 5).Copy
reportsheet.Activate
Cells(i, 4).PasteSpecial
Next i
reportsheet.Activate
For k = 2 To finalrsheet
If Cells(k, 2).Value = Cells(k, 4).Value Then
Cells(k, 5).Value = "No change"
Else
Cells(k, 5).Value = "Change"
End If
Next k
End Sub

 

[PCL]

change

[COLOR=#333333]j = Range("h1:q3000").Find(What:=variablename).Select
[/COLOR]

With ... NOT TESTED

Dim F

Set F =[/COLOR][COLOR=#333333]Range("h1:q3000").Find(What:=variablename)
[/COLOR]     If (Not F Is Nothing) Then
            [COLOR=#333333]cell(k,5) =  "need manual verification"[/COLOR]
[COLOR=#333333]     else
           Previous code
      End if

[/COLOR]

 

[PCL]

change

[COLOR=#333333]j = Range("h1:q3000").Find(What:=variablename).Select
[/COLOR]

With ... NOT TESTED

[COLOR=#333333]

Dim F

[/COLOR][COLOR=#333333]
[/COLOR] [COLOR=#333333]Set F =[/COLOR][COLOR=#333333]Range("h1:q3000").Find(What:=variablename)[/COLOR]
   If (Not F Is Nothing) Then
            [COLOR=#333333]cell(k,5) =  "need manual verification"[/COLOR]
[COLOR=#333333]    else
           Previous code
      End if



[/COLOR]

 

[GovindRajan]

thanks for your advice, but if i give this command find option is not selecting the cells which it found. i need that cell so that i can copy the value of the next cell. how do i proceed?

 

[PCL]

Not sure to understand what is "next cell"

[COLOR=#333333]   Range("h1:q3000").Find(What:=variablename)
[/COLOR]   If (Not F Is Nothing) Then
            [COLOR=#333333]       cell(k,5) =  "need manual verification"[/COLOR]
[COLOR=#333333]   else
      F.select
       [/COLOR]      Selection.Offset(0, 5).Copy
[FONT=Verdana]      reportsheet.Activate[/FONT]
[FONT=Verdana]      Cells(i, 4).PasteSpecial
[/FONT][COLOR=#333333]   End if

[/COLOR]

 

[GovindRajan]

Thanks for replying, i found that i was missing f.select due to which my search cell was not being selected. thank you for your reply. it works fine now

 

[MARK858]

You don't need to select the cell

  Set F = Range("h1:q3000").Find(What:=variablename)
   If Not F Is Nothing Then
      cell(k,5) =  "need manual verification"
   else
      F.Offset(0, 5).Copy reportsheet.Cells(i, 4)
   End if