Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Overwriting
Hi.
I have a piece of code which asks the user to enter details of the data that is being imported and which then enters this date in the column Y of the spreadsheet - next to those new data entries (see below): dt = Application.InputBox("Please enter details (mmm-yyyy) for the data imported") lastRow = Worksheets("C-National").Cells(Rows.Count, "B").End(xlUp).Row For Each c In Worksheets("C-National").Range("B3:B" & lastRow) If c.Value < "" And Cells(c.Row, "Y").Value = "" Then Worksheets("C-National").Cells(c.Row, "Y").Value = dt End If Next c This works fine and enters the dates in a treat. However, when the next batch of data is imported (the next month) it not only putting in the date for that new data, but is overwriting the dates for the previous month's data. I thought that I had got round this by asking the macro to look in the cells in Column Y and put in the data only if the cell was blank - but this does not seem to be having the desired effect. Could anyone advise how I might modify this code to get round this issue. Many thanks in advance. Liz. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Overwriting
Hi
It should work if C-National sheet is the active sheet when you run the macro , as you do not have set the sheet reference: If c.Value < "" And Worksheets("C-National").Cells(c.Row, "Y").Value = "" Then or use a With statement to set the reference (note the leading dot): dt = Application.InputBox _ ("Please enter details (mmm-yyyy) for the data imported ") With Worksheets("C-National") lastRow = .Cells(Rows.Count, "B").End(xlUp).Row For Each c In .Range("B3:B" & lastRow) If c.Value < "" And .Cells(c.Row, "Y").Value = "" Then .Cells(c.Row, "Y").Value = dt End If Next c End With Regards, Per On 4 Mar., 16:09, BoRed79 wrote: Hi. I have a piece of code which asks the user to enter details of the data that is being imported and which then enters this date in the column Y of the spreadsheet - next to those new data entries (see below): dt = Application.InputBox("Please enter details (mmm-yyyy) for the data imported") lastRow = Worksheets("C-National").Cells(Rows.Count, "B").End(xlUp).Row For Each c In Worksheets("C-National").Range("B3:B" & lastRow) If c.Value < "" And Cells(c.Row, "Y").Value = "" Then Worksheets("C-National").Cells(c.Row, "Y").Value = dt End If Next c This works fine and enters the dates in a treat. *However, when the next batch of data is imported (the next month) it not only putting in the date for that new data, but is overwriting the dates for the previous month's data. * I thought that I had got round this by asking the macro to look in the cells in Column Y and put in the data only if the cell was blank - but this does not seem to be having the desired effect. Could anyone advise how I might modify this code to get round this issue. Many thanks in advance. Liz. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Overwriting
First, there's a small bug in your code that you may see if the wrong sheet is
active. If you're careful (or lucky), you may never see it. For Each c In Worksheets("C-National").Range("B3:B" & lastRow) If c.Value < "" And Cells(c.Row, "Y").Value = "" Then Worksheets("C-National").Cells(c.Row, "Y").Value = dt End If That cells(c.row,"Y") refers to either the activesheet (if the code is in a general module) or refers to the worksheet that owns the code (if the code is in a worksheet module). I'd use: with worksheets("C-National") For Each c In .Range("B3:B" & lastRow) If c.Value < "" _ And .Cells(c.Row, "Y").Value = "" Then .Cells(c.Row, "Y").Value = dt end if next c End with Notice the leading dots on those range/cell objects. That means that they belong to the object in the previous with statement. In this case, it's the C-National worksheet. So you may not be checking the C-National sheet for that cell's value! If that doesn't fix the problem, then... I bet that you have something in column Y of that row--maybe a space (or multiple spaces) or if you're getting the data from the web, it could be one of those non-breaking HTML space characters. Those make the cells look empty when they're not. Chip Pearson has a very nice addin that will help determine what's in the cell: http://www.cpearson.com/excel/CellView.aspx You may be able to add a little procedure that fixes those values before your real macro starts. BoRed79 wrote: Hi. I have a piece of code which asks the user to enter details of the data that is being imported and which then enters this date in the column Y of the spreadsheet - next to those new data entries (see below): dt = Application.InputBox("Please enter details (mmm-yyyy) for the data imported") lastRow = Worksheets("C-National").Cells(Rows.Count, "B").End(xlUp).Row For Each c In Worksheets("C-National").Range("B3:B" & lastRow) If c.Value < "" And Cells(c.Row, "Y").Value = "" Then Worksheets("C-National").Cells(c.Row, "Y").Value = dt End If Next c This works fine and enters the dates in a treat. However, when the next batch of data is imported (the next month) it not only putting in the date for that new data, but is overwriting the dates for the previous month's data. I thought that I had got round this by asking the macro to look in the cells in Column Y and put in the data only if the cell was blank - but this does not seem to be having the desired effect. Could anyone advise how I might modify this code to get round this issue. Many thanks in advance. Liz. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
overwriting formulas | Excel Programming | |||
Overwriting data | Excel Discussion (Misc queries) | |||
Spreadsheets are overwriting each other | Excel Discussion (Misc queries) | |||
Overwriting Cells | Excel Discussion (Misc queries) | |||
overwriting a file | Excel Programming |