Meatball
02-19-2010, 09:37 AM
I have the following code which was working fine for a long time. I recently changed Excel version from 97-03 to 07. I have the same problem running code now when I try on 97-03, so version does not seem to be the problem. There have also been a couple of issues with the connection to the drive holding all workbooks involved but they are working fine at the present time.
I am getting a Update link window and I do not know why. It seems to want to come up for both subs below.
I have tried rewriting the path name to Workbooks.Open Filename:="Y:\Gould Southern Info\Tool & Desc Lookup\Exacta library.xls" but does not seem to have helped.
Anyone have any ideas why I am getting this and/or how to fix it?
Private Sub ExLookup()
'
' ExLookup Macro
' Macro recorded 3/4/2009 by David D
'
'
Dim LR As Long
LR = Sheets("--Jimmy-Cost--").Range("C2000").End(xlUp).Row
Workbooks.Open Filename:="Y:\Gould Southern Info\Tool & Desc Lookup\Exacta library.xls"
ThisWorkbook.Activate
Sheets("RFQ to EX BOM Pg 2").Select
Range("C18").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[2],'[Exacta library]Sheet1'!C2:C3,2,FALSE)"
Selection.AutoFill Destination:=Range("C18:C" & LR), Type:=xlFillDefault
Range("C18:C2000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Replace What:="#N/A", Replacement:=""
Range("G18").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[Exacta library]Sheet1'!C2:C4,3,FALSE)"
Selection.AutoFill Destination:=Range("G18:G" & LR), Type:=xlFillDefault
Range("G18:G2000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Replace What:="#N/A", Replacement:=""
Selection.Replace What:="0", Replacement:=""
Call ExLibrary
Range("A2").Select
Windows("Exacta library.xls").Close
End Sub
Private Sub ExLibrary()
Dim ER As Long
ER = Sheets("RFQ to EX BOM Pg 2").Range("F" & Rows.Count).End(xlUp).Row
Dim RangeToCheck As Range, _
CellInRangeToCheck As Range
Set RangeToCheck = Range("G18:G" & ER)
For Each CellInRangeToCheck In RangeToCheck
If (CellInRangeToCheck.Value <> "") Then
Range("G5").Select
Selection = "IN EXACTA"
Range("G6").Select
Selection = "LIBRARY AS"
Exit For
End If
Next
End Sub
I am getting a Update link window and I do not know why. It seems to want to come up for both subs below.
I have tried rewriting the path name to Workbooks.Open Filename:="Y:\Gould Southern Info\Tool & Desc Lookup\Exacta library.xls" but does not seem to have helped.
Anyone have any ideas why I am getting this and/or how to fix it?
Private Sub ExLookup()
'
' ExLookup Macro
' Macro recorded 3/4/2009 by David D
'
'
Dim LR As Long
LR = Sheets("--Jimmy-Cost--").Range("C2000").End(xlUp).Row
Workbooks.Open Filename:="Y:\Gould Southern Info\Tool & Desc Lookup\Exacta library.xls"
ThisWorkbook.Activate
Sheets("RFQ to EX BOM Pg 2").Select
Range("C18").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[2],'[Exacta library]Sheet1'!C2:C3,2,FALSE)"
Selection.AutoFill Destination:=Range("C18:C" & LR), Type:=xlFillDefault
Range("C18:C2000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Replace What:="#N/A", Replacement:=""
Range("G18").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[Exacta library]Sheet1'!C2:C4,3,FALSE)"
Selection.AutoFill Destination:=Range("G18:G" & LR), Type:=xlFillDefault
Range("G18:G2000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Replace What:="#N/A", Replacement:=""
Selection.Replace What:="0", Replacement:=""
Call ExLibrary
Range("A2").Select
Windows("Exacta library.xls").Close
End Sub
Private Sub ExLibrary()
Dim ER As Long
ER = Sheets("RFQ to EX BOM Pg 2").Range("F" & Rows.Count).End(xlUp).Row
Dim RangeToCheck As Range, _
CellInRangeToCheck As Range
Set RangeToCheck = Range("G18:G" & ER)
For Each CellInRangeToCheck In RangeToCheck
If (CellInRangeToCheck.Value <> "") Then
Range("G5").Select
Selection = "IN EXACTA"
Range("G6").Select
Selection = "LIBRARY AS"
Exit For
End If
Next
End Sub