| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find funtion in macro
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find funtion in macro
'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 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find funtion in macro
Thanks,
"Daniel.C" wrote: '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 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find funtion in macro
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. "orquidea" wrote: Thanks, "Daniel.C" wrote: '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 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find funtion in macro
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 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. "orquidea" wrote: Thanks, "Daniel.C" wrote: '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 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find funtion in macro
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 "Daniel.C" wrote: 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 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. "orquidea" wrote: Thanks, "Daniel.C" wrote: '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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| create a report and have a search funtion to find data | Excel Discussion (Misc queries) | |||
| How do I find age in years from anniversary date (Age funtion)? | Excel Worksheet Functions | |||
| Get Macro warning, but can't find Macro | Excel Worksheet Functions | |||
| If Funtion | Excel Worksheet Functions | |||
| Excel funtion to find how many boxes in larger container | Excel Worksheet Functions | |||
Linear Mode
