Change Column Location based on cell value

[lawboy1976]

Hi All,

I have some code which checks whether certain columns have a valid date in their cells. If there is no valid date, teh contents of the cell are replaced by the text "Incorrect Date Format".

The table runs from Column A to Column BN. Column A is always populated, so I use it as a reference for Row Count in the below reference. There are various error tests that are performed on the data. The number of errors per row is added up and totalled in cell Bo as each sub runs.
So, in the below sample, you will see that if a cell in column AW contains the text "Incorrect Date Format", then the cell is highlighted, and the value of Cell BO is incremented by 1.
So, if there were two errors noted before this was run, BO would equal 3 once it has been run (the two pre-existing ones + 1)

Here's the code:

Sub datekiller()

last = Cells(Rows.Count, "a").End(xlUp).Row
For i = last To 2 Step -1
If Cells(i, "aw").Value = "Incorrect Date Format" Then
Cells(i, "aw").interior.colorindex = 3
Cells(i, "bo").Value = Cells(i, "bo").Value + 1
End If
Next i
End Sub


Now, my question is this: I would like a cell to contain the text "Cell AW in this row has an invalid date format. It's been deleted. Please check it on the system".

If the value of BO = 1, then I would like that text to appear in BP, next to BO.
If the value of BO = 2, then I would like that text to appear in BQ (BO +2)
If the value of BO = 3, then I would like that text to appear in BR (BO +3)
and so on.

I thought the code would be like this:
Cells(i, ("bo" + (cells(i, "BO).value))).Value = "Cell AW in this row has an invalid date format. It's been deleted. Please check it on the system."

but I get a type mismatch error. I thought that it would add the value of BO to the reference to BO itself, but it clearly doesn't like that.

Anyone got any ideas?
#


Got it:
Cells(i, "bo").Offset(0, Cells(i, "BO").Value).Value = "There was a Date entered for Date Of Instruction, but it was in the wrong format. It HAS to be in the format 01/01/01."

Thanks