VBA macro to check for match

[Roadhouse]

Hi can anyone help please I have a workbook with a "controls" sheet then the sheets after the controls sheet all have data in them. I need a macro to check if the name in A1 matches exactly with a cell on row 3 on every sheet. Then if any don't match write the sheet and name that doesn't match on the controls sheet in columns A and B

[djbomaha]

Hi Roadhouse,

Here is a program that I think might do what you are asking.

Sub Controls()

' Dimension array variables
Dim shtname() As String
Dim arrA1() As String

' Get Number of Columns in sheet
numcols = Application.Columns.Count

'Verify if Sheet "controls" exists, if not, create it
On Error Resume Next
Err.Clear
Sheets("controls").Activate
If (Err.Number <> 0) Then
   Sheets.Add before:=Sheets(1)
   ActiveSheet.Name = "controls"
End If
On Error GoTo 0

' Verify "controls" sheet is first in list
If (Sheets(1).Name <> "controls") Then Sheets("controls").Move before:=Sheets(1)

' Go through Sheets and check if name in range A1 shows up in Row 3 for each sheet
ncontrols = 0
If (Sheets.Count > 1) Then
   For i = 2 To Sheets.Count
      Sheets(i).Activate
      ' Get value of A1
      str1 = Range("A1")
      ' Find Last Colum with Date in row 3
      lastcol = Range(Cells(3, numcols), Cells(3, numcols)).End(xlToLeft).Column
      For j = 1 To lastcol
         If (str1 = Cells(3, j)) Then GoTo 10  'Value of A1 has a match in row 3
      Next j
      ' Add Sheet name and value of A1 to array if A1 has not match in row 3
      ncount = ncount + 1
      ReDim Preserve shtname(1 To ncount) As String
      ReDim Preserve arrA1(1 To ncount) As String
      shtname(ncount) = Sheets(i).Name
      arrA1(ncount) = Range("A1")
10 'Next i
   Next i
End If

' Populate "controls" sheet
If (ncount > 0) Then
   Sheets("controls").Activate
   Cells.Clear
   For i = 1 To ncount
      Range("A" & i) = shtname(i)
      Range("B" & i) = arrA1(i)
   Next i
   ' Put in Headings
   Rows(1).Insert shift:=xlDown
   Range("A1") = "Sheet Name"
   Range("B1") = "A1 Value"
   Rows(1).Font.Bold = True
   Cells.VerticalAlignment = xlCenter
   Cells.HorizontalAlignment = xlCenter
   Columns("A:B").AutoFit
End If

End Sub

Hope it helps,

Dan

[Roadhouse]

dan that was brilliant thanks