Check range A10 to A45 and if cell not empty then same line in B must have value RRS feed

  • Question

  • Hello everybody.

    I am new around here and also VBA and macros are not my forte at all, hence why I am here right?

    Anyway, I have done so much research and got so much help from other forums that I can't believe I am where I am and the number of people that are willing to help others.

    Going to my problem.

    I have a range of cells, column A range A10 to A45 and column B range B10 to B45.

    Column A is for item description and column B is for quantity.

    The macro is to check through range A10 to A45 and for each cell that finds with data then same line cell "when I say same line cell I mean if A10 cell has data then B10 must have numeric value" on column B must have a numeric number.

    If not then macro stops.

    I already have the following code but it does not quite dos it yet.

    Dim r As Range
    Const MySheet = "Sheet1"
    Const MyRange = "A10:B45"
    Set r = Worksheets(MySheet).Range(MyRange)
    If Application.WorksheetFunction.Count(r.Columns(2)) < _
    Application.WorksheetFunction.CountA(r.Columns(1)) Then
    MsgBox "Qty missing!", vbInformation
    On Error Resume Next
    Application.Goto r.Columns(2).SpecialCells(4)
    On Error GoTo 0
    Cancel = True
    End If

    The following happened; 

    A10=text B10=1
    A11=text B11=1
    A12="empty cell B12=1
    A13=text B13="empty cell"

    and macro has failed to pick up empty cell B13.

    Any help is as always well and truly appreciated.

    A very nice weekend.


    Friday, March 8, 2013 11:34 PM

All replies

  • Try the following and see if it does what you want. Better to use Worksheet and Range variables than constants. Then when the range is set to a worksheet and range then the range variable stores both the worksheet and range in the one variable so that when the range variable is referenced VBA knows what worksheet it is on.

    I have kept the MsgBox in the code but from a user point of view I would get rid of it. It is a real pain having to click OK before you can enter the data.

    Sub test()
        Dim ws As Worksheet
        Dim rng As Range
        Dim r As Range
        Set ws = Worksheets("Sheet1")
        'Note: Following is One column only _
         Use Offset to address adjacent columns.
        Set rng = ws.Range("A10:A45")
        For Each r In rng
            If r.Value <> "" Then
                If Not WorksheetFunction.IsNumber(r.Offset(0, 1)) Then
                    Application.Goto r.Offset(0, 1)
                    MsgBox "Qty missing!", vbInformation
                    Exit For
                End If
            End If
        Next r

    End Sub

    Regards, OssieMac

    • Edited by OssieMac Saturday, March 9, 2013 12:52 AM Removed commented out code that was used in testing
    Saturday, March 9, 2013 12:51 AM