Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code relative to current date
Hi,
I'm working with Excel 2007, and every day I receive a new automatically generated worksheet with data from every day of that month up to and including the current day. So column A contains the date, looks something like: 7/01/2009 7/01/2009 7/01/2009 7/02/2009 7/02/2009 etc. Suppose that I get the worksheet for 7/29/2009, in which case I need to pull out data only from 7/28/2009. Furthermore I only need to pull data from that date that contains certain keywords, like "wood" and "tile." The current solution I can think of would be to either pull all the data by keyword, and then run something that eliminates any of that data that isn't the desired date. Or, there may be a way to look at the last non empty cell in A, subtract 1 from that date and then use that new date as the criteria for what data the macro pulls. The current macro I built pulls the data by keyword, but not by date, so maybe either modify this macro or write a new, simpler one? Sub DataMove() RowCount = 1 For Each c In Range("B:B") If c.Value Like "*Wood*" Or _ c.Value Like "*Stone*" Or _ c.Value Like "*Tile*" Then Cells(RowCount, "H").Value = c.Value Cells(RowCount, "I").Value = c.Offset(0, 1).Value Cells(RowCount, "J").Value = c.Offset(0, 2).Value Cells(RowCount, "K").Value = c.Offset(0, 3).Value Cells(RowCount, "G").Value = c.Offset(0, -1).Value RowCount = RowCount + 1 End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code relative to current date
Sub DataMove()
set SrcSht = Sheets("Sheet1") set DestSht = Sheets("Sheet2") NewRow = 1 RowCount = 1 with SrcSht Do while .range("B" & RowCount) < "" Keyword = .range("B" & RowCount) if Keyword = "wood" or _ Keyword = "stone" or _ Keywood = "tile" then DestSht.Cells(NewRow, "H").Value = .Cells(NewRow, "A") DestSht.Cells(NewRow, "J").Value = .Cells(NewRow, "B") DestSht.Cells(NewRow, "K").Value = .Cells(NewRow, "C") DestSht.Cells(NewRow, "G").Value = .Cells(NewRow, "D") NewRow = NewRow, + 1 end if RowCount = RowCount + 1 Loop end with End Sub "Jbm" wrote: Hi, I'm working with Excel 2007, and every day I receive a new automatically generated worksheet with data from every day of that month up to and including the current day. So column A contains the date, looks something like: 7/01/2009 7/01/2009 7/01/2009 7/02/2009 7/02/2009 etc. Suppose that I get the worksheet for 7/29/2009, in which case I need to pull out data only from 7/28/2009. Furthermore I only need to pull data from that date that contains certain keywords, like "wood" and "tile." The current solution I can think of would be to either pull all the data by keyword, and then run something that eliminates any of that data that isn't the desired date. Or, there may be a way to look at the last non empty cell in A, subtract 1 from that date and then use that new date as the criteria for what data the macro pulls. The current macro I built pulls the data by keyword, but not by date, so maybe either modify this macro or write a new, simpler one? Sub DataMove() RowCount = 1 For Each c In Range("B:B") If c.Value Like "*Wood*" Or _ c.Value Like "*Stone*" Or _ c.Value Like "*Tile*" Then Cells(RowCount, "H").Value = c.Value Cells(RowCount, "I").Value = c.Offset(0, 1).Value Cells(RowCount, "J").Value = c.Offset(0, 2).Value Cells(RowCount, "K").Value = c.Offset(0, 3).Value Cells(RowCount, "G").Value = c.Offset(0, -1).Value RowCount = RowCount + 1 End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code relative to current date
Joel,
Thanks for spending time helping me on this, it's much appreciated. I'm running into a few issues with your code though. Minorly, in case you try to test the code, note that there's an extra comma on New Row = New Row + 1. Onto the real troubles I'm having though. First, the code doesn't seem to want to grab the correct rows of Keywords with the "if Keyword" strings. For some reason the old "if c.Value Like" grabbed the right ones, but not these, so maybe we should use the old code on that? Another less pressing problem is that the new code is mixing up what order the data is in. I think I should have been more clear in the original description: columns A-E have data in them, A holds the date, B holds the name with the words we're searching for, and C-E have numerical data. The new code however is placing the data into columns in the order DA Blank BC, and E doesn't even show up. More important than these issues however is that the new code does pull specific dates, but not the ones we need. I used your code on a worksheet that goes up to 7/23/09, but the data pulled only consisted of dates 7/01/09 to 7/03/09. For that sheet, for example, I am trying to get data only from 7/22/09. I guess I didn't realize how difficult this might be, and apologies if I wasn't clear at the start -- if you need more clarification to help, please just ask, any way I can help you, helps me. Thanks for the help again, though. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code relative to current date
I fixed the row problem and the comma. the order can be changed to any order
you want. I gave just an example. Put in as many columns of data that you want to move and the source and desttination column as required. I don't know what column the dat is located so I can't help with filtering the date automatically. Sub DataMove() Set SrcSht = Sheets("Sheet1") Set DestSht = Sheets("Sheet2") NewRow = 1 RowCount = 1 With SrcSht Do While .Range("B" & RowCount) < "" Keyword = .Range("B" & RowCount) If Keyword = "wood" Or _ Keyword = "stone" Or _ Keywood = "tile" Then DestSht.Cells(NewRow, "H").Value = .Cells(RowCount, "A") DestSht.Cells(NewRow, "J").Value = .Cells(RowCount, "B") DestSht.Cells(NewRow, "K").Value = .Cells(RowCount, "C") DestSht.Cells(NewRow, "G").Value = .Cells(RowCount, "D") NewRow = NewRow + 1 End If RowCount = RowCount + 1 Loop End With End Sub "Jbm" wrote: Joel, Thanks for spending time helping me on this, it's much appreciated. I'm running into a few issues with your code though. Minorly, in case you try to test the code, note that there's an extra comma on New Row = New Row + 1. Onto the real troubles I'm having though. First, the code doesn't seem to want to grab the correct rows of Keywords with the "if Keyword" strings. For some reason the old "if c.Value Like" grabbed the right ones, but not these, so maybe we should use the old code on that? Another less pressing problem is that the new code is mixing up what order the data is in. I think I should have been more clear in the original description: columns A-E have data in them, A holds the date, B holds the name with the words we're searching for, and C-E have numerical data. The new code however is placing the data into columns in the order DA Blank BC, and E doesn't even show up. More important than these issues however is that the new code does pull specific dates, but not the ones we need. I used your code on a worksheet that goes up to 7/23/09, but the data pulled only consisted of dates 7/01/09 to 7/03/09. For that sheet, for example, I am trying to get data only from 7/22/09. I guess I didn't realize how difficult this might be, and apologies if I wasn't clear at the start -- if you need more clarification to help, please just ask, any way I can help you, helps me. Thanks for the help again, though. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code relative to current date
Joel,
I'm afraid I'm being counterproductive here. Let my try to simplify this. Don't worry about keywords for this example at all. Imagine I have the dates in column A like this, with corresponding data in column B Column A 7/01/09 7/01/09 7/02/09 7/02/09 7/02/09 7/03/09 7/03/09 Now suppose the date is 7/03/09, so in this instance I need to copy rows 3-5 (data from the previous day) and place that data in Columns D and E. So Column D would look like this Column D 7/02/09 7/02/09 7/02/09 And Column E would have the corresponding data to those dates that was in B. I need to do this every day, with a separate new document, so for today (the 29th), I need Column D to hold only data from the 28th. The last cell in A will always contain the current date. I'm sorry to have wasted your effort on the previous code, but I think this will get at what I'm trying to do with better accuracy. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code relative to current date
I added an inport box to select the rows you want. I also added a new workbook
Sub DataMove() Set SrcSht = Sheets("Sheet1") Set Newbk = Workbooks.Add Set DestSht = Newbk.Sheets("Sheet1") SrcSht.Activate Set MyCell = Application.InputBox( _ prompt:="Select Row to Export", _ Type:=8) Set SrcSht = MyCell.Parent FirstRow = MyCell.Row LastRow = FirstRow + MyCell.Rows.Count - 1 NewRow = 1 RowCount = 1 With SrcSht For RowCount = FirstRow To LastRow Keyword = .Range("B" & RowCount) If Keyword = "wood" Or _ Keyword = "stone" Or _ Keywood = "tile" Then DestSht.Cells(NewRow, "H").Value = .Cells(RowCount, "A") DestSht.Cells(NewRow, "J").Value = .Cells(RowCount, "B") DestSht.Cells(NewRow, "K").Value = .Cells(RowCount, "C") DestSht.Cells(NewRow, "G").Value = .Cells(RowCount, "D") NewRow = NewRow + 1 End If RowCount = RowCount + 1 Next RowCount End With End Sub "Jbm" wrote: Joel, I'm afraid I'm being counterproductive here. Let my try to simplify this. Don't worry about keywords for this example at all. Imagine I have the dates in column A like this, with corresponding data in column B Column A 7/01/09 7/01/09 7/02/09 7/02/09 7/02/09 7/03/09 7/03/09 Now suppose the date is 7/03/09, so in this instance I need to copy rows 3-5 (data from the previous day) and place that data in Columns D and E. So Column D would look like this Column D 7/02/09 7/02/09 7/02/09 And Column E would have the corresponding data to those dates that was in B. I need to do this every day, with a separate new document, so for today (the 29th), I need Column D to hold only data from the 28th. The last cell in A will always contain the current date. I'm sorry to have wasted your effort on the previous code, but I think this will get at what I'm trying to do with better accuracy. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current Date Code in form | Excel Discussion (Misc queries) | |||
code that searches for current date | Excel Programming | |||
Restricting date entry relative to current date | Excel Worksheet Functions | |||
Can I add the current date/time to my code when I print it? | Excel Programming | |||
code pasting a date changes date format in current month only | Excel Programming |