VB Help Needed: change X Col which contain "YES" to data at Col X + Merge VB Code

[harky]

Hi,
I need a VB to change Col Q which contain "YES" to data at Col H (The rest col remain untouched)
and possible to merge with this VB Code?

Sub S2_AddZero()
    Dim a
    Dim i As Long
    Dim R As Range


    For Each R In Range("D2", Range("D" & Rows.Count).End(xlUp))
        If Len(R.Text) >= 5 And Left(R.Text, 1) <> "0" Then
            R.NumberFormat = "General"
        Else
            R.NumberFormat = "@"
            R.HorizontalAlignment = xlCenter
            R.Value = Right("00000" & R.Text, 5)
        End If
    Next R
End Sub

My data is like this

Result

 

[Fluff]

How about

   With Range("Q:Q")
      .Replace "Yes", "=xxx", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).FormulaR1C1 = "=rc[-9]"
      .Value = .Value
   End With

This can simply be placed at the end of your existing macro

 

[harky]

thank you so much. it work great.

How about

   With Range("Q:Q")
      .Replace "Yes", "=xxx", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).FormulaR1C1 = "=rc[-9]"
      .Value = .Value
   End With

This can simply be placed at the end of your existing macro

 

[Fluff]

Glad to help & thanks for the feedback