Excel if range of cell contain a text put a cell value in

Hi Diego,

Let's take a look at the bigger picture as I am assuming that you have a lot more data rows.

Do you want to loop through column A from the bottom up, when a date is found then continue pasting the date to column B until a name is found?

Example:
A1 Tom
A2 20-2-2014
A3 16-4-2014
A4 22-6-2014
A5 23-12-2014
A6 Sam
A7 15-6-2014
A8 12-10-2014
A9 25-11-2014
A10 Adam
A11 1-1-2014
A12 20-3-2014

B1
B2 23-12-2014
B3 23-12-2014
B4 23-12-2014
B5 23-12-2014
B6
B7 25-11-2014
B8 25-11-2014
B9 25-11-2014
B10
B11 20-3-2014
B12 20-3-2014

Let me know if this is what you are looking for.

Best regards,
Trowa

Hi Diego,

Use the following code and be enlightened:

Sub RunMe()
Dim x As Long
Dim MyDate As Date

MyDate = Range("A" & Rows.Count).End(xlUp)

For x = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If IsDate(Range("A" & x)) = False Then
        MyDate = Range("A" & x - 1)
    Else
        Range("B" & x) = MyDate
    End If
Next x

End Sub

Best regards,
Trowa

Hi Trowa,

I just need to paste the code into sheet , will do right ?
Thanks in advance.

Best Regards,
Diego

Hi Diego,

The code needs to go into a module:
From Excel hit Alt+ F11 to open a new window.
Here you go to the top menu and click on Insert > Module.
Now paste the code in the big white field and close the newly opened window.
Back at Excel hit Alt+ F8 to display the available macro's.
Double click on RunMe to run the code.

We can also make the code run automatically, for example when the sheet is activated.
Then you would place the code into the sheet.
The code will look a little different as shown below:

Private Sub Worksheet_Activate()
Dim x As Long
Dim MyDate As Date

MyDate = Range("A" & Rows.Count).End(xlUp)

For x = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If IsDate(Range("A" & x)) = False Then
        MyDate = Range("A" & x - 1)
    Else
        Range("B" & x) = MyDate
    End If
Next x

End Sub

Best regards,
Trowa

Hi Trowa,

I try to use the codes Sub RunMe()
that you mentioned but I have a error of type mismatch.
Below is my sample data. May I know how to resolve it.

I try to use the test data that I mention above, it work.

A1 6/6/2014
A2 14/6/2014
A3 26/6/2014
A4 Payment
A5 29/4/2014
A6 29/4/2014
A7 9/5/2014
A8 Payment
A9 22/5/2014
A10 29/5/2014
A11 29/5/2014
A12 29/5/2014
A13 29/5/2014
A14 9/6/2014
A15 Payment
A16 20/5/2014
A17 10/6/2014
A18 3/5/2014
A19 8/5/2014
A20 Payment


Best Regards,
Diego

Hi Diego,

That is because your and my sample data both ended with a date.

Let me add a condition to the code, so it doesn't matter if your data ends with a date or text.

Your latest sample also started with a date, so I let the code loop to the first row as well (as opposed to the second row).

Here it is:

Sub RunMe()
Dim x As Long
Dim MyDate As Date

If IsDate(Range("A" & Rows.Count).End(xlUp)) = False Then
    MyDate = Range("A" & Rows.Count).End(xlUp).Offset(-1, 0)
Else
    MyDate = Range("A" & Rows.Count).End(xlUp)
End If

For x = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If IsDate(Range("A" & x)) = False Then
        MyDate = Range("A" & x - 1)
    Else
        Range("B" & x) = MyDate
    End If
Next x

End Sub

NOTE: the error still occurs if the last 2 entry's are both non dates.

Best regards,
Trowa