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

C

cava30

New member
Joined
Mar 8, 2013
Messages
6
Reaction score
0
Points
0
Hi.

I have tested the following macro which works to an extent.

Code: 
[COLOR=#000000][FONT=Verdana]Dim r As Range[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Const MySheet = "Sheet1"
Const MyRange = "A10:B45"[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Set r = Worksheets(MySheet).Range(MyRange)[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]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[/FONT][/COLOR]

However, the following happened; ranges were A10 to B20

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.

I must say that this has been the hardest one to get any sort of info on the internet and I have googled but I am not program minded so I might even be looking for the wrong thing.

What I am trying to do;

I have an invoice (used this when I first started 3 years ago and no macros then) spreadsheet and B (B10 to B45) column is for QT which stands for quantity.

Column A (A10 to A45) is for item description and item is selected by a drop down list.

Columns C and D are for UOM (Unit Of Measure) and Price which are automatically fill with a vlookup formula that looks for the item selected in column A.

The macro or VB code I am after is to do the following;

Range A10 to A45, for every cell with an item or data/text if you like, then the equivalent line in column B must have quantity or in other words a numeric value.

And this is repeated throughout range A10 to A45. I was thinking of macro move on to next step when it finds an empty cell within this range but the problem is that my brother might skip a line in between and then it all goes bad once again.

One nice thing to have in place would be to have the macro popping up an inputBox when it does come across an empty cell in B column for the range in question and asks to input quantity in the cell but if I can get the first bit working as intended then I am happy.

I have tried to persuade my brother to purchase QB2013PRO, which is what I have for my small part time business and works as a treat, but hey he's just starting so I kind of understand him.

As mentioned before, I used this file when I first started and had never used any macros as I haven't needed them and to today I am not a programmer. Only just now with his need to have this as much as automated and safe possible to avoid any human errors, that I started going through macros and must say, I am quite amused at what one can do with it.

My brother's not IT minded as myself and not a keen user of excel hence the need to make this as fool prof possible.

Back in my days I would have to print it to my PDF printer to save an electronic copy and always going back to keep track with numbering. God, this was when I was using freeTAS.

Anyway, I hope I can get over this one which is the last bit of the puzzle and many thanks for all the help everyone here are offering others like myself and for their time as well.

A very nice weekend to all.

Cheers.
Albert
 
I would add conditional formatting to the range A10:B45 with a formula of

=IF(OR(AND(A10="",B10<>""),AND(A10<>"",B10="")

with a highlight colour which will show mismatched rows.
 
Bob Phillips said:
I would add conditional formatting to the range A10:B45 with a formula of

=IF(OR(AND(A10="",B10<>""),AND(A10<>"",B10="")

with a highlight colour which will show mismatched rows.
Click to expand...

The only problem with this is that macro that runs all the codding to save file as pdf and generate new document number would still go ahead and if there is as much as one item cell with data, therefore on item, and no quantity then Invoice/quote would be incorrect hence the need of a macro that will check for this and not stop if conditions are not meat.

Thank you for the input and time.

Cheers,
Albert
 
So get the macro set the conditional formatting and just add a simple calculation to test whether any errors and don't create the PDF if so,
 
And you have just lost me at the very beginning mate, sorry.

I am not macro and excel expert mate.

Wouldn't even know where to start.
 
Code: 
Dim r As RangeConst MySheet = "Sheet1"
Const MyRange = "A10:B45"


    With Worksheets(MySheet)
    
        Set r = .Range(MyRange)
        r.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(AND($A10="""",$B10<>""""),AND($A10<>"""",$B10=""""))"
        r.FormatConditions(1).Interior.ColorIndex = 3
        If .Evaluate("SUMPRODUCT(--((A10:A45="""")+(B10:B45="""")),--(A10:A45<>B10:B45))") > 0 Then
        
            'give a message
        Else
        
            'create the pdf
        End If
    End With
 
You must log in or register to reply here.
Back
Top