Excel if range of cell contain a text put a cell value in [Closed]

Report
Posts
4
Registration date
Tuesday September 16, 2014
Status
Member
Last seen
October 1, 2014
-
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
-
Hi All,

How do I put a cell value into a cell when the range of cell contain a specfic text.
For example, in the sample below, I want to put the cell value (13/2/2014) into B3 when a range of cell (A) contain Sam.




A1 Tom
A2 13/2/2014
A3 Sam
A4 15/6/2014

B1
B2
B3 13/2/2014
B4


Is it possible as well that I can copy the cell value (13/2/2014) to all fields in column B till it reaches TOM (A1) for example?

Please enlighten me.
Thanks

3 replies

Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
Hi Diego,

Your first question can be solved by using this formula in B1 and then drag it down as far as needed:
=IF(A1="Sam","13/2/2014","")

Your second question is a little strange, since Tom is at the first row. Meaning the value "13/2/2014" will not be entered at all.

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4001 users have said thank you to us this month

Posts
4
Registration date
Tuesday September 16, 2014
Status
Member
Last seen
October 1, 2014

Hi TrowaD,

ManHi TrowaD,

Many thanks for the help.
It worked for the first question.

For the second question , I have change the format.
Is it possible as well that I can copy the cell value (23/12/2014) to all fields in column B till it reaches TOM (A1) for 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


Something like below

B1
B2 23/12/2041
B3 23/12/2014
B4 23/12/2014
B5 23/12/2014
B6
B7
B8
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
Posts
4
Registration date
Tuesday September 16, 2014
Status
Member
Last seen
October 1, 2014

Hi Trowa,

Yes you are right. Please enlighten me on how to do it.
Many thanks in advance = )

Best Regards,
Diego
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
diegomagum
Posts
4
Registration date
Tuesday September 16, 2014
Status
Member
Last seen
October 1, 2014

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
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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