ojackiec
12-21-2005, 07:35 AM
I'm having a problem with a piece of my code. It is supposed to find the records in the Master Equipment List that have an M in column G. Column G only contains one of three letters: M, Q, or A. The code generates a spreadsheet called the Monthly Inspection Log and places certain columns from the row containing an M into the sheet. For background, my macro is contained in one location. It is opened and then another file is opened that contains the Master Equipment List. The macro places a new menu on the bar that allows the user to create different reports.
The code completes itself but doesn't display any records. I know that they exist in my sample file I'm using to debug my code. I get the message box at the end asking if I want to print but it hasn't found any records. Any ideas would be VERY welcome!! I have only been coding in VBA for about 3 weeks and I know my code is probably sloppy so bear with me. I'm also not a programmer but an environmental specialist! Thanks again for any help!!
Here's where I define:
Sub Monthly()
Dim ws As Worksheet
Dim FromSheet As Worksheet, ToSheet As Worksheet
Dim FromRow As Long, ToRow As Long
Dim FindThis As Variant
Dim rng As Range, FirstAddress As String, FoundCell As Object
Dim obj As Object, cellsDone$
Dim result As Variant
And here's where I'm having problems:
Application.Calculation = xlCalculationManual
Set FromSheet = ActiveWorkbook.Worksheets("Master Equipment List")
Set ToSheet = ActiveWorkbook.Worksheets("Monthly Inspection Log")
ToRow = 2
FindThis = "M"
With FromSheet.Cells
With FromSheet
Set rng = Range("G2", Range("G5000").End(xlUp))
End With
Set FoundCell = rng.Find(FindThis, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
FromRow = FoundCell.Row
Do
ToSheet.Cells(ToRow, 1).Value = .Cells(FromRow, 1).Value
ToSheet.Cells(ToRow, 2).Value = .Cells(FromRow, 2).Value
ToSheet.Cells(ToRow, 3).Value = .Cells(FromRow, 3).Value
ToSheet.Cells(ToRow, 4).Value = .Cells(FromRow, 4).Value
ToSheet.Cells(ToRow, 5).Value = .Cells(FromRow, 5).Value
ToSheet.Cells(ToRow, 6).Value = .Cells(FromRow, 11).Value
With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ToRow = ToRow + 1
Set FoundCell = rng.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And _
FoundCell.Address <> FirstAddress
End If
End With
result = MsgBox("Print Monthly Inspection Log?", vbYesNo)
If result = vbYes Then ws.PrintOut
With ws
.Name = company.ReportingMonth.Value & "Inspection Log"
End With
End Sub
Thanks again!!
The code completes itself but doesn't display any records. I know that they exist in my sample file I'm using to debug my code. I get the message box at the end asking if I want to print but it hasn't found any records. Any ideas would be VERY welcome!! I have only been coding in VBA for about 3 weeks and I know my code is probably sloppy so bear with me. I'm also not a programmer but an environmental specialist! Thanks again for any help!!
Here's where I define:
Sub Monthly()
Dim ws As Worksheet
Dim FromSheet As Worksheet, ToSheet As Worksheet
Dim FromRow As Long, ToRow As Long
Dim FindThis As Variant
Dim rng As Range, FirstAddress As String, FoundCell As Object
Dim obj As Object, cellsDone$
Dim result As Variant
And here's where I'm having problems:
Application.Calculation = xlCalculationManual
Set FromSheet = ActiveWorkbook.Worksheets("Master Equipment List")
Set ToSheet = ActiveWorkbook.Worksheets("Monthly Inspection Log")
ToRow = 2
FindThis = "M"
With FromSheet.Cells
With FromSheet
Set rng = Range("G2", Range("G5000").End(xlUp))
End With
Set FoundCell = rng.Find(FindThis, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
FromRow = FoundCell.Row
Do
ToSheet.Cells(ToRow, 1).Value = .Cells(FromRow, 1).Value
ToSheet.Cells(ToRow, 2).Value = .Cells(FromRow, 2).Value
ToSheet.Cells(ToRow, 3).Value = .Cells(FromRow, 3).Value
ToSheet.Cells(ToRow, 4).Value = .Cells(FromRow, 4).Value
ToSheet.Cells(ToRow, 5).Value = .Cells(FromRow, 5).Value
ToSheet.Cells(ToRow, 6).Value = .Cells(FromRow, 11).Value
With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ToRow = ToRow + 1
Set FoundCell = rng.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And _
FoundCell.Address <> FirstAddress
End If
End With
result = MsgBox("Print Monthly Inspection Log?", vbYesNo)
If result = vbYes Then ws.PrintOut
With ws
.Name = company.ReportingMonth.Value & "Inspection Log"
End With
End Sub
Thanks again!!