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

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;

Cells
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.

Cheers,
Albert

Hello Albert,

I am not sure what you mean by "If not then macro stops.", but this should detect the situation that you described.

Sub MainMacro()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim rng As Range, rCell As Range

Set rng = ws.Range("A10:A45")

For Each rCell In rng
    If Not IsEmpty(rCell) Then
        If IsEmpty(rCell.Offset(0, 1)) Or Not IsNumeric(rCell.Offset(0, 1)) Then
            MsgBox ("You must enter a numeric value in " & rCell.Offset(0, 1).Address)
            Exit Sub
        End If
    End If
Next rCell

End Sub

Sorry. I meat that if for example A10 had text and B10 was empty then stop running macro or exit macro.

Mate, your code works great but when error is displayed it refers to cell as $B$10 with dollar signs.

Is it possible to have it without? I am not to worried about this though.

Another thing can we set focus to the empty cell after clicking OK button?

How about and inputBox asking for the value of the empty cell with a cancel and ok button?

Many thanks for your time and you code.

Cheers,
Albert

From stnkynts' code, change to

MsgBox ("You must enter a numeric value in " & rCell.Offset(0, 1).Address(False, False))

Just noticed that code above works yet if I type 0 in column B it accepts it as OK.

Numeric value must be greater than zero line 0.001.

Jason your code removed successfully the dollar signs.

Looks more tidy. Thank you mate.

Then to select the cell, right before Exit Sub,

rCell.Select

Also, for the input box you could use

Sub InputBox()
MsgBox (Application.InputBox("Enter String"))
End Sub

Hi Jason.

The rCell.Select is targeting column A.

For example if column B11 is the one that needs a numeric value then this cell should be the selected one.

On the input box the data I type in it is not being placed or transfered to the empty cell prior refered by the error.

For example, A10 had text and B10 was empty. Macro flagged cell B10 as "You must enter a numeric value in", so after that I got the input box and type a number and this was not sent to cell B10.

Many thanks for the help.

Cheers
Albert

Thinking back to your original request, I made some changes.
It now checks that the value you entered was numeric as well

The input data was just an example for you to take a implement. This should do it though
As for the selection, I thought you meant A. Sorry.
Try this instead:

For Each rCell In rng
    If Not IsEmpty(rCell) Then
        If IsEmpty(rCell.Offset(0, 1)) Or Not IsNumeric(rCell.Offset(0, 1)) Then
            MyNum = Application.InputBox("Enter Number")
            If MyNum = False Then
                MsgBox ("You must enter a numeric value in " & rCell.Offset(0, 1).Address)
                rCell.Offset(0, 1).Select
                Exit Sub
            Else
                If IsNumeric(MyNum) = False Then
                    MsgBox ("You must enter a numeric value in " & rCell.Offset(0, 1).Address)
                    rCell.Offset(0, 1).Select
                Else
                    rCell.Offset(0, 1).Value = MyNum
                End If
            End If
        End If
    End If
Next rCell

AHHH sorry and one other thing.
About the 0's.
To a computer, a 0 and a Null is not the same thing.
Change the very first if statement to

If not IsEmpty(rCell) and rCell.Value <> 0 then

Again, apologizes for the mulitple posts

Almost there. Thank you.

Mate you mentioned me to implement the InputBox, if only I knew how I would be jumping of joy really.

Some times I tweak the code to work for me and only very rarely it works with the debug coming up and then another yellow line of code to deal with.

The way you have now put this, if cell in column B is empty to which there is data in column A, the input box only asks for a number now without reference to the cell.

Could we have the cell still being mentioned or even better the data in cell A displayed to which cell B is empty and therefore causing the error?

Another thing is the numeric value is still being passed as correct if cell has got 0 (zero) in it.

But I can always take care of this with conditional format and not allow for 0.

Thank you mate for your input.

Cheers,
Albert

Actually I have just thought if it wouldn't be easier and simpler to have the macro just checking range in A and for the same amount of cells with data, the same amount of cells in column B should have a numeric value bigger than 0.

By bigger I mean 0.001 is bigger as there are some products that can go as far as 0.050Kg.

If the number of cells in column B is not equal to the number of cells in A then return error such "You have selected items with zero quantity. Please check your invoice." set focus to the very first empty cell and that is it. I would be happy with this.

I believe this would make it much simpler no?

The only thing is it must see identical cell in line to each other. Example considering that there is an invoice of only 1 item;

A10=butter basted

B11=1

Now this is the same number of cells with data right? There is one cell in A and one cell in B. But the macro must see this as wrong as for A10 there should be a numeric value for B10.

I hope I am not making this way confusing and if this last approach of mine makes it any easier I would go for it.

Many thanks.

Cheers.
Albert

Works wonderfully mate.

I have taken a look at the code and can't even see where does the macro see the value higher that 0.

I was expecting something like >0 somewhere along any line of code.

As you have managed this and I can't really ask for more as it is working as wanted to, how would you go about referring to the content of the cell in A when displaying the inputBox?

I mean right now it indicates which cell needs a value but instead of this for example;

A10= bag of potatoes B10=empty no value here.

inputBox and msgBox display both B10 as reference.

How would you code this to display Enter number for bag of potatoes. and you must enter a quantity for a bag of potatoes.

Sorry to be a pain.

But I just think this so intriguing man.

Cheers.
Albert

Change to
MyNum = Application.InputBox("Enter Quantity for " & rCell.Value)

Also, right now, if you enter a non number in the Input Box, like a letter, then you get the message box but it doesnt stop the macro.
If you want the Macro to stop, then you need an "Exit Sub":

                If IsNumeric(MyNum) = False Then
                    MsgBox ("You must enter a numeric value in " & rCell.Offset(0, 1).Address(False, False))
                    rCell.Offset(0, 1).Select
                    Exit Sub
                Else

Cells in column B if I type in 0 the macro won't stop at this.

I have changed the first line as you have mentioned before but macro is still overlooking the 0.

Sorry to be a pain mate and much much appreciated.

Cheers.
Albert

You will be able to do it soon. Just keep plugging away the way you are now.
Its how I learned too.

Cell Address can be fixed. Notice how I added the & .Address(False,False) to the InputBox and now it shows.
Just like the Msgboxes which show the address.

As for the 0. That was my fault.
It checks for Column A being 0.


Try this code:

For Each rCell In rng
    If Not IsEmpty(rCell) Then
        If IsEmpty(rCell.Offset(0, 1)) Or Not IsNumeric(rCell.Offset(0, 1)) Or rCell.Offset(0, 1) = 0 Then
            MyNum = Application.InputBox("Enter Number for Cell " & rCell.Offset(0, 1).Address(False, False))
            If MyNum = False Then
                MsgBox ("You must enter a numeric value in " & rCell.Offset(0, 1).Address(False, False))
                rCell.Offset(0, 1).Select
                Exit Sub
            Else
                If IsNumeric(MyNum) = False Then
                    MsgBox ("You must enter a numeric value in " & rCell.Offset(0, 1).Address(False, False))
                    rCell.Offset(0, 1).Select
                Else
                    rCell.Offset(0, 1).Value = MyNum
                End If
            End If
        End If
    End If
Next rCell

I see what you mean but I think the last for loop should do the trick.
If it doesnt, we can try to think of something there.