VBA conditional replacement of text

[luv2bounce]

Hello you excel experts!!!! I don't know what has changed - but the macro that has been working well since this original post now gets a runtime error and doesn't work. I think it is not finding the heading "Project Started at Risk", but I can't figure out how to fix it? Could someone try to point me in the right direction? I've included a sample of the spreadsheet, and the code is below. I would be ever so grateful for help and understanding.

Sub ChangeStageStarted()
'
' ChangeStageStarted Macro
' Repalce Stage to 6x if project has started at risk
'
' Keyboard Shortcut: Ctrl+Shift+X
'
sn = ActiveSheet.UsedRange

For jj = 2 To UBound(sn, 2)
If sn(2, jj) = "Stage" Then x = jj
If sn(2, kk) = "Project Started At Risk" Then y = kk
If sn(2, mm) = "Certainty%" Then q = mm
Next

For j = 3 To UBound(sn)
If sn(j, y) = "Y" Then sn(j, x) = "6x. Project Started"
If sn(j, y) = "Y" Then sn(j, q) = 95
Next

ActiveSheet.UsedRange = sn
End Sub

[HansV]

Your loop variable is jj, but you refer to variables kk and mm that don't have a value.

Also: your comparison is case-sensitive. Z2 contains "Project Started at Risk" but the code looks for "Project Started At Risk" with "At" instead of "at".

Add the following lines at the top of the module:

Code: Select all

Option Explicit
Option Compare Text

and change the macro to

Code: Select all

Sub ChangeStageStarted()
'
' ChangeStageStarted Macro
' Replace Stage to 6x if project has started at risk
'
' Keyboard Shortcut: Ctrl+Shift+X
'
    Dim sn As Variant, jj As Long, x As Long, y As Long, q As Long, j As Long
    sn = ActiveSheet.UsedRange

    For jj = 2 To UBound(sn, 2)
        If sn(2, jj) = "Stage" Then x = jj
        If sn(2, jj) = "Project Started At Risk" Then y = jj
        If sn(2, jj) = "Certainty%" Then q = jj
    Next

    For j = 3 To UBound(sn)
        If sn(j, y) = "Y" Then sn(j, x) = "6x. Project Started"
        If sn(j, y) = "Y" Then sn(j, q) = 95
    Next

    ActiveSheet.UsedRange = sn
End Sub

[luv2bounce]

You are so wonderful! I am truly, truly grateful! Sometimes you can't see the wood from the trees when you don't know what you're looking for!