If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
How to allow "if then" text?
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! -- Kenshe ------------------------------------------------------------------------ Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850 View this thread: http://www.excelforum.com/showthread...hreadid=486081 |
#22
|
|||
|
|||
How to allow "if then" text?
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 wrote: 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! -- Kenshe ------------------------------------------------------------------------ Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850 View this thread: http://www.excelforum.com/showthread...hreadid=486081 -- Dave Peterson |
#23
|
|||
|
|||
How to allow "if then" text?
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!! -- Kenshe ------------------------------------------------------------------------ Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850 View this thread: http://www.excelforum.com/showthread...hreadid=486081 |
#24
|
|||
|
|||
How to allow "if then" text?
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 wrote: 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!! -- Kenshe ------------------------------------------------------------------------ Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850 View this thread: http://www.excelforum.com/showthread...hreadid=486081 -- Dave Peterson |
#25
|
|||
|
|||
How to allow "if then" text?
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 the order of example with explanation? Most of your code is a mystery to me even when I run it line by line to see how it works, but still I would like to learn atleast the basics and understand the acronyms a little better. Again, thanks for all your help. Ken -- Kenshe ------------------------------------------------------------------------ Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850 View this thread: http://www.excelforum.com/showthread...hreadid=486081 |
#26
|
|||
|
|||
How to allow "if then" text?
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. Kenshe wrote: 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 the order of example with explanation? Most of your code is a mystery to me even when I run it line by line to see how it works, but still I would like to learn atleast the basics and understand the acronyms a little better. Again, thanks for all your help. Ken -- Kenshe ------------------------------------------------------------------------ Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850 View this thread: http://www.excelforum.com/showthread...hreadid=486081 -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking text boxes | Volunteer Mom | Publisher | 7 | November 12th, 2008 01:29 AM |
Formulas dealing with text data | Bagia | Worksheet Functions | 6 | June 20th, 2005 10:29 PM |
Ideas On Producing Envelopes & Labels For Data | RNUSZ@OKDPS | Setting Up & Running Reports | 0 | April 28th, 2005 03:22 PM |
Access reports with a horizontal line after each record??? | Bill via AccessMonster.com | Setting Up & Running Reports | 6 | March 9th, 2005 04:51 PM |
Concatenatd fields in a query for a searching form | Marc | Running & Setting Up Queries | 8 | October 19th, 2004 08:49 PM |