Loop Overwriting

[BoRed79]

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.

 

[Don Guillett]

Maybe?
lastRow = Worksheets("C-National").Cells(Rows.Count, "B").End(xlUp).Row+1

 

[Per Jessen]

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

 

[Dave Peterson]

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.