Hi .
I have the following on a spreadsheet and I need help printing with Vba:
there are 3 ) a,b,c)columns and starting in a1.
daily Total $25,000.00 n
doe Total $225.00 y
smith Total $240.00 n
day Total $50.00 y
green Total $70.00 n
I would like to print so the Y' s are first and n's are second so it looks like this:
doe Total $225.00 y
day Total $50.00 y
smith Total $240.00 n
green Total $70.00 n
daily Total $25,000.00 n
Can someone please help....
Thx in advance
Hello Jonnj,
Here is a macro to group the Yes and No data. This macro assumes Y or N is in the 3rd column (As I understand your post). Also, it is currently set to handle up to 1000 entries. You can easily change this by changing the first dimension of the YesNo(1000, 2, 1) Array in Dim statements of the code.
Copy and paste the code into a Standard Module. When you call the macro, you only need to give it the leftmost Column and Row numbers. It gets the data from the other columns by using offset and calculates the end of the range automatically. Using your example code which starts in "A1"
You would Call the Macro this way: GroupYesNo ("A1")
_________________________________________________________________
Public Sub GroupYesNo(ByVal Start_Cell As String)
Dim Test As Range
Dim YesNo(1000, 2, 1) As Variant
Dim I As Long
Dim J As Long
Dim N As Long
Dim Y As Long
Dim StartRow As Long
Dim LastRow As Long
Dim StartCol As Long
With ActiveSheet.Range(Start_Cell)
StartRow = .Row
StartCol = .Column
End With
LastRow = ActiveSheet.Cells(Rows.Count, StartCol).End(xlUp).Row
For I = StartRow To LastRow
Set Test = ActiveSheet.Cells(I, StartCol)
If Test.Offset(0, 2).Value = "y" Then
Y = Y + 1
YesNo(Y, 0, 0) = Test.Value
YesNo(Y, 1, 0) = Test.Offset(0, 1).Value
YesNo(Y, 2, 0) = Test.Offset(0, 2).Value
Else
N = N + 1
YesNo(N, 0, 1) = Test.Value
YesNo(N, 1, 1) = Test.Offset(0, 1).Value
YesNo(N, 2, 1) = Test.Offset(0, 2).Value
End If
Next I
'Put Yes's onto Worksheet
For I = 1 To Y
With ActiveSheet.Cells(StartRow + I - 1, StartCol)
.Value = YesNo(I, 0, 0)
.Offset(0, 1).Value = YesNo(I, 1, 0)
.Offset(0, 2).Value = YesNo(I, 2, 0)
End With
Next I
'Put No's onto Worksheet
For J = 1 To N
With ActiveSheet.Cells(StartRow + Y + J - 1, StartCol)
.Value = YesNo(J, 0, 1)
.Offset(0, 1).Value = YesNo(J, 1, 1)
.Offset(0, 2).Value = YesNo(J, 2, 1)
End With
Next J
End Sub
_________________________________________________________________
Hope this is what you wanted,
Leith Ross
Last edited by Leith Ross; 03-19-2005 at 10:00 AM.
Hi Leith,
Let me first say, Thanks for your time and assistance.
When I try to run the macro I get an err msg " Reference Not valid" and it won;t let me do anything else with it, not even edit.
Also, the problem is I want to print this out with the Y's rows first and the N's rows second . Do I put the ActiveWorksheet.printout before the end sub?
Again thanks for your help
Jon
Hi Jonnj,
Can you tell which statement caused the error? As for the printing question, I am not sure I know what you want to do. Could you explain it to me in more detail. I ran the macro on my machine to make sure it was OK. What version of Windows and Office do you have?
Thanks,
Leith Ross
Leith,
I have Windows XP home and XP office.
I have a userform to enter data into cells. The person will Determine if the payment is a Y or N vaild payment, It could be from 1 to 1000 entries. What I want to do is....to print out that page or pages and have the Y's rows show first and the N's rows show second.
I know I could run a macro to sort descending but I wanted to have it bulit into the print code to save the user a step in sorting.
When I try to run the macro it gives me that error and will not allow me to edit the module. It wont do anything at all, just repeats the err msg.
Thanks
Jon
Hi Jon,
Can you step through the code using the VBA editor to isolate the problem. The code is written in Office '97 VBA version 5.0, and should not be a problem. As for the printing the easiest approach would be to set the print area for the Yes group and print it. Then set the print area for the No group. Everything stays on one worksheet, but gets split into 2 printed pages.
Leith
Leith,
The Vba editor also will not let me step thru. The cursor stays at the top of the page no matter how many times I click on step thru. THis is baffling.
Jon
Hello Jon,
Have you tried lowering the macro security level? I really can think of anything else that would stop the execution. Excel '97 should be compatabile with XP (so says Microsoft). If all else fails, clear everything out and reload the code. This is very strange.
Keep me posted,
Leith Ross
Leith,
I had my security on low....and I tried to repaste the code..I still got the same msg" Reference is not vaild" ..I dont know if its something with OfficeXP.
Thanks for your time and effort in this matter,
If I may use your expertise once again..in a way to work around this, I have code to do sort on the spreadsheet. The sort leaves the first 3 rows with the Y' and last 2 rows with the N's. Is there short code you can show that will loop down the rows till it finds the last row with a Y in it, then insert a blank row between the last Y row and first N row.
Thanks again
Jon
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks