Programming Question

J

Jon Turner

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance
 
J

Jeff

John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
For Each c In r
If c.Offset(0, 3) = "no" Then
Lnam = c.Value
Fnam = c.Offset(0, 1).Value
addr = c.Offset(0, 2).Value
MsgBox ("Found " & Fnam & " " & Lnam & " @" & addr)
End If
Next c
End Sub

Jeff
 
B

Bob Reynolds

Jeff, Could this code be used to select and set a print area for a worksheet
where I need to copy and paste all the cells with active data in it.
I was looking for something to read various rows and when it gets to a
bottom row and the data stops, then set the print area and print the results
of that document. The document I'm using has 208 rows but very few times
will they all be filled. usually they only have about 20 or 30 rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to do?
Thanks
Bob Reynolds
 
T

Tom Ogilvy

Lastrow is the lastrow with data, so if you set your printarea to include
just down to that row it should do what you want.
 
B

Bob Reynolds

I'm not as smart as I thought. I substituted the "D" in the macro to read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to select what I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB
 
T

Tom Ogilvy

Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

-
Regards,
Tom Ogilvy
 
B

Bob Reynolds

Thanks Tom, that took care of a small part of it but it won't print more
than the top rows.
WAGE COMPUTATION SHEET Date: 05-20-04 Identification Nr: 12345
Employer: Enter Employer's Name Here Social Security Number:
CONFIDENTIAL
Employee (Name/Address): Enter employee's Name Here Occupation:
This is the Occupation Section The Total Amounts Due This
1234 Any place Telephone: (876) 543-2345 Employee is shown below:
Raleigh, NC 283565 $1,670.01 $48.85 $1,718.86
W/W END PAY DAY HOURS WORK Salary Hourly Rate Straight Time Wages
Due OT Hours OT Rate OT Wages Due Wages Paid TOTAL WAGES DUE INTEREST
(Coeff.) TOTAL AMOUNT DUE
01-01-04 55.00 $500.00 $9.09 $500.00 15.00 $4.55 $68.25 $568.25
$17.50 $585.75
0.0308
01-01-04 55.00 $100.00 $1.82 $100.00 15.00 $0.91 $13.65 $113.65
$3.50 $117.15
0.0308

This is what I'm trying to work with, but with 208 rows. I only get the top
row that is greyed out. There is an additional column of O also that
contains a vlookup formula. (All numbers are ficticous)

I can provide the worksheet if necessary.
Thanks
BOB
 
B

Bob Reynolds

It appears my paste didn't stay formatted but can provide the ws if need to
be attached.
BOB
 
T

Tom Ogilvy

This isn't a binaries news group, so attaching the file would be a
non-starter.

If the last row to be printed has a value in column A, then you should not
have any trouble.

You can send me a sample workbook if you wish and I will take a look, but I
can't tell anything from your email.

[email protected]
 
B

Bob Reynolds

Tom Thanks for your help.
I have one other worksheet similiar to this one, but all the cells are
filled in by being linked to another data entry worksheet, so each cell
contains a formula. It appears this see a formula as a not empty cell and
would continue to show the row as being completed.

Is there a way to make this formula distinguish from a formula returning a
blank or empty cell (the formula is still in it) and the result of the
formula?
Hope you have a positive answer for this one...
Bob
 
T

Tom Ogilvy

Sub findlastrow()
Dim lastrow As Long
Dim r As Range
LastRow = 1
for each cell in Range(Cells(1,"C"),Cells(rows.count,"C").End(xlup))
if trim(cell.text) <> "" then
lastrow = cell.row
end if
Next
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

Change "C" to the column you want to use to determine the last row.
 
B

Bob Reynolds

Thanks so much, worked like a champ. I assume the LastRow=1 tells it that if
it doesn't include a product then don't read it and skip it, is that
correct?

I greatly appreciate the assistance.
BOB
 
Top