Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
Here is my situation:
First, I have a spreadsheet that has shortened names of ~ 100 facilities along with a corresponding absolute reference code (number/letter mix). For example, Chicago may have a code like GA123. Second, I have a master spreadsheet that includes tons of programming with charts and sorts. Third, each month I need to pull a downloaded file (spreadsheet 3) from an external source which has data associated with the facilities. The external souce uses very long names sometimes, so my master spreadsheet has some coding that copies the shortened versions from spreadsheet 1 into spreadsheet 3. My problem has started recently when a few facilities were sold and a few were bought. I would like my master spreadsheet to look at the plants listed in spreadsheet 3 and then match them by code with spreadsheet 1, and then replace the long version names in spreadsheet 3. If there is a plant change, I would like for the spreadsheet 1 to be updated automatically with the name and code from spreadsheet 3. I know it's a bit confusing, so if you need more information, I can provide. thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
Your problem doesn't sound very complicated. except I need more details. It
would help to have sample data, columns, and worksheet names. The only caveoat is how to translate long names to short names.. there may be a little special coding to handle the translation. "Jeff Gross" wrote: Here is my situation: First, I have a spreadsheet that has shortened names of ~ 100 facilities along with a corresponding absolute reference code (number/letter mix). For example, Chicago may have a code like GA123. Second, I have a master spreadsheet that includes tons of programming with charts and sorts. Third, each month I need to pull a downloaded file (spreadsheet 3) from an external source which has data associated with the facilities. The external souce uses very long names sometimes, so my master spreadsheet has some coding that copies the shortened versions from spreadsheet 1 into spreadsheet 3. My problem has started recently when a few facilities were sold and a few were bought. I would like my master spreadsheet to look at the plants listed in spreadsheet 3 and then match them by code with spreadsheet 1, and then replace the long version names in spreadsheet 3. If there is a plant change, I would like for the spreadsheet 1 to be updated automatically with the name and code from spreadsheet 3. I know it's a bit confusing, so if you need more information, I can provide. thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
Spreadsheet 1 example data:
A B Facility Name Facility Name EC1AA Chicago BD2EC Paris Spreadsheet 2 example data: A B EC1AA Chicago Focused Factory BD2EC Paris Center of Research and Study Master Spreadsheet is where code is located behind a macro button that completes data updating, query updating, chart updating (~1000 different charts) for data and recoloring based on new data. If you need more, I can provide. Thanks. Jeff "Joel" wrote: Your problem doesn't sound very complicated. except I need more details. It would help to have sample data, columns, and worksheet names. The only caveoat is how to translate long names to short names.. there may be a little special coding to handle the translation. "Jeff Gross" wrote: Here is my situation: First, I have a spreadsheet that has shortened names of ~ 100 facilities along with a corresponding absolute reference code (number/letter mix). For example, Chicago may have a code like GA123. Second, I have a master spreadsheet that includes tons of programming with charts and sorts. Third, each month I need to pull a downloaded file (spreadsheet 3) from an external source which has data associated with the facilities. The external souce uses very long names sometimes, so my master spreadsheet has some coding that copies the shortened versions from spreadsheet 1 into spreadsheet 3. My problem has started recently when a few facilities were sold and a few were bought. I would like my master spreadsheet to look at the plants listed in spreadsheet 3 and then match them by code with spreadsheet 1, and then replace the long version names in spreadsheet 3. If there is a plant change, I would like for the spreadsheet 1 to be updated automatically with the name and code from spreadsheet 3. I know it's a bit confusing, so if you need more information, I can provide. thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
Changge the two constants a the beginning of the macro as necessary.
Sub replacenames() Const MasterSheet = "Master" 'contains long names Const ShortNamesSheet = "Sheet1" With Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With With Sheets(MasterSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A2:A" & MasterLastrow) For Each FacilityCode In MasterRange Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues) If Not c Is Nothing Then FacilityCode.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next FacilityCode End With End Sub "Jeff Gross" wrote: Spreadsheet 1 example data: A B Facility Name Facility Name EC1AA Chicago BD2EC Paris Spreadsheet 2 example data: A B EC1AA Chicago Focused Factory BD2EC Paris Center of Research and Study Master Spreadsheet is where code is located behind a macro button that completes data updating, query updating, chart updating (~1000 different charts) for data and recoloring based on new data. If you need more, I can provide. Thanks. Jeff "Joel" wrote: Your problem doesn't sound very complicated. except I need more details. It would help to have sample data, columns, and worksheet names. The only caveoat is how to translate long names to short names.. there may be a little special coding to handle the translation. "Jeff Gross" wrote: Here is my situation: First, I have a spreadsheet that has shortened names of ~ 100 facilities along with a corresponding absolute reference code (number/letter mix). For example, Chicago may have a code like GA123. Second, I have a master spreadsheet that includes tons of programming with charts and sorts. Third, each month I need to pull a downloaded file (spreadsheet 3) from an external source which has data associated with the facilities. The external souce uses very long names sometimes, so my master spreadsheet has some coding that copies the shortened versions from spreadsheet 1 into spreadsheet 3. My problem has started recently when a few facilities were sold and a few were bought. I would like my master spreadsheet to look at the plants listed in spreadsheet 3 and then match them by code with spreadsheet 1, and then replace the long version names in spreadsheet 3. If there is a plant change, I would like for the spreadsheet 1 to be updated automatically with the name and code from spreadsheet 3. I know it's a bit confusing, so if you need more information, I can provide. thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
I used your code and modified it a bit as follows:
Sub replacenames() Const MasterSheet = "C:\Reports\DNLD\[HS_Monthly.xls]HS_Monthly!" 'contains long names Const ShortNamesSheet = "C:\Reports\[Master Plant Names.xls]Sheet1!" With Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With With Sheets(MasterSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A9:A" & MasterLastrow) For Each Plant_GSDB_Code In MasterRange Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues) If Not c Is Nothing Then Plant_GSDB_Code.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next Plant_GSDB_Code End With End Sub Now I'm getting an error "Subscript out of range" related to the line starting near the top with "With Sheets(ShortNamesSheet)" Any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
VBA references aren't the same as the worksheet. I modified your changes.
You also have to open workbooks in VBA where in worksheets you can just reference the workbooks without openning. the workbook where the code is running is called Thisworkbook. Sub replacenames() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const PlantNameWbkName = "C:\Reports\Master Plant Names.xls" Const MasterSheet = "HS_Monthly" 'contains long names Const ShortNamesSheet = "Sheet1!" Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=PlantNameWbkName Set PlantNameWbk = ActiveWorkbook With PlantNameWbk.Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With With MonthlyWbk.Sheets(MasterSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A9:A" & MasterLastrow) For Each Plant_GSDB_Code In MasterRange Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues) If Not c Is Nothing Then Plant_GSDB_Code.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next Plant_GSDB_Code End With MonthlyWbk.Close PlantNameWbk.Close End Sub "Jeff Gross" wrote: I used your code and modified it a bit as follows: Sub replacenames() Const MasterSheet = "C:\Reports\DNLD\[HS_Monthly.xls]HS_Monthly!" 'contains long names Const ShortNamesSheet = "C:\Reports\[Master Plant Names.xls]Sheet1!" With Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With With Sheets(MasterSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A9:A" & MasterLastrow) For Each Plant_GSDB_Code In MasterRange Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues) If Not c Is Nothing Then Plant_GSDB_Code.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next Plant_GSDB_Code End With End Sub Now I'm getting an error "Subscript out of range" related to the line starting near the top with "With Sheets(ShortNamesSheet)" Any ideas? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
Remove this close statement, otherwise, your workbook will close
MonthlyWbk.Close "Joel" wrote: VBA references aren't the same as the worksheet. I modified your changes. You also have to open workbooks in VBA where in worksheets you can just reference the workbooks without openning. the workbook where the code is running is called Thisworkbook. Sub replacenames() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const PlantNameWbkName = "C:\Reports\Master Plant Names.xls" Const MasterSheet = "HS_Monthly" 'contains long names Const ShortNamesSheet = "Sheet1!" Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=PlantNameWbkName Set PlantNameWbk = ActiveWorkbook With PlantNameWbk.Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With With MonthlyWbk.Sheets(MasterSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A9:A" & MasterLastrow) For Each Plant_GSDB_Code In MasterRange Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues) If Not c Is Nothing Then Plant_GSDB_Code.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next Plant_GSDB_Code End With MonthlyWbk.Close PlantNameWbk.Close End Sub "Jeff Gross" wrote: I used your code and modified it a bit as follows: Sub replacenames() Const MasterSheet = "C:\Reports\DNLD\[HS_Monthly.xls]HS_Monthly!" 'contains long names Const ShortNamesSheet = "C:\Reports\[Master Plant Names.xls]Sheet1!" With Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With With Sheets(MasterSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A9:A" & MasterLastrow) For Each Plant_GSDB_Code In MasterRange Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues) If Not c Is Nothing Then Plant_GSDB_Code.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next Plant_GSDB_Code End With End Sub Now I'm getting an error "Subscript out of range" related to the line starting near the top with "With Sheets(ShortNamesSheet)" Any ideas? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
Thanks for all of your help on this. I made the changes you stated and
although it goes further, I am still getting an out of range error. Here is the code that you provided with a small error that I was able to fix myself in your Const ShortNamesSheet where you had the ending ! which I removed. Now the error shows up at the second "With" statement. I can't seem to find any obvious errors myself. Sub replacenames() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const PlantNameWbkName = "C:\Reports\Master Plant Names.xls" Const MasterSheet = "HS_Monthly" 'contains long names Const ShortNamesSheet = "Sheet1" Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=PlantNameWbkName Set PlantNameWbk = ActiveWorkbook With PlantNameWbk.Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With With MonthlyWbk.Sheets(MasterSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A9:A" & MasterLastrow) For Each Plant_GSDB_Code In MasterRange Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues) If Not c Is Nothing Then Plant_GSDB_Code.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next Plant_GSDB_Code End With PlantNameWbk.Close End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in External document
I was able to figure out some minor changes to your code and now it appears
to be running correctly. Here is the final code for your information: Sub replacenames() Const MonthlyNameWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const PlantNameWbkName = "C:\Reports\Master Plant Names.xls" Const MasterNamesSheet = "HS_Monthly" 'contains long names Const ShortNamesSheet = "Sheet1" 'contains short names Workbooks.Open Filename:=PlantNameWbkName Set PlantNameWbk = ActiveWorkbook With PlantNameWbk.Sheets(ShortNamesSheet) ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set ShortRange = .Range("A2:A" & ShortLastrow) End With Workbooks.Open Filename:=MonthlyNameWbkName Set MonthlyNameWbk = ActiveWorkbook With MonthlyNameWbk.Sheets(MasterNamesSheet) MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set MasterRange = .Range("A9:A" & MasterLastrow) For Each PlantGSDBCode In MasterRange Set c = ShortRange.Find(What:=PlantGSDBCode, LookIn:=xlValues) If Not c Is Nothing Then PlantGSDBCode.Offset(rowoffset:=0, columnoffset:=1) = _ c.Offset(rowoffset:=0, columnoffset:=1) End If Next PlantGSDBCode End With MonthlyNameWbk.Save MonthlyNameWbk.Close PlantNameWbk.Close End Sub Thanks a lot - you've been a great help. I'm trying to locate a training class in VBA for Excel and it's a bit hard since .Net has come out. Again, thanks - you've made a big difference. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup in External Worksheet | Excel Worksheet Functions | |||
Retrieve data from external excel document | Excel Programming | |||
printing an external document | Excel Programming | |||
Lookup Value in an External Spreadsheet | Excel Discussion (Misc queries) | |||
Lookup in data from external datasource | Excel Worksheet Functions |