Macro not repeating output from search function
 

This macro is supposed to display the output from a spreadsheet, including
multiple items per day. Right now, it's displaying the first item and then
quitting. It used to function properly, so can you help me figure out where
the glitch is at?


' Based on the input date, this macro will pull all of the work centers,
part numbers, and the quantities due that day.
'
' Keyboard Shortcut: Ctrl+s
'
Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long
Dim rng As Range
Dim SearchDate As Date
Sheets("Output").Select
On Error Resume Next
SearchDate = InputBox("Enter the search date, 'm/d' (month/day),
example: 1/1 12/31 ")
If SearchDate = 0 Then
Application.ScreenUpdating = True
MsgBox "You clicked on 'Cancel', or you did not enter a date - macro
terminated."
Exit Sub
End If
Application.ScreenUpdating = False
LR3 = Sheets("Schedule").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("Output")
LR = .Cells(Rows.Count, 3).End(xlUp).Row
LC = .Cells(1, Columns.Count).End(xlToLeft).Column - 1
Set rng = .Range("C1:C" & LR)
MyLR = Application.WorksheetFunction.Match(0, rng, 0) - 1
For Ctr = 2 To MyLR Step 3
For Ctr2 = 4 To LC Step 1
On Error Resume Next
If .Cells(Ctr, Ctr2) = "#N/A" Then
'Do nothing
ElseIf .Cells(Ctr, Ctr2) = SearchDate Then
Sheets("Schedule").Cells(LR3, 1) = .Cells(Ctr - 1,
Ctr2).Value
Sheets("Schedule").Cells(LR3, 2) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 3) = .Cells(Ctr - 1,
4).Value
Sheets("Schedule").Cells(LR3, 4) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 5) = .Cells(Ctr, 4).Value
Sheets("Schedule").Cells(1, 5) = SearchDate
LR3 = LR3 + 1
End If
Next Ctr2
Next Ctr
End With
Sheets("Schedule").Select
End Sub

Macro not repeating output from search function
 

First of all, your Dim statement "Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long"
only sets LC As Long, the rest will be variants. You must use the As
operator with each variable. They cannot be grouped.

As for the main problem, what did you change? If your worksheet layout was
changed, it would affect your step 3 sequence. Looking at the code, I can't
see any furry hands sticking out, that would keep it from looping and
getting the next occurrence of the search date. Maybe there is only one
occurrence. Best suggestion I can make is to execute the procedure one step
at the time, or maybe use a break point at the two Next lines and just use
the tool tips to check all variable values to make sure they are what you
expect them to be.




"PointerMan" wrote in message
...
This macro is supposed to display the output from a spreadsheet, including
multiple items per day. Right now, it's displaying the first item and
then
quitting. It used to function properly, so can you help me figure out
where
the glitch is at?


' Based on the input date, this macro will pull all of the work centers,
part numbers, and the quantities due that day.
'
' Keyboard Shortcut: Ctrl+s
'
Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long
Dim rng As Range
Dim SearchDate As Date
Sheets("Output").Select
On Error Resume Next
SearchDate = InputBox("Enter the search date, 'm/d' (month/day),
example: 1/1 12/31 ")
If SearchDate = 0 Then
Application.ScreenUpdating = True
MsgBox "You clicked on 'Cancel', or you did not enter a date -
macro
terminated."
Exit Sub
End If
Application.ScreenUpdating = False
LR3 = Sheets("Schedule").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("Output")
LR = .Cells(Rows.Count, 3).End(xlUp).Row
LC = .Cells(1, Columns.Count).End(xlToLeft).Column - 1
Set rng = .Range("C1:C" & LR)
MyLR = Application.WorksheetFunction.Match(0, rng, 0) - 1
For Ctr = 2 To MyLR Step 3
For Ctr2 = 4 To LC Step 1
On Error Resume Next
If .Cells(Ctr, Ctr2) = "#N/A" Then
'Do nothing
ElseIf .Cells(Ctr, Ctr2) = SearchDate Then
Sheets("Schedule").Cells(LR3, 1) = .Cells(Ctr - 1,
Ctr2).Value
Sheets("Schedule").Cells(LR3, 2) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 3) = .Cells(Ctr - 1,
4).Value
Sheets("Schedule").Cells(LR3, 4) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 5) = .Cells(Ctr, 4).Value
Sheets("Schedule").Cells(1, 5) = SearchDate
LR3 = LR3 + 1
End If
Next Ctr2
Next Ctr
End With
Sheets("Schedule").Select
End Sub

Macro not repeating output from search function
 

Need to clarify the Dim thing. You must delclare the variable using the
variable name, the As operator and the data type, for each variable.


"PointerMan" wrote in message
...
This macro is supposed to display the output from a spreadsheet, including
multiple items per day. Right now, it's displaying the first item and
then
quitting. It used to function properly, so can you help me figure out
where
the glitch is at?


' Based on the input date, this macro will pull all of the work centers,
part numbers, and the quantities due that day.
'
' Keyboard Shortcut: Ctrl+s
'
Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long
Dim rng As Range
Dim SearchDate As Date
Sheets("Output").Select
On Error Resume Next
SearchDate = InputBox("Enter the search date, 'm/d' (month/day),
example: 1/1 12/31 ")
If SearchDate = 0 Then
Application.ScreenUpdating = True
MsgBox "You clicked on 'Cancel', or you did not enter a date -
macro
terminated."
Exit Sub
End If
Application.ScreenUpdating = False
LR3 = Sheets("Schedule").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("Output")
LR = .Cells(Rows.Count, 3).End(xlUp).Row
LC = .Cells(1, Columns.Count).End(xlToLeft).Column - 1
Set rng = .Range("C1:C" & LR)
MyLR = Application.WorksheetFunction.Match(0, rng, 0) - 1
For Ctr = 2 To MyLR Step 3
For Ctr2 = 4 To LC Step 1
On Error Resume Next
If .Cells(Ctr, Ctr2) = "#N/A" Then
'Do nothing
ElseIf .Cells(Ctr, Ctr2) = SearchDate Then
Sheets("Schedule").Cells(LR3, 1) = .Cells(Ctr - 1,
Ctr2).Value
Sheets("Schedule").Cells(LR3, 2) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 3) = .Cells(Ctr - 1,
4).Value
Sheets("Schedule").Cells(LR3, 4) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 5) = .Cells(Ctr, 4).Value
Sheets("Schedule").Cells(1, 5) = SearchDate
LR3 = LR3 + 1
End If
Next Ctr2
Next Ctr
End With
Sheets("Schedule").Select
End Sub

Macro not repeating output from search function
 

Thanks for the feedback! I did add a column to the beginning of the sheet,
so that might be part of the problem. I don't know very much about macros,
so your explanations are much appreciated.



"JLGWhiz" wrote:

First of all, your Dim statement "Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long"
only sets LC As Long, the rest will be variants. You must use the As
operator with each variable. They cannot be grouped.

As for the main problem, what did you change? If your worksheet layout was
changed, it would affect your step 3 sequence. Looking at the code, I can't
see any furry hands sticking out, that would keep it from looping and
getting the next occurrence of the search date. Maybe there is only one
occurrence. Best suggestion I can make is to execute the procedure one step
at the time, or maybe use a break point at the two Next lines and just use
the tool tips to check all variable values to make sure they are what you
expect them to be.




"PointerMan" wrote in message
...
This macro is supposed to display the output from a spreadsheet, including
multiple items per day. Right now, it's displaying the first item and
then
quitting. It used to function properly, so can you help me figure out
where
the glitch is at?


' Based on the input date, this macro will pull all of the work centers,
part numbers, and the quantities due that day.
'
' Keyboard Shortcut: Ctrl+s
'
Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long
Dim rng As Range
Dim SearchDate As Date
Sheets("Output").Select
On Error Resume Next
SearchDate = InputBox("Enter the search date, 'm/d' (month/day),
example: 1/1 12/31 ")
If SearchDate = 0 Then
Application.ScreenUpdating = True
MsgBox "You clicked on 'Cancel', or you did not enter a date -
macro
terminated."
Exit Sub
End If
Application.ScreenUpdating = False
LR3 = Sheets("Schedule").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("Output")
LR = .Cells(Rows.Count, 3).End(xlUp).Row
LC = .Cells(1, Columns.Count).End(xlToLeft).Column - 1
Set rng = .Range("C1:C" & LR)
MyLR = Application.WorksheetFunction.Match(0, rng, 0) - 1
For Ctr = 2 To MyLR Step 3
For Ctr2 = 4 To LC Step 1
On Error Resume Next
If .Cells(Ctr, Ctr2) = "#N/A" Then
'Do nothing
ElseIf .Cells(Ctr, Ctr2) = SearchDate Then
Sheets("Schedule").Cells(LR3, 1) = .Cells(Ctr - 1,
Ctr2).Value
Sheets("Schedule").Cells(LR3, 2) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 3) = .Cells(Ctr - 1,
4).Value
Sheets("Schedule").Cells(LR3, 4) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 5) = .Cells(Ctr, 4).Value
Sheets("Schedule").Cells(1, 5) = SearchDate
LR3 = LR3 + 1
End If
Next Ctr2
Next Ctr
End With
Sheets("Schedule").Select
End Sub

Macro not repeating output from search function
 

Since I didn't modify any of those elements, will I still need to change
them? My only change to the spreadsheet was to insert a column at the
beginning of the spreadsheet.


"JLGWhiz" wrote:

Need to clarify the Dim thing. You must delclare the variable using the
variable name, the As operator and the data type, for each variable.


"PointerMan" wrote in message
...
This macro is supposed to display the output from a spreadsheet, including
multiple items per day. Right now, it's displaying the first item and
then
quitting. It used to function properly, so can you help me figure out
where
the glitch is at?


' Based on the input date, this macro will pull all of the work centers,
part numbers, and the quantities due that day.
'
' Keyboard Shortcut: Ctrl+s
'
Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long
Dim rng As Range
Dim SearchDate As Date
Sheets("Output").Select
On Error Resume Next
SearchDate = InputBox("Enter the search date, 'm/d' (month/day),
example: 1/1 12/31 ")
If SearchDate = 0 Then
Application.ScreenUpdating = True
MsgBox "You clicked on 'Cancel', or you did not enter a date -
macro
terminated."
Exit Sub
End If
Application.ScreenUpdating = False
LR3 = Sheets("Schedule").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("Output")
LR = .Cells(Rows.Count, 3).End(xlUp).Row
LC = .Cells(1, Columns.Count).End(xlToLeft).Column - 1
Set rng = .Range("C1:C" & LR)
MyLR = Application.WorksheetFunction.Match(0, rng, 0) - 1
For Ctr = 2 To MyLR Step 3
For Ctr2 = 4 To LC Step 1
On Error Resume Next
If .Cells(Ctr, Ctr2) = "#N/A" Then
'Do nothing
ElseIf .Cells(Ctr, Ctr2) = SearchDate Then
Sheets("Schedule").Cells(LR3, 1) = .Cells(Ctr - 1,
Ctr2).Value
Sheets("Schedule").Cells(LR3, 2) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 3) = .Cells(Ctr - 1,
4).Value
Sheets("Schedule").Cells(LR3, 4) = .Cells(Ctr + 1,
3).Value
Sheets("Schedule").Cells(LR3, 5) = .Cells(Ctr, 4).Value
Sheets("Schedule").Cells(1, 5) = SearchDate
LR3 = LR3 + 1
End If
Next Ctr2
Next Ctr
End With
Sheets("Schedule").Select
End Sub