Add text to cell on condition

[Apple08]

Hi everyone

I need to add a text 'P-' to the cell text in column F based on the value of column M. If column M's cell value is 'Test', then 'P-' have to add to the text in column F e.g. P-XYZ123

I have setup code as below, however it doesn't work:

Dim Lst As Long, r As Long
Lst = Range("A" & Rows.Count).End(xlUp).Row
For r = 1 To Lst
    With Sheets("Combined_Data").Range("M" & r)
        If .Value = "Test" Then
              .Range("F" & r).Value = "P-" & Range("F" & r).Value
              Exit For
        End If
    End With
 Next r

Please could anyone help? Many thanks.

 

[dreid1011]

Dim Lst As Long, r As Long
Lst = Range("A" & Rows.Count).End(xlUp).Row
For r = 1 To Lst
    With Sheets("Combined_Data").Range("M" & r)
        If .Value = "Test" Then
              .Range("F" & r).Value = "P-" & Range("F" & r).Value
              [COLOR=#FF0000][B]Exit For[/B][/COLOR]
        End If
    End With
 Next r

Well, if this is doing anything at all, it looks like it is going exit the loop after the first occurrence of "Test". So, if there are multiple occurrences, it will only do one.

Is that what is happening? Or nothing at all? You weren't clear by "it doesn't work".

 

[Fluff]

As well as what dreid1011 has pointed out there are a few other potential problems
1) You are calculating Lst based on the active sheet rather than the Combined data sheet.
2) Because of the way you have used the with statement you are actually changing the value in col R not col F
3) The value in col R on the Combined sheet will equal the value in col F on the Active sheet = "P-"

Try

   Dim Lst As Long, r As Long
   With Sheets("Combined_Data")
      Lst = .Range("A" & Rows.Count).End(xlUp).Row
      For r = 1 To Lst
         If .Value = "Test" Then
            .Range("F" & r).Value = "P-" & .Range("F" & r).Value
         End If
      Next r
   End With

 

[Apple08]

There is nothing occurred after I run the macro. So should I just need to remove Exit for?

 

[Fluff]

Oops missed abit

   Dim Lst As Long, r As Long
   With Sheets("Combined_Data")
      Lst = .Range("A" & Rows.Count).End(xlUp).Row
      For r = 1 To Lst
         If .Range("M" & r).Value = "Test" Then
            .Range("F" & r).Value = "P-" & .Range("F" & r).Value
         End If
      Next r
   End With

 

[Rick Rothstein]

I think this code will do what your posted code attempted to do...

[table="width: 500"]
[tr]
	[td]Sub AddPDashIfTest()
  Dim LastRow As Long
  LastRow = Sheets("Combined Data").Cells(Rows.Count, "M").End(xlUp).Row
  Range("F1:F" & LastRow) = Evaluate(Replace("IF('Combined Data'!M1:M#=""Test"",""P-""&F1:F#,IF(F1:F#="""","""",F1:F#))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[Apple08]

Thanks Fluff and Rick, it is now working perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback