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.