Macro to show row number and sheet name

[Flupsie]

I have account numbers X2365, X2390 in Col A and values pertaining to these are in Col C in the same row as Col A

I would like a message box to advise me the sheet name and row number containing a zero (0) that is in the same row as X2365 & X2390

For Eg if X2365 on Sheet7 = 0 and is on row C415 , the message box must show sheet7 C415 etc

Your assistance is most appreciated

[LalitPandey87]

Here you go:

Open your excel file and press Alt + F11 then Press Ctrl + R and your left hand side double click on Thisworkbook and paste below code in right side window.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    Call lmp_Test


End Sub


Sub lmp_Test()


    Dim varData()           As Variant
    Dim rngToCheck          As Range
    Dim strShtName          As String
    Dim lngLoop             As Long
    Dim strMsg              As String
    
    'Change accordingly
    Const strColToCheck     As String = "A1" 'Is the starting cell of the column which contains account numbers
    Const lngColToMatch     As String = 3 'Is the column no in which i need to check 0 value for the same row account number.
    
    With ActiveSheet
        strShtName = .Name
        Set rngToCheck = .Range(strColToCheck)
        If .Cells(.Rows.Count, rngToCheck.Column).End(xlUp).Row >= rngToCheck.Row Then
            Set rngToCheck = rngToCheck.Resize(.Cells(.Rows.Count, rngToCheck.Column).End(xlUp).Row).Resize(, lngColToMatch)
            varData = rngToCheck.Value
            strMsg = vbNullString
            For lngLoop = LBound(varData) To UBound(varData)
                If LenB(Trim(varData(lngLoop, 1))) > 0 Then
                    If LenB(Trim(varData(lngLoop, lngColToMatch))) = 0 Then
                        strMsg = strMsg & IIf(LenB(Trim(strMsg)) > 0, vbLf, vbNullString) & strShtName & " " & rngToCheck.Resize(1, 1).Offset(lngLoop - 1, lngColToMatch - 1).Address(0, 0)
                    End If
                End If
            Next lngLoop
        End If
        If LenB(Trim(strMsg)) > 0 Then
            MsgBox strMsg
        End If
        
    End With
    
    


End Sub

[Flupsie]

Thanks for the reply

Macro not doing anything when activated. I have attached sample data as well as my Macro which I cant get to work as well as your macro

It would be appreciated if you would amend your code. I only want to macro to check if Col C has a 0 (zero) where it pertains to 635X and 734X

https://www.dropbox.com/s/p5f94vuiuu...20Template.xls

[bakerman]

Amended your code.

Sub Variance_635X()

Dim ws As Worksheet, r As Range, msg As String, ff As String
For Each ws In Sheets
    Set r = ws.Columns(1).Find("635X", , xlValues, xlWhole)
    If Not r Is Nothing Then
        ff = r.Address
        Do
           If Abs(Val(CStr(r.Offset(0, 2).Value))) = 0 Then
              msg = msg & ws.Name & r.Address(0, 0) & ", "
            End If
            Set r = ws.Columns(1).FindNext(r)
        Loop Until ff = r.Address
    End If
Next
MsgBox IIf(Len(msg) > 0, Left(msg, Len(msg) - 2), "Zero Variances Found")
End Sub

[Flupsie]

Thanks Bakerman for amending the code. The macro works perfectly

[snb]

Sub M_snb()
   For Each sh In Sheets
    with sh.columns(1).resize(,3)
       .autofilter 1,"635X"
       .autofilter 3, 0
       for each cl in .columns(1).specialcells(12)
         if cl.row >1 then c00=c00 & vblf & sh.name & "!" & cl.address
       next
       .autofilter
    end with
  next

  if c00<>"" then MsgBox c00
End Sub

[Flupsie]

Thanks for the help, much appreciated