Lookup in External document

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
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
 
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?
 
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
 
Remove this close statement, otherwise, your workbook will close

MonthlyWbk.Close


Joel said:
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 said:
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?
Click to expand...
Click to expand...
 
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
 
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.
 
Back
Top