VBA Populate column with formula

[Rymare]

I have a dynamic range, it changes all the time, and every time a value looks like so: #####-### (or in my formula I put ?????-???), I need this formula: =IF(COUNTIF(A2, "?????-???")>0,MID(A2,5,1),"") to be placed in the corresponding row in column C, because I need to extract the 5th number in the pattern (it corresponds to a year). I need "A2" to automatically adjust. I've attempted to hit "Record macro" which I'm generally loathe to do and then edit the corresponding code by inputting the dynamic range instead of the static one the record button usually gives. That didn't work. So far, this is what I've come up with and it also doesn't work. At all. Not even a pity paste into any of the cells, it just runs and stays blank. I know that this can be done manually but for the purposes of this excel file--nothing can be manual, it needs to be programmatic.

Sub comparedate()

Dim r_Sheet As Worksheet: Set r_Sheet = Sheets("Review")
Dim Lastrow As Integer
Dim rng As Range
Dim r As Variant
Dim form As String




Lastrow = r_Sheet.Range("A" & Rows.Count).End(xlUp).Row
Set rng = r_Sheet.Range("A2:A" & Lastrow)
For Each r In rng
    form = "=IF(COUNTIF(" & r_Sheet.Range("A" & r.Row).Value & ", ""?????-???"")>0,MID(" & r_Sheet.Range("A" & r.Row).Value & ",5,1),"""")"
    If r.Value <> "" Then
        r.Offset(0, 3).Value = form
    End If
Next r






End Sub

Any help is greatly appreciated!

 

[yky]

Since you are using VBA, you don't really need the following.

form = "=IF(COUNTIF(" & r_Sheet.Range("A" & r.Row).Value & ", ""?????-???"")>0,MID(" & r_Sheet.Range("A" & r.Row).Value & ",5,1),"""")"

Can't you simply use:

IF COUNTIF(r_Sheet.Range("A" & r.Row).Value,"?????-???")>0 THEN
form = MID("A"&r.Row,5,1)
END IF

 

[Rymare]

Thank you works perfectly!