First of all, I am working on a macro in Excel 2003 on a Windows XP box. I work in Excel and VBA once in a while.
This macro was written by me months ago and has worked fine running daily. The users have asked for new features and that is where I am having problems.
An error log is generated in one tracking system and exported as type ".log". My macro imports it into sheet1 of a template workbook and distributes the errors to each of the factories to review. The errors are part of a training tracking system and the latest addition is to get rid of all the previously existing errors. To do this, I placed 29,703 errors on sheet2 to compare each imported log file against.
The spreadsheet has over 50 columns, so I chose the few key columns to concatenate and placed them out beyond the 50 columns in a column of their own. The formula for cells in that column is the same on sheet1 and sheet2. It is simply "=CONCATENATE(A4,B4,AM4)".
The errors I am battling are mismatched types and Object or With variable not declared. I am off for the day, so I will respond in the morning to any additional questions.
Here are the declarations:
And here is the problem section of code:
As the code sits, I am returning no errors, but also am finding "nothing". I can eyeball a watch and the spredsheet and verify that the data is there and matches. When I use more detailed "Find" instructions, it dies on the "Find".
This macro was written by me months ago and has worked fine running daily. The users have asked for new features and that is where I am having problems.
An error log is generated in one tracking system and exported as type ".log". My macro imports it into sheet1 of a template workbook and distributes the errors to each of the factories to review. The errors are part of a training tracking system and the latest addition is to get rid of all the previously existing errors. To do this, I placed 29,703 errors on sheet2 to compare each imported log file against.
The spreadsheet has over 50 columns, so I chose the few key columns to concatenate and placed them out beyond the 50 columns in a column of their own. The formula for cells in that column is the same on sheet1 and sheet2. It is simply "=CONCATENATE(A4,B4,AM4)".
The errors I am battling are mismatched types and Object or With variable not declared. I am off for the day, so I will respond in the morning to any additional questions.
Here are the declarations:
Code:
' Declare macro wide variables
Dim currentRow As Integer, sheetIndex As Integer
Dim rowCount As Long, lastRow As Long
Dim pctDone As Single
Dim Filename As String, currentCell As String
Dim curCell As Range, curRange As Range, tarRange As Range, tarCell As RangeAnd here is the problem section of code:
Code:
' ============================== Compare errors to baseline errors ================================================
' Update progress bar
ProgressBar.LabelCaption = "Comparing errors to baseline..."
ProgressBar.FrameRibbon.Caption = "0%"
ProgressBar.LabelRibbon.Width = 0
DoEvents
' Copy concatenation formula into sheet1 after import
ActiveWorkbook.Sheets(2).Activate ' Get formula
Range("AT1").Select
Selection.Copy
ActiveWorkbook.Sheets(1).Activate ' Paste formula
Columns("AT").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
' Get a count of rows from the old errors sheet for looping
ActiveWorkbook.Sheets(2).Activate ' Get formula
Set curRange = Range([AT1].End(xlUp), [AT65536].End(xlUp))
rowCount = curRange.Rows.Count
Set tarCell = Cells(1, 46)
' Find each row in Sheet2 and delete from Sheet1
For rowIndex = 1 To rowCount
' Calculate the percent done and update and ribbon
pctDone = sheetIndex / Sheets.Count
With ProgressBar
.FrameRibbon.Caption = Format(pctDone, "0%")
.LabelRibbon.Width = pctDone * .FrameRibbon.Width
End With
ActiveWorkbook.Sheets(2).Activate ' Get current row
currentCell = (Cells(rowIndex, "AT").Value)
ActiveWorkbook.Sheets(1).Activate
Set tarRange = Range([AT1].End(xlUp), [AT65536].End(xlUp))
' Search sheet1 for every occurrence of the row
With tarRange
' Set tarCell = .Find(What:=currentCell, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows).Offset(-1, 0)
' Set tarCell = .Find(What:=currentCell, _
After:=Cells(1, 46), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Set tarCell = .Find(What:=currentCell, _
LookIn:=xlValues)
If Not tarCell Is Nothing Then
firstAddress = c.Address
tarCell.Offset(1, 0).EntireRow.Delete
' Loop until every copy of the row is deleted
Do
Set tarCell = .FindNext(tarCell)
Loop While Not tarCell Is Nothing And tarCell.Address <> firstAddress
End If
End With
Next rowIndex
' Hide the old errors sheet
ActiveWorkbook.Sheets(2).Activate ' Hide from users
ActiveWindow.SelectedSheets.Visible = False
' Delete the concatenation column from sheet 1
ActiveWorkbook.Sheets(1).Activate ' Return home
Columns("AT:AT").Select ' Delete the work column
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Application.Wait (Now + TimeValue("0:00:01")) ' Show completionAs the code sits, I am returning no errors, but also am finding "nothing". I can eyeball a watch and the spredsheet and verify that the data is there and matches. When I use more detailed "Find" instructions, it dies on the "Find".
