Solved: Find a text in workbook and...

[Mistiqe]

Hello,

first of all I would like to apologize in advance for any mistakes in this post as English is not my native language. So bear with me please, I'm doing my best.

I started my adventure with Excel couple of days ago so I'm a newbie, but I want to learn, that's why I'm here. I can do basic Excel stuff because it's pretty easy. I do have problems with more advanced stuff though. Like now. Let me describe my assignment. Please open the attachments for better understanding.

I've got two example workbooks: info.xml and list.xml. The info.xml is missing some data which can be found in list.xml. Now, I would like to write a VBA code, a macro, that would:

1. Search the name in column A in info.xml
2. Fill out the missing data in info.xml accordingly based on list.xml

So if it finds "John Doe" in info.xml I would like the macro to fill out the "Age", "Day" and "Status" data which can be found in list.xml. Then repeat the process for the remaining names. The original file is a lot bigger, this is just an example.

I don't even know where to begin. I tried to search your forum for an answer but 1000+ pages is just too much Plus, when I use the "search" function it gives me inaccurate results as my question is too complex for the search engine and I have no idea how to define my search question. That's why I decided to start a new thread. Is there somebody who knows how to write that code? I would really appreciate any help.

[macropod]

Hi Mistique,

With both workbooks open, insert the following formulae into 'Info.xls' at:
C2 =INDEX([list.xls]Sheet1!B$1:B$100,(MATCH($A2,[list.xls]Sheet1!$A1:$A100,0)))
F2 =INDEX([list.xls]Sheet1!C$1:C$100,(MATCH($A2,[list.xls]Sheet1!$A1:$A100,0)))
Copy the formula from F2 across to G2, then copy all three formulae down as far as needed.

Note: If a name in 'Info.xls' does not appear in 'List.xls', the formulae will return '#N/A'.

[Mistiqe]

Thanks macropod, but that's not working properly. I played a bit with it and it looks like the correct formula would be:

C2 =INDEX([list.xls]Sheet1!$B$1:$B$112;(MATCH($A2;[list.xls]Sheet1!$A$1:$A$112;0)))
F2 =INDEX([list.xls]Sheet1!$C$1:$C$112;(MATCH($A2;[list.xls]Sheet1!$A$1:$A$113;0)))
G2 =INDEX([list.xls]Sheet1!$D$1:$D$112;(MATCH($A2;[list.xls]Sheet1!$A$1:$A$113;0)))

Now it's working. Thanks for pointing me to the right direction.

Ok, that's one way how to do it. But does anyone know how to create a VBA macro code that would do the same? Just for the sake of knowledge.

[macropod]

Hi Mistique,

Yes, I should have mentioned adjusting the row numbers to suit your needs. Do be aware that you'll need to use the same row ranges on both parts of each formula - your 2nd & 3rd formula refer to rows 112 and 113 (presumably, the 113 should have been 112).

As for a macro solution, we would need to know whether the two files are in the same folder and, if not, what the path to the list.xls file is. The macro could be coded to apply the worksheet formulae I posted and simply insert the results into the worksheet. Do note, though, that, unlike the formula approach, the macro's results would ordinarily be static (ie they wouldn't automatically update if you changed the values in the list.xls file) or if you changed the name in the info.xls file. You could, of course, use an event-driven macro to address the latter.

[Mistiqe]

Hi Mistique,

Do be aware that you'll need to use the same row ranges on both parts of each formula - your 2nd & 3rd formula refer to rows 112 and 113 (presumably, the 113 should have been 112).

It's just a spelling mistake. Didn't notice it when I pasted the formula.

As for a macro solution, we would need to know whether the two files are in the same folder and, if not, what the path to the list.xls file is.

Yes, both files are in the same folder.

The macro could be coded to apply the worksheet formulae I posted and simply insert the results into the worksheet.

This one I can do myself But I meant more advanced VBA code using functions, like this example:

[vba]
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
With Range("A1:A10")
Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Range("A1:A10").Find(what:="a", after:=LastCell)

If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
Debug.Print FoundCell.Address
Set FoundCell = Range("A1:A10").FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do

End If
Loop
[/vba]
This one would find all occurrences of "a" in defined cell range.

Do note, though, that, unlike the formula approach, the macro's results would ordinarily be static (ie they wouldn't automatically update if you changed the values in the list.xls file) or if you changed the name in the info.xls file. You could, of course, use an event-driven macro to address the latter.

That's no problem as values in list.xls (don't know why I wrote *.xml in the previous post...) are static anyway. As I said before - it's just for the sake of my knowledge. The formula you showed me works like a charm and it resolves my problem.

[macropod]

Hi Amy,

Here's a relatively simple way:

Sub Demo()
Dim i As Long
For i = 2 To ActiveSheet.UsedRange.Rows.Count
  Cells(i, 3).Value = Evaluate("=INDEX([list.xls]Sheet1!B$1:OFFSET([list.xls]Sheet1!B$1,MATCH(1E+306,[list.xls]Sheet1!B:B,1),),(MATCH($A" & i & ",[list.xls]Sheet1!A$1:OFFSET([list.xls]Sheet1!A$1,MATCH(" & """ * """ & ",[list.xls]Sheet1!A:A,-1),),0)))")
  Cells(i, 6).Value = Evaluate("=INDEX([list.xls]Sheet1!C$1:OFFSET([list.xls]Sheet1!C$1,MATCH(1E+306,[list.xls]Sheet1!B:B,1),),(MATCH($A" & i & ",[list.xls]Sheet1!A$1:OFFSET([list.xls]Sheet1!A$1,MATCH(" & """ * """ & ",[list.xls]Sheet1!A:A,-1),),0)))")
  Cells(i, 7).Value = Evaluate("=INDEX([list.xls]Sheet1!D$1:OFFSET([list.xls]Sheet1!D$1,MATCH(1E+306,[list.xls]Sheet1!B:B,1),),(MATCH($A" & i & ",[list.xls]Sheet1!A$1:OFFSET([list.xls]Sheet1!A$1,MATCH(" & """ * """ & ",[list.xls]Sheet1!A:A,-1),),0)))")
Next
End Sub

Note: The 'list.xls' workbook must be open for this to work.

[Mistiqe]

Who's Amy? ;p

Anyway, works like a charm. Didn't know it would be that simple. Good enough, as long as it doesn't look like a recorded macro. Thank you very much macropod.

[macropod]

Who's Amy? ;p

Sorry Mistique - working on threads in multiple forums simultaneously.

Glad to be of help.