I have many serial files in a folder like spect1, spect2....spect35 and 2 vital columns D,E
these columns sometimes a #N/A or 0 or null appears in E and I dont want it to appear.
What can I do to compare
the D,E of all files and insert the value (which is text) in E when it finds #N/A or 0 or null. For example in D in 3 files
the number 49868686 shows. In one of these 3 files in E the #N/A shows in the other 2 files the material name ACC-FM30A. Well
I d like this name to be shown instead of #N/A because the macro compares the D and finds 49868686 in all 3 cases. This is
of course impossible for me so help is appreciated.
However I append these 2 columnss of many serial files with
the code that a dear member provided
I have no idea how the previous problem I mentioned could be inserted in this macro!!!!So all tasks are done at the same
Dim SummaryFile As String, SummarySheet As String, DataFile As String, DataSheet As String
Dim SearchDir As String
Dim NextRow As Long, LastDataRow As Long
Dim i As Integer
SummaryFile = ActiveWorkbook.Name
SummarySheet = ActiveSheet.Name
SearchDir = "S:Trade MarketingSELL THROUGH PROJECTÐñüôõðç ìïñöÞSPECTRAProcessed"
For i = 1 To 52
DataFile = "spect" & i & ".xls"
NextRow = Workbooks(SummaryFile).Sheets(SummarySheet).Range("D65535").End(xlUp).Row + 1
Workbooks.Open Filename:=SearchDir & "" & DataFile
DataSheet = Workbooks(DataFile).ActiveSheet.Name
LastDataRow = Workbooks(DataFile).Sheets(DataSheet).Range("D65535").End(xlUp).Row
Workbooks(DataFile).Sheets(DataSheet).Range("D5:E" & LastDataRow).Copy _
Destination:=Workbooks(SummaryFile).Worksheets(SummarySheet).Range("D" & NextRow)