Workbook 1 holds a table of information. Workbook 2 is where I run the macro. It temporarily opens Workbook 1 to return information using the Match function, populating column AF in workbook 2 based on whether a match is found.
For each row where a match is found in workbook 1, workbook 2 should populate column AF with the text "360 on-hand".
The problem is that the macro will return "360 on-hand" for some of the matches, and will not for others that in fact do match, leaving column AF blank. I have highlighted yellow about 5- 6 examples in both workbooks where the Match should work, but does not. The “match” is simply ignored.
For the code to work, please edit the path to point to wherever workbook 1 resides. Then open workbook 2 and run the macro.
Workbook 1 name is “J26 360 ThermoType database copy.xls”
Workbook 2 name is “export.xls"
Code is as follows:
Sub DIE_360_ON_HAND_TEST()
'THIS MACRO RUNS A LOOKUP IN THE 360 DIES TO SEE WHICH ARE ON-HAND
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
'OPEN TABLE OF 360’S
Workbooks.Open filename:="YOUR PATH HERE\J26 360 ThermoType database copy.xls"
Windows("EXPORT.xls").Activate
'REMOVE AUTOFILTER
Rows("1:1").Select
Range("E1").Activate
Selection.AutoFilter
‘CLEAR AND RENAME COLUMN AF
Range("AF:AF").ClearContents
Range("AF:AF").ColumnWidth = 13
Range("AF1").FormulaR1C1 = "360 DIE?"
'INSERT FORUMULA AT COLUMN AF
Range("AF2", Cells(FinalRow, "AF")).FormulaR1C1 = _
"=IF(RC[3]<>""360"","""",IF(ISNA(MATCH(SUBSTITUTE(RC[-21]," _
& """-"",""""),'[J26 360 ThermoType database copy.xls]360_2012'!C1,0))" _
& ",""please add design"",""360 ON-HAND""))"
‘MAKE RESULT VALUES INSTEAD OF FORMULAS
Range("AF2:AF" & FinalRow).Value = Range("AF2:AF" & FinalRow).Value
'Close 360 database without saving
Workbooks("J26 360 ThermoType database copy.xls").Close False
'ADD AUTOFILTER
Rows("1:1").Select
Range("E1").Activate
Selection.AutoFilter
Application.ScreenUpdating = False
End Sub
J26 360 ThermoType database copy.xls
Attachment 399906
Bookmarks