Read and find

[Tom]

Hi.

I need a procedure to read a name in Document1 then go and find it in
Document2. It then pauses for 10 seconds before returning to Document1 and
reads the next name below. This is repeated until the name is a blank. Any
help is much appreciated.

TIA
Tom

 

[Don Guillett]

First, let's get the nomenclature.
A file is called a workbook
A worksheet within a file is called a worksheet or sheet. Sometimes tab..
Why the pause?
What you may want is a FINDNEXT macro (look in the vba help index) with a
msgbox ?? If all else fails,

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Tom]

Let me explain the reason behind my request. I'm trying to convert an Excel4
macro to an Excel11 macro but haven't been able to find a way around steps
like "=PAUSE()", which is no longer supported. Or "=GET.CELL(5)" - a
command line say in A65 of the xlm macro that reads the content of a cell in
File1 and able to be called up in File2 with the command "=FIND(A65)" .
However, I kow some clever people among you lot can come up with alternate
solutions to them.

----- Original Message -----
From: "Don Guillett" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Friday, December 04, 2009 2:53 PM
Subject: Re: Read and find

First, let's get the nomenclature.
A file is called a workbook

Call it what you like. Excel program developers do refer to a file as a
document. Hence, they use the term "=GET.DOCUMENT(1)". Who are we to argue
with them.

A worksheet within a file is called a worksheet or sheet. Sometimes tab..
Why the pause?

It allows the user to do a quick check for errors/inconsistancies in the
figures displayed or to jot down any piece of information one is looking
for.

What you may want is a FINDNEXT macro (look in the vba help index) with a
msgbox ??

Isn't there a neater solution?

If all else fails,
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

Example:
Read the content say, "ABC" in cell A2 of File1 and hold it in the Excel
memory. Activate File2 and find "ABC" there, then pause for a set time
period. Activate File1 and do an offset one step down the column. Repeat the
above. Expected result - stop if the read content of File1 is a blank.
Sounds simple but can be a challenge to replicate those steps with Excel11.

 

[Don Guillett]

At age 73, I have been doing this awhile. We NO longer call them documents.
This macro will look in a workbook called Menu.xls on a worksheet called
menu for the match of range c1 of the source workbook. Assumes that your
macro is in the workbook with range c1 as are you and both files are open.
However, you need not goto to the other workbook to do most things. What do
you really want to do? I repeat my offer to send your file(s)

Sub findcellinotherfile()
Dim myrange As String
myrange = Range("c1")
sourcefile = "Menu.xls"
Windows(sourcefile).Activate
sheets("Menu").Select
On Error Resume Next
ActiveSheet.Columns(1).Find(What:=myrange, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Select
End Sub

 

[Tom]

Thanks Don for getting out step1. The next 2 steps may be a bit more tricky
I think.

Tom

 

[Don Guillett]

I repeat, What is it that you want to do?

 

[Tom]

I did explain it from the beginning and then with an Example. See below.

Tom