Within a range of cells, how do I enter a formula based on condition with vba?

Question

I'm new to VBA. I have a range of cells in column B with the format:

1###-2#-3##-4#

I want to enter a mid formula to get the last cell based on the fact that:

the next cell is empty
the cell matches the regex pattern

My code doesn't give me any errors but doesn't run.

Option Explicit

Sub DeptProgram()

    Dim regex As String
    Dim Pattern As String
    Dim cell As Range

    regex = "[0-9]{4}-[0-9]{2}-[0-9]{3}-[0-9]{2}"

    For Each cell In Range("B1:B600")
        If Pattern = regex And ActiveCell.Offset(1, 0) <> "" Then
            ActiveCell.Offset(0, 2).Formula = "MID(B" & "activecell.Row.Offset(0,-2), 6, 11)"
        End If
    Next cell

End Sub

On another note: I used regex in VBA based on this article.

What is `Pattern`? Do the hash marks represent numbers - if not, you won't get a match anyway? More fundamentally, you haven't declared or initialized regexp object. — SJR, Jul 09 '19 at 16:06
Should I have done something like `cell.value = regex`? The hash marks do represent numbers. — Julian, Jul 09 '19 at 16:10
`if cell like "####-##-###-##" and cell.offset(1,0) = "" then` — Scott Craner, Jul 09 '19 at 16:14
I suggest you re-read that link which has all the data, e.g. `Dim regEx As New RegExp` early binding. That said, you can use the Like operator in this particular case. (As per Scott's comment.) — SJR, Jul 09 '19 at 16:15
You are missing an `=` in your formula: `...Formula = "MID(B" &...` should be `...Formula = "=MID(B" &...` — cybernetic.nomad, Jul 09 '19 at 16:20

Scott Craner · Accepted Answer · 2019-07-09T16:47:22.750

2

As per all the comments:

Option Explicit

Sub DeptProgram()

    Dim cell As Range

    For Each cell In Range("B1:B600")
        if cell like "####-##-###-##" and cell.offset(1,0) = "" then
            cell.Offset(2,0).Formula = "=MID(" & cell.Address & ", 6, 11)"
        End If
    Next cell

End Sub

After running:

edited Jul 09 '19 at 16:47

answered Jul 09 '19 at 16:35

Scott Craner

148,073
10
49
81

It gives me a `type mismatch` error at the if statement. Does "####-##-###-##" make the cell a string? – Julian Jul 09 '19 at 16:39
It is working for me, do you have errors in the column? – Scott Craner Jul 09 '19 at 16:42
I have no errors. I'll have to figure this out myself. Now it's giving me a 1004 error, application defined... – Julian Jul 09 '19 at 16:45

Within a range of cells, how do I enter a formula based on condition with vba?

1 Answers1