Find funtion in macro

[orquidea]

Hi:

I am trying to write a macro which searches for names of companies listed in
one file (Switch Accounts) on other file with data ("ord-dump).


'open file with list
Workbooks.Open Filename:="H:\Switch Accounts.xls"
'the list of the names of the companies start on A2
RangeCount = 2
Range("a" & RangeCount).Select

'activate file with data
Windows("ord-dump.xls").Activate

Sheets.Select

'Find company

Cells.Find(What:=( I don't know how to code to find the active cell from
file Switch Accounts.xls), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


End Sub

 

[Daniel.C]

'open file with list
Workbooks.Open Filename:="H:\Switch Accounts.xls"
'the list of the names of the companies start on A2
RangeCount = 2
Var = Range("a" & RangeCount)

'activate file with data
Windows("ord-dump.xls").Activate

Sheets.Select

'Find company

Cells.Find(What:=Var, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

End Sub
HTH
Daniel

 

[orquidea]

Thanks,

'open file with list
Workbooks.Open Filename:="H:\Switch Accounts.xls"
'the list of the names of the companies start on A2
RangeCount = 2
Var = Range("a" & RangeCount)

'activate file with data
Windows("ord-dump.xls").Activate

Sheets.Select

'Find company

Cells.Find(What:=Var, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

End Sub
HTH
Daniel

 

[orquidea]

Daniel

Sorry to botter you again. In ref. to the same macro, I set a loop which is
supposed to go over all the list in file Switch Accounts. There is a list
of 10 different companies, but instead of taking the accounts from switch
list is taking the accounts of the file ord-dum. The code is below. I can't
figure out where the error is

'open file with list
Workbooks.Open Filename:="H:\Switch Accounts.xls"

RangeCount = 2
BillAccount = Range("a" & RangeCount)

'activate file with data

Windows("ord-dump.csv").Activate

Sheets.Select

Do Until ActiveCell.Offset(2, 0) = ""

'Find cell
Cells.Find(What:=(BillAccount), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


Selection.CurrentRegion.Select
Selection.Copy

Sheets.Add

Range("A2").Select
ActiveSheet.Paste


RangeCount = RangeCount + 1

Loop


Thanks in advance.

 

[Daniel.C]

I need some precisions :
1."Sheets.Select" selects alls sheets. You can't do a "Find" command on
more than a sheet.
2."Do Until ActiveCell.Offset(2, 0) = """ What is the original position
of ActiveCell ? In what workbook is it ?
Daniel

 

[orquidea]

Daniel

1. Sheets select - that comand was there in error, I don't need it
2. "Do until ActiveCell.offset(2,0) = "" I believe this is a wrong comand

The original position is in the file Switch Accounts.xls. This file has 10
names of companies. The macro has to find every name of this list (file) in
the file Ord.Dump. The variable BillAccount is for every name of the company
in Swith Accounts. Once the BillAccount has been found in the Ord-Dump file
(there will be a few rows with data of that company), the macro selects the
current region and copy, adds a new sheet and paste the selected information
in the new sheet in file ord-dump. Then the macro has to go back to Switch
Accounts and go to the next row (that's why I used the counter) which will be
the next varible BillAccount and do the process again.
Thanks a lot for your help.
Escarly