How to allow "if then" text?

[Kenshe]

It does look to be difficult this way.
I know there must be a simplier way to do this, I am not sure though
how to do this.

The date is manually entered in each column and is always entered in
Row 4 regardless of column, using a cell comment instead of tool bar
comments, those will always be in Row 29 regardless of column also.
Here's how it's designed:

By using a button to create a new sheet.
Go out and search for data in row 29 on a specified date in all sheets
(29 of them).
Generate report in new sheet created.
Reporting: Date (listed in Row 4), Address (B4, F4..ect) and whatever
data is in Row 29 cell (comments)
Using another button to delete report sheet created when finished with
it.
(both buttons will be on another sheet labeled "Report")

So the macro need to search every sheet for a specified date, capture
any data listed in row4 and row29 and its address (B4) on the specified
date only and place all data on a new sheet. Simple??

I am learning, without the extensive knowledge it becomes difficult,
however getting as far as I have with understanding how it works
(somewhat) keep me learning!

If possible, a solution would help alot!

Thanks again!

 

[Dave Peterson]

I'm not quite sure, but maybe...

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim rptWks As Worksheet

Dim oRow As Long

Dim DateRow As Long
Dim CommentRow As Long

Dim myDate As Date

Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long

myDate = Application.InputBox(prompt:="enter date:", Type:=1)

If IsDate(myDate) Then
'keep going
Else
MsgBox "Please try again!"
Exit Sub
End If

If Year(myDate) < 2005 _
Or Year(myDate) > 2010 Then
MsgBox "Hey, that date: " & Format(myDate, "mmmm dd, yyyy") _
& " doesn't look right!"
Exit Sub
End If

DateRow = 4
CommentRow = 29
FirstCol = 1

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("report").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set rptWks = Worksheets.Add
rptWks.Name = "Report"
With rptWks.Range("a1").Resize(1, 4)
.Value = Array("Date", "Worksheet" & Chr(10) & "Name", _
"Address", "Comment")
.WrapText = True
End With

oRow = 1
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = rptWks.Name Then
'do nothing
Else
With wks
LastCol = .Cells(DateRow, .Columns.Count).End(xlToLeft).Column
For iCol = FirstCol To LastCol
If .Cells(DateRow, iCol).Value2 = CLng(myDate) Then
'found a match
oRow = oRow + 1
With rptWks.Cells(oRow, 1)
.Value = myDate
.NumberFormat = "mm/dd/yyyy"
End With
rptWks.Cells(oRow, 2).Value = "'" & .Name
rptWks.Cells(oRow, 3).Value _
= .Cells(DateRow, iCol).Address(0, 0)
rptWks.Cells(oRow, 4).Value _
= .Cells(CommentRow, iCol).Value
End If
Next iCol
End With
End If
Next wks

With rptWks.UsedRange
With .Columns
.ColumnWidth = 255
.AutoFit
End With
With .Rows
.AutoFit
End With
End With
End Sub

And this deletes the Report worksheet before it starts do the real work--you
won't have to delete it (manually or via a different button).

 

[Kenshe]

Dave,
It works like a charm! Now I just need to understand it

Since it now reports even empty comments, can I place a referance in
there to where if there is no comment in row29 then skip altogether?

like: "if .cells(comment.icol).value0 then next icol" or something on
that order.

Thanks for your great instruction!!

 

[Dave Peterson]

This portion could change:

If .Cells(DateRow, iCol).Value2 = CLng(myDate) Then
'found a match
oRow = oRow + 1
With rptWks.Cells(oRow, 1)
.Value = myDate
.NumberFormat = "mm/dd/yyyy"
End With
rptWks.Cells(oRow, 2).Value = "'" & .Name
rptWks.Cells(oRow, 3).Value _
= .Cells(DateRow, iCol).Address(0, 0)
rptWks.Cells(oRow, 4).Value _
= .Cells(CommentRow, iCol).Value
End If

to:

If .Cells(DateRow, iCol).Value2 = CLng(myDate) Then
'found a match
if trim(.cells(commentrow,icol).value) = "" then
'do nothing
else
oRow = oRow + 1
With rptWks.Cells(oRow, 1)
.Value = myDate
.NumberFormat = "mm/dd/yyyy"
End With
rptWks.Cells(oRow, 2).Value = "'" & .Name
rptWks.Cells(oRow, 3).Value _
= .Cells(DateRow, iCol).Address(0, 0)
rptWks.Cells(oRow, 4).Value _
= .Cells(CommentRow, iCol).Value
End If
end if

 

[Kenshe]

Thanks Dave,
It performs seamlessly! I can't possibly thank you enough.

Can you recommend a few books for newbee's like myself, more on th
order of example with explanation? Most of your code is a mystery to m
even when I run it line by line to see how it works, but still I woul
like to learn atleast the basics and understand the acronyms a littl
better.

Again, thanks for all your help.

Ke

 

[Dave Peterson]

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

Depending on how advanced you are...

Professional Excel Development
By Stephen Bullen, Rob Bovey, John Green

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.