Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric

I replaced form6 with 4059 and 4069 (they are the two mix types I have for
results at this time). Neather one worked. I got no errors. The macro went
through its passes but didn't copy the last four test results.

"JLGWhiz" wrote:

Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric

Form6 appears in two places in the code I posted. I assume you replaced both
with the same mix type numbe.

Let's do a quality check here. This is the illustration you posted:

a b c d
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

It shows the mix type in column A and this is what the code is based on. Is
the illustration correct? If not then the code is searching the wrong column
for your mix type numbers. After looking at your ListBox click event code,
it appears that the data actually should look like this:

a b c d e
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

with the mix type numbers in column B. In that case, change this line of
code:

If .Cells(i, 1) = Form6 Then

To:

If .Cells(i, 2) = Form6 Then


If that fixes the problem of not finding anything, then we can work on
getting the Form6 variable established.

"Eric" wrote:

I replaced form6 with 4059 and 4069 (they are the two mix types I have for
results at this time). Neather one worked. I got no errors. The macro went
through its passes but didn't copy the last four test results.

"JLGWhiz" wrote:

Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric

Good news....It copied!!! But,
1) It copied all the formating and everything I only need it to copy the
values because each cell in rows 9 through 12 have conditional formats.

2) When there is only one test (row 9 filled), rows 10 through 12 still had
the old (previous) search results filling the cells.

3) Mix number is in column "B". I thought I had told you that but I guess
not. I just tried to fix it instead of bothering you some more. Sorry.....

Now I guess we can work on the Form6 variable. At the risk of being
redundent, Form6 is a drop down box that has a nodupe macro in it. When I
start sub Last_four() macro, the nodupes starts. I then click what I want
for a mix type, and then it send me to form6 which is the copy and paste
macro.

Now

"JLGWhiz" wrote:

Form6 appears in two places in the code I posted. I assume you replaced both
with the same mix type numbe.

Let's do a quality check here. This is the illustration you posted:

a b c d
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

It shows the mix type in column A and this is what the code is based on. Is
the illustration correct? If not then the code is searching the wrong column
for your mix type numbers. After looking at your ListBox click event code,
it appears that the data actually should look like this:

a b c d e
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

with the mix type numbers in column B. In that case, change this line of
code:

If .Cells(i, 1) = Form6 Then

To:

If .Cells(i, 2) = Form6 Then


If that fixes the problem of not finding anything, then we can work on
getting the Form6 variable established.

"Eric" wrote:

I replaced form6 with 4059 and 4069 (they are the two mix types I have for
results at this time). Neather one worked. I got no errors. The macro went
through its passes but didn't copy the last four test results.

"JLGWhiz" wrote:

Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric

when I put 4069 into the macro (which has 7 tests) I get an error back
"Application-defined or Object-defined error". I didn't recieve this when I
used 4059 (which has 1 test).

"Eric" wrote:

Good news....It copied!!! But,
1) It copied all the formating and everything I only need it to copy the
values because each cell in rows 9 through 12 have conditional formats.

2) When there is only one test (row 9 filled), rows 10 through 12 still had
the old (previous) search results filling the cells.

3) Mix number is in column "B". I thought I had told you that but I guess
not. I just tried to fix it instead of bothering you some more. Sorry.....

Now I guess we can work on the Form6 variable. At the risk of being
redundent, Form6 is a drop down box that has a nodupe macro in it. When I
start sub Last_four() macro, the nodupes starts. I then click what I want
for a mix type, and then it send me to form6 which is the copy and paste
macro.

Now

"JLGWhiz" wrote:

Form6 appears in two places in the code I posted. I assume you replaced both
with the same mix type numbe.

Let's do a quality check here. This is the illustration you posted:

a b c d
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

It shows the mix type in column A and this is what the code is based on. Is
the illustration correct? If not then the code is searching the wrong column
for your mix type numbers. After looking at your ListBox click event code,
it appears that the data actually should look like this:

a b c d e
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

with the mix type numbers in column B. In that case, change this line of
code:

If .Cells(i, 1) = Form6 Then

To:

If .Cells(i, 2) = Form6 Then


If that fixes the problem of not finding anything, then we can work on
getting the Form6 variable established.

"Eric" wrote:

I replaced form6 with 4059 and 4069 (they are the two mix types I have for
results at this time). Neather one worked. I got no errors. The macro went
through its passes but didn't copy the last four test results.

"JLGWhiz" wrote:

Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric

I am looking at this:

when I put 4069 into the macro (which has 7 tests) I get an error back
"Application-defined or Object-defined error".

In the mean time:
I modified your ListBox1_Click codeThese changes should work.

If you get any errors, note what they are and what line they
occur on and post back.

This replaces your current ListBox_Click code and should fix
all the things you noted.

Sub ListBox1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim lr4, lc4, mCnt, cnt As Long
lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row
lc4 = Sheets("last four").UsedRange.Columns.count + 1
Set ws = Sheets("Test Database")
Set rng = ws.Range("B26:AD2500")
Sheets("test Database").Range("A25").Value = ListBox1.Value
For i = 0 To UserForm6.ListBox1.ListCount - 1
If UserForm6.ListBox1.Selected(i) Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
ws.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=" & ws.Range("A25").Value
ws.AutoFilter.Range.Copy
With Sheets("Last four")
..Range("B2500").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlValues, operation:=xlNone, _
skipblanks:=False, Transpose:=False
ws.AutoFilterMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Next
myVar4 = UserForm6.ListBox1.Selected(i)
mCnt = Application.CountIf(.Range("B72:B" & lr4), myVar4)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 72 Step -1
If .Cells(i, 2) = myVar4 Then
If cnt <= 4 Then
Range("A9:Z12").ClearContents
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B9").PasteSpecial Paste:=xlPasteValues
Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B&x").PasteSpecial Paste:=xlPasteValues
x = x - 1
Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B&x").PasteSpecial Paste:=xlPasteValues
x = x - 1
Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B&x").PasteSpecial Paste:=xlPasteValues
x = x - 1
End Select
cnt = cnt + 1
End If
End If
Next
End With
Range("S14").Select
End Sub



"Eric" wrote:

when I put 4069 into the macro (which has 7 tests) I get an error back
"Application-defined or Object-defined error". I didn't recieve this when I
used 4059 (which has 1 test).

"Eric" wrote:

Good news....It copied!!! But,
1) It copied all the formating and everything I only need it to copy the
values because each cell in rows 9 through 12 have conditional formats.

2) When there is only one test (row 9 filled), rows 10 through 12 still had
the old (previous) search results filling the cells.

3) Mix number is in column "B". I thought I had told you that but I guess
not. I just tried to fix it instead of bothering you some more. Sorry.....

Now I guess we can work on the Form6 variable. At the risk of being
redundent, Form6 is a drop down box that has a nodupe macro in it. When I
start sub Last_four() macro, the nodupes starts. I then click what I want
for a mix type, and then it send me to form6 which is the copy and paste
macro.

Now

"JLGWhiz" wrote:

Form6 appears in two places in the code I posted. I assume you replaced both
with the same mix type numbe.

Let's do a quality check here. This is the illustration you posted:

a b c d
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

It shows the mix type in column A and this is what the code is based on. Is
the illustration correct? If not then the code is searching the wrong column
for your mix type numbers. After looking at your ListBox click event code,
it appears that the data actually should look like this:

a b c d e
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

with the mix type numbers in column B. In that case, change this line of
code:

If .Cells(i, 1) = Form6 Then

To:

If .Cells(i, 2) = Form6 Then


If that fixes the problem of not finding anything, then we can work on
getting the Form6 variable established.

"Eric" wrote:

I replaced form6 with 4059 and 4069 (they are the two mix types I have for
results at this time). Neather one worked. I got no errors. The macro went
through its passes but didn't copy the last four test results.

"JLGWhiz" wrote:

Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric

I finished typing everything....I am getting an error *End If without block
if*< . It is the first end if. I double checked and I do have the If
statement in the macro.

I hope you get paid enough to put with people like me.......Do you have a
boss that I can sing your praises too?


"JLGWhiz" wrote:

I am looking at this:

when I put 4069 into the macro (which has 7 tests) I get an error back
"Application-defined or Object-defined error".

In the mean time:
I modified your ListBox1_Click codeThese changes should work.

If you get any errors, note what they are and what line they
occur on and post back.

This replaces your current ListBox_Click code and should fix
all the things you noted.

Sub ListBox1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim lr4, lc4, mCnt, cnt As Long
lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row
lc4 = Sheets("last four").UsedRange.Columns.count + 1
Set ws = Sheets("Test Database")
Set rng = ws.Range("B26:AD2500")
Sheets("test Database").Range("A25").Value = ListBox1.Value
For i = 0 To UserForm6.ListBox1.ListCount - 1
If UserForm6.ListBox1.Selected(i) Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
ws.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=" & ws.Range("A25").Value
ws.AutoFilter.Range.Copy
With Sheets("Last four")
.Range("B2500").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlValues, operation:=xlNone, _
skipblanks:=False, Transpose:=False
ws.AutoFilterMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Next
myVar4 = UserForm6.ListBox1.Selected(i)
mCnt = Application.CountIf(.Range("B72:B" & lr4), myVar4)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 72 Step -1
If .Cells(i, 2) = myVar4 Then
If cnt <= 4 Then
Range("A9:Z12").ClearContents
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B9").PasteSpecial Paste:=xlPasteValues
Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B&x").PasteSpecial Paste:=xlPasteValues
x = x - 1
Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B&x").PasteSpecial Paste:=xlPasteValues
x = x - 1
Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B&x").PasteSpecial Paste:=xlPasteValues
x = x - 1
End Select
cnt = cnt + 1
End If
End If
Next
End With
Range("S14").Select
End Sub



"Eric" wrote:

when I put 4069 into the macro (which has 7 tests) I get an error back
"Application-defined or Object-defined error". I didn't recieve this when I
used 4059 (which has 1 test).

"Eric" wrote:

Good news....It copied!!! But,
1) It copied all the formating and everything I only need it to copy the
values because each cell in rows 9 through 12 have conditional formats.

2) When there is only one test (row 9 filled), rows 10 through 12 still had
the old (previous) search results filling the cells.

3) Mix number is in column "B". I thought I had told you that but I guess
not. I just tried to fix it instead of bothering you some more. Sorry.....

Now I guess we can work on the Form6 variable. At the risk of being
redundent, Form6 is a drop down box that has a nodupe macro in it. When I
start sub Last_four() macro, the nodupes starts. I then click what I want
for a mix type, and then it send me to form6 which is the copy and paste
macro.

Now

"JLGWhiz" wrote:

Form6 appears in two places in the code I posted. I assume you replaced both
with the same mix type numbe.

Let's do a quality check here. This is the illustration you posted:

a b c d
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

It shows the mix type in column A and this is what the code is based on. Is
the illustration correct? If not then the code is searching the wrong column
for your mix type numbers. After looking at your ListBox click event code,
it appears that the data actually should look like this:

a b c d e
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

with the mix type numbers in column B. In that case, change this line of
code:

If .Cells(i, 1) = Form6 Then

To:

If .Cells(i, 2) = Form6 Then


If that fixes the problem of not finding anything, then we can work on
getting the Form6 variable established.

"Eric" wrote:

I replaced form6 with 4059 and 4069 (they are the two mix types I have for
results at this time). Neather one worked. I got no errors. The macro went
through its passes but didn't copy the last four test results.

"JLGWhiz" wrote:

Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric

Don't use the modified version yet. It has bugs.

"Eric" wrote:

when I put 4069 into the macro (which has 7 tests) I get an error back
"Application-defined or Object-defined error". I didn't recieve this when I
used 4059 (which has 1 test).

"Eric" wrote:

Good news....It copied!!! But,
1) It copied all the formating and everything I only need it to copy the
values because each cell in rows 9 through 12 have conditional formats.

2) When there is only one test (row 9 filled), rows 10 through 12 still had
the old (previous) search results filling the cells.

3) Mix number is in column "B". I thought I had told you that but I guess
not. I just tried to fix it instead of bothering you some more. Sorry.....

Now I guess we can work on the Form6 variable. At the risk of being
redundent, Form6 is a drop down box that has a nodupe macro in it. When I
start sub Last_four() macro, the nodupes starts. I then click what I want
for a mix type, and then it send me to form6 which is the copy and paste
macro.

Now

"JLGWhiz" wrote:

Form6 appears in two places in the code I posted. I assume you replaced both
with the same mix type numbe.

Let's do a quality check here. This is the illustration you posted:

a b c d
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

It shows the mix type in column A and this is what the code is based on. Is
the illustration correct? If not then the code is searching the wrong column
for your mix type numbers. After looking at your ListBox click event code,
it appears that the data actually should look like this:

a b c d e
71 4056 1/3/08 5 7
72 4056 1/3/08 5 8
73 4058 1/5/08 3 4
74 4056 1/6/08 2 6
75 4058 1/7/08 3 5
76 4056 1/9/08 6 7
77 4056 1/9/08 7 9

with the mix type numbers in column B. In that case, change this line of
code:

If .Cells(i, 1) = Form6 Then

To:

If .Cells(i, 2) = Form6 Then


If that fixes the problem of not finding anything, then we can work on
getting the Form6 variable established.

"Eric" wrote:

I replaced form6 with 4059 and 4069 (they are the two mix types I have for
results at this time). Neather one worked. I got no errors. The macro went
through its passes but didn't copy the last four test results.

"JLGWhiz" wrote:

Try making Form6 = 4056 and see if it works.

"Eric" wrote:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long
With Worksheets("last four")
lr4 = .Cells(Rows.count, 2).End(xlUp).Row
lc4 = .UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("A71:A" & lr4), form6)
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 71 Step -1
If .Cells(i, 1) = form6 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9")

Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B&x")
x = x - 1

End Select
cnt = cnt + 1
End If
End If
Next
End With
End Sub

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric