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/p5f94vuiuu5xxyc/Zero%20Variance%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