excel vba find option

GovindRajan

New Member
Joined
Nov 11, 2019
Messages
3
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
change

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

With ... NOT TESTED


Code:
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]
 
Last edited:

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
change

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

With ... NOT TESTED

Code:
[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]
 
Last edited:

GovindRajan

New Member
Joined
Nov 11, 2019
Messages
3
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,348

ADVERTISEMENT

Not sure to understand what is "next cell"

Code:
[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]
 
Last edited:

GovindRajan

New Member
Joined
Nov 11, 2019
Messages
3
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You don't need to select the cell

Code:
  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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,568
Messages
5,637,094
Members
416,957
Latest member
Brovashift

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top