Look for only exact match and not like match.

[pedie]

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

[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>

 

[Mr_Roscoe]

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

 ActiveCell.Select

with

 Selection.FindNext(After:=ActiveCell).Activate

which helped find each "ik"

HTH, Mr R.

 

[pedie]

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.

 

[meldoc]

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.

 

[pedie]

Brian, i dont know. when i run the code it randomly paste the copied content anywhere...i'm 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...

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

 

[meldoc]

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
	A	B	C	D	E	F	G	H
3
4
5	Ik ID1
6	data	data	data	data	data	>
7	data	data	data	data	data	>
8	data	data	data	data	data	>
9	data	data	data	data	data	>
10	data	data	data	data	data	>
11	data	data	data	data	data	>
12	data	data	data	data	data	>
13
14
15
16
17
18	Ik ID2
19	data	data	data	data	data	>
20	data	data	data	data	data	>
21	data	data	data	data	data	>
22	data	data	data	data	data	>
23
24
Sheet1

Excel 2010

 

[pedie]

Okay Brian, you have a good night! 'm trying this out.
Thanks again!


Yes this works well now.
Thank you so much!

 

[pedie]

yes my sheet looks like this. Thanks again!
Sorry i missed out on this question so..

Thanks again.

Excel Workbook
	A	B	C	D	E	F	G	H
3
4
5	Ik ID1
6	data	data	data	data	data	>
7	data	data	data	data	data	>
8	data	data	data	data	data	>
9	data	data	data	data	data	>
10	data	data	data	data	data	>
11	data	data	data	data	data	>
12	data	data	data	data	data	>
13
14
15
16
17
18	Ik ID2
19	data	data	data	data	data	>
20	data	data	data	data	data	>
21	data	data	data	data	data	>
22	data	data	data	data	data	>
23
24
Sheet1

Excel 2010

 

[meldoc]

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>

 

[pedie]

Brian, thank you so much again.
Thank you for always being there....

Pedie