Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
Did the code work when you had 101? I looked at your early posting and read
the instructions for you to replace form6 with your input data from form 6. Make sure you are getting good data in variable form6. try adding a msgbox to debug the code like the one below. Your problem is probably that your aren't referrencing the input from form6 properly. Sub last_four_four() Dim lr4, lc4, mCnt, cnt As Long msgbox("form6 = " & form6) 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 "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
joel, Form 6 is a form for no dupes. When he says to put a variable in
place of form 6 what does that mean? "Joel" wrote: Did the code work when you had 101? I looked at your early posting and read the instructions for you to replace form6 with your input data from form 6. Make sure you are getting good data in variable form6. try adding a msgbox to debug the code like the one below. Your problem is probably that your aren't referrencing the input from form6 properly. Sub last_four_four() Dim lr4, lc4, mCnt, cnt As Long msgbox("form6 = " & form6) 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 "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
The variable has to equate to a value in column A rows 71 To the last row for
your type mix. You previously stated that you got this value from Form 6 so I stuck that in as a place holder. If your Form six is a TextBox then the variable would be equal to the TextBox value. You didn't explatin what the Form six was so I assumed it was a control which produced a value equal to a mix type. You have to have something in the places where Form6 appears in the code that identifies the mix type you want to select the test results for or it will not select anything. "Eric" wrote: joel, Form 6 is a form for no dupes. When he says to put a variable in place of form 6 what does that mean? "Joel" wrote: Did the code work when you had 101? I looked at your early posting and read the instructions for you to replace form6 with your input data from form 6. Make sure you are getting good data in variable form6. try adding a msgbox to debug the code like the one below. Your problem is probably that your aren't referrencing the input from form6 properly. Sub last_four_four() Dim lr4, lc4, mCnt, cnt As Long msgbox("form6 = " & form6) 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 "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
I can't believe how hard I am making this......I must admitt I'm lost, way
over my head. I have pasted the whole macro into this post so hopefully you can tell me where I am going wrong. Sub ListBox1_Click() Sheets("test Database").Range("A25").Value = ListBox1 For i = 0 To UserForm6.ListBox1.ListCount - 1 If UserForm6.ListBox1.Selected(i) Then Dim ws As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set ws = Sheets("Test Database") Set rng = ws.Range("B26:AD2500") ws.AutoFilterMode = False rng.AutoFilter Field:=1, Criteria1:="=" & ws.Range("A25").Value ws.AutoFilter.Range.Copy Sheets("Last four").Select Range("B2500").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False ws.AutoFilterMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End If Next 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("B72:B" & lr4), form6)~~~I don't understand If mCnt = 4 Then how form6 will mCnt = 4 will put in auto- End If maticly the Mix type cnt = 1 I pick from the For i = lr4 To 72 Step -1 listbox. 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 Range("S14").Select End Sub This list box will list all the mix types and I can pick from. This is then copied over to the bottom of sheets("last four"). Then just the last four tests are copied up to rows 9-12 on sheets("last four"). "JLGWhiz" wrote: The variable has to equate to a value in column A rows 71 To the last row for your type mix. You previously stated that you got this value from Form 6 so I stuck that in as a place holder. If your Form six is a TextBox then the variable would be equal to the TextBox value. You didn't explatin what the Form six was so I assumed it was a control which produced a value equal to a mix type. You have to have something in the places where Form6 appears in the code that identifies the mix type you want to select the test results for or it will not select anything. "Eric" wrote: joel, Form 6 is a form for no dupes. When he says to put a variable in place of form 6 what does that mean? "Joel" wrote: Did the code work when you had 101? I looked at your early posting and read the instructions for you to replace form6 with your input data from form 6. Make sure you are getting good data in variable form6. try adding a msgbox to debug the code like the one below. Your problem is probably that your aren't referrencing the input from form6 properly. Sub last_four_four() Dim lr4, lc4, mCnt, cnt As Long msgbox("form6 = " & form6) 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 "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
That problem was a With Sheets('last four") That I had put in. I am having
other problems. I can't get it to copy the autofilter data. I'll get back to you later tonight or tomorrow when I work this out. "Eric" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |