0

I am new to vb.script so this may just be a formatting question that I cannot find the answer. The problem is validating data in cells that are on different sheets in the same workbook.

Looping through worksheets and then looping through the cell range:

Private Sub Validate(ByRef objWorkbook As Workbook)

Dim strPattern As String: strPattern = "^\-{0,1}\d+(.\d+){0,1}$"
Dim regEx As New VBScript_RegExp_55.RegExp
Dim strInput As String
Dim strOutput As String
Dim Myrange As Range
Dim regExCount As Object
Set regExCount = CreateObject("vbscript.regexp")
On Error Resume Next

For Each objWorksheet In objWorkbook.Worksheets
    If (UCase(objWorksheet.Name) = "Foo") Then
        objWorksheet.Select
        Range("Q2").Select

    ElseIf (UCase(objWorksheet.Name) = "Bar") Or (UCase(objWorksheet.Name) = "Poo") Then
        objWorksheet.Select
        Set Myrange = ActiveSheet.Range("D51:AA76")

        For Each cell In Myrange.Cells
            If strPattern <> "" Then
            strInput = cell.Value
            strReplace = ""

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            Set regExCount = regEx.Execute(strInput)
            If regExCount.Count = 0 Then
                strOutput = strOutput + "Illegal character at " + cell.AddressLocal + "\r\n"
            End If
        Next cell
    End If

Next
MsgBox (strOutput)

End Sub

When I compile I get an error of a Next without a For loop at the Next Cell. Removing that line and I get an error for Block If without an End Ff highlighting the End Sub. Adding an End If before the End sub and I get a Next without a For error.

2 Answers 2

2

Isn't it this block missing and end if?

        If strPattern <> "" Then
            strInput = cell.Value
            strReplace = ""
Sign up to request clarification or add additional context in comments.

Comments

1

Your

If strPattern <> "" Then

is not closed between

    End If ' regExCount.Count
Next cell ' In Myrange.Cells

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.