+ Reply to Thread
Results 1 to 3 of 3

Macro to check balances are zero

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Macro to check balances are zero

    Hi,

    I'm trying to write a process that will check each cell in column F to make sure it is Zero but for some reason it falls over;

    My code is;

    Sub Step_one()
    
    Dim FName As Variant
    Dim wb As Workbook
    Dim lrow As Long, i As Long, j As Long, startrange As Long, endrange As Long
    Dim Sh As Worksheet
    Dim Counter As Long
    Const pwd = "IRSS_Protect"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    With Sheets("sheet1")
        .Unprotect pwd
        For Each cel In .Range("F")
        On Error Resume Next
            If Round(cel.Value, 2) <> 0 Then
                Counter = Counter + 1
            End If
            On Error GoTo 0
        Next cel
        .Protect Password:=pwd, AllowFiltering:=True
    End With
    
    End Sub
    Does anyone why this wouldn't work

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Macro to check balances are zero

    maybe so
    Sub Step_one()
    Dim cel As Range
    Dim Counter As Long
    'Const pwd = "IRSS_Protect"
    With Sheets("sheet1")
        '    On Error Resume Next
        For Each cel In .Range("F1", .Cells(Rows.Count, 6).End(xlUp))
            If Round(cel.Value, 2) <> 0 Then Counter = Counter + 1
        Next cel
        '    On Error GoTo 0
    End With
    MsgBox Counter
    End Sub

  3. #3
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Macro to check balances are zero

    Thank you very much.

    Works very well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 13
    Last Post: 04-18-2013, 12:32 PM
  2. How to sum balances that have same SSN/EIN???
    By jgomez in forum Excel General
    Replies: 2
    Last Post: 01-25-2012, 12:13 PM
  3. Macro to force format and check barcode check digit
    By Code Flunkie in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-02-2009, 10:27 AM
  4. Macro to check for blank cell entry, copy previous value, and check for duplicates
    By xPunxNotDeadx in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-26-2009, 06:33 PM
  5. [SOLVED] formula to check balances within parameters
    By Todd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2006, 08:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1