Look for only exact match and not like match.

pedie

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Hi,

Mycode looks in col A but find the like match and then updates..please advice on what I should do to get exact match
I really doubt there is something wrong on the blue line....Is that coded correctly?
I am trying to find ik in A:A and then find "" cell below it and then paste the copied content..

Thanks alot for helping me in Advance
Pedie
Code: 
[FONT=Arial]Sub Update()[/FONT]
[FONT=Arial]Application.ScreenUpdating = False[/FONT]
[FONT=Arial]Dim wb As Workbook, MyFile As String[/FONT]
[FONT=Arial]Dim ik As String[/FONT]
[FONT=Arial]ik = ThisWorkbook.Sheets("MyDATA").Range("C4").Value[/FONT]
[FONT=Arial]Sheets("MyDATA").Range("C2:C13").Copy[/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[FONT=Arial]MyFile = "C:\TRY.xls"[/FONT]
[FONT=Arial]   On Error Resume Next[/FONT]
[FONT=Arial]   Set wb = Workbooks.Open(Filename:=MyFile)[/FONT]
[FONT=Arial]   On Error GoTo 0[/FONT]
[FONT=Arial]   If wb Is Nothing Then MsgBox "File not in accessabl…": Exit Sub[/FONT]
[FONT=Arial]   Windows("TRY.xls").Activate[/FONT]
[FONT=Arial]   Sheets("MAIN").Activate[/FONT]
[FONT=Arial][COLOR=blue]    If Not ActiveWorkbook.ActiveSheet.Range("A1:A65500").Find(ik, LookAt:=xlWhole) Is Nothing Then[/COLOR][/FONT]
[FONT=Arial]    ActiveCell.Select[/FONT]
[FONT=Arial]    Do Until ActiveCell.Value = ""[/FONT]
[FONT=Arial]    ActiveCell.Offset(1, 0).Select[/FONT]
[FONT=Arial]    Loop[/FONT]
[FONT=Arial]    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _[/FONT]
[FONT=Arial]    xlNone, SkipBlanks:=False, Transpose:=True[/FONT]
[FONT=Arial]    wb.Close savechanges:=True[/FONT]
[FONT=Arial]    ThisWorkbook.Activate[/FONT]
[FONT=Arial]    Sheets("MyDATA").Select[/FONT]
[FONT=Arial]    Application.CutCopyMode = False[/FONT]
[FONT=Arial]    MsgBox "OK"[/FONT]
 
[FONT=Arial]    Else[/FONT]
[FONT=Arial]    wb.Close savechanges:=False[/FONT]
[FONT=Arial]    ThisWorkbook.Activate[/FONT]
[FONT=Arial]    Sheets("MyDATA").Select[/FONT]
[FONT=Arial]    Application.CutCopyMode = False[/FONT]
[FONT=Arial]    MsgBox ik & " not found...."[/FONT]
[FONT=Arial]   End If[/FONT]
[FONT=Arial]Application.ScreenUpdating = True[/FONT]
[FONT=Arial]End sub[/FONT]
<o:p></o:p>
 
Last edited:
Sort by date Sort by votes
Pedie - a bit more information would help. I dont think it is the finding of the "ik" that is the prob, more the selection of that cell and what to do next.

Are there already blank rows under the cell in Col A where you find "ik"?

After your blue line I replaced
Code: 
 ActiveCell.Select
with
Code: 
 Selection.FindNext(After:=ActiveCell).Activate

which helped find each "ik"

HTH, Mr R.
 
Upvote 0
Can you please rewrite everything for me?
I want to search only in range(A:A) and not where else...and there will be only 1 ik in this col which is already pre-defined.


PS: what I am trying to achieve here is that every user has different code ids that is ik Range("C4").Value and updating info from different book by finding their code in this sheet and then updating info in the code area only....


Please advice.
 
Upvote 0
Pedie,

I may be missing something but I can't get the line of code, to find anything.
Does it work at all, for you.
 
Upvote 0
Brian, i dont know. when i run the code it randomly paste the copied content anywhere...i'm confused:confused: i just mixed code from here and there...and thought it would work as i thought but not so..

if you have time can you please make this work?


yes you're right...it does not find anything..i just tried this way and then i came to know it does not work...:)

Code: 
Sub y()
Dim ik As String
ik = Range("C4").Value
   Sheets("Sheet2").Activate
    If Not ActiveWorkbook.ActiveSheet.Range("A1:A65500").Find(ik, LookAt:=xlWhole) Is Nothing Then
    ActiveCell.Select
    MsgBox "found"
    Else
    MsgBox "not found"
    Exit Sub
    End If
End Sub
 
Last edited:
Upvote 0
It's late here so Ill look again tomorrow.

this works. try it like your sub y.<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> c<SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> ik<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><br>  ik = ThisWorkbook.Sheets(1).Range("C4").Value<br>  <SPAN style="color:#00007F">Set</SPAN> c = Range("A1:A65500").Find(ik, LookAt:=xlWhole)<br>    <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> c<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><br>      c.Select<br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br></FONT>

What does your sheet look like, is it like this.
Excel Workbook
ABCDEFGH
3
4
5Ik ID1
6datadatadatadatadata>
7datadatadatadatadata>
8datadatadatadatadata>
9datadatadatadatadata>
10datadatadatadatadata>
11datadatadatadatadata>
12datadatadatadatadata>
13
14
15
16
17
18Ik ID2
19datadatadatadatadata>
20datadatadatadatadata>
21datadatadatadatadata>
22datadatadatadatadata>
23
24
Sheet1
Excel 2010
 
Upvote 0
Okay Brian, you have a good night! 'm trying this out.
Thanks again! :)


Yes this works well now.
Thank you so much!
 
Last edited:
Upvote 0
yes my sheet looks like this. Thanks again!
Sorry i missed out on this question so..

Thanks again.;)
Excel Workbook
ABCDEFGH
3
4
5Ik ID1
6datadatadatadatadata>
7datadatadatadatadata>
8datadatadatadatadata>
9datadatadatadatadata>
10datadatadatadatadata>
11datadatadatadatadata>
12datadatadatadatadata>
13
14
15
16
17
18Ik ID2
19datadatadatadatadata>
20datadatadatadatadata>
21datadatadatadatadata>
22datadatadatadatadata>
23
24
Sheet1

Excel 2010
 
Upvote 0
Pedie,

Try this.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook, found <SPAN style="color:#00007F">As</SPAN> Range<br>MyFile = "C:\TRY.xls"<br>ik = ThisWorkbook.Sheets("MyDATA").Range("C4").Value<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br>  <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open(Filename:=MyFile)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>  <SPAN style="color:#00007F">If</SPAN> wb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> MsgBox "File not in Accessible...": <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>  <br>  <SPAN style="color:#00007F">With</SPAN> Workbooks("TRY.xls").Sheets("MAIN")<br>    <SPAN style="color:#00007F">Set</SPAN> found = .Range("A1:A65500").Find(ik, LookAt:=xlWhole)<br>      <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        ThisWorkbook.Sheets("MyDATA").Range("C2:C13").Copy<br>          .Range(found.Address).End(xlDown).Offset(1, 0).PasteSpecial _<br>                Paste:=xlPasteValuesAndNumberFormats, Transpose:=<SPAN style="color:#00007F">True</SPAN><br>              wb.Close savechanges:=<SPAN style="color:#00007F">True</SPAN><br>            Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>          MsgBox "OK"<br>          Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <br>      wb.Close savechanges:=<SPAN style="color:#00007F">False</SPAN><br>    MsgBox ik & " not found...."<br>  Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Brian, thank you so much again.
Thank you for always being there....

Pedie;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Add a code or number at the end of the file name using VBA (Looped)
Replies
3
Views
357
HighAndWilder
H
H
  • Question Question
Macro to Open File and limit selection where files contains "Parts" in C
Replies
4
Views
349
howard
H
C
  • Question Question
VBA code stuck (extract data from worksheet)
Replies
1
Views
469
Alex Blakenburg
A
I
  • Solved
Simplify my working code please
Replies
7
Views
298
ipbr21054
I
C
  • Solved
Workbook Won't Close
Replies
4
Views
182
6StringJazzer
6StringJazzer

Share this page

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
Back
Top