catching malformed data before sub runs

[mperrah]

Hello,

I have several subs that run from choosing an item from a custom add-in menu.
for the most part it works great, but if the data pulled in from another sheet is missing portions, the output is fumbled.

there are 2 sheets I copy data from into my workbook on 2 separate sheets from other workbooks then they are closed leaving only mine open and active.

column C of both newly populated sheets are compared then portions are combined and copied to a third sheet

the data has a main header row, A1:M1
then and a header row for each order.A2:M2
each order has a blank row then a product header B4:H4
followed by the products B5:H5
then multiple rows depending on the number of products in the order
then a blank row
then the next order header A7:M7
and so on.

>> my trouble
in this example, if b5 is empty, my code outputs the rest of the output data in wrong places.

somehow I need to check the input for the values in the B column to follow a pattern
row1 is the Main Header - will always be ture
row2 is an order header - will always be true
row3 is blank - will always be true
row4 should be the first product header starting in column B - if something is in A -msgbox "data missing" vbok, end sub
row5 should be the first product item - if this is blank - msgbox "data missing" vbok, end sub

this not too hard to code, but when the number of cells in between each column A header varies, not sure how to scan that efficiently
there should be at least 4 blank rows (after row2) between each filled cell in column A

and if there is something in column B but not A, there should be at least 2 cells in a row down with something in the cell

here is a picture of the data
13195
thank you in advance for any ideas/solutions.
-mark

[jonh]

Make the headers named ranges and get the number or rows between them?

[mperrah]

The sheets with the headers a output twice daily and are different every time. The come from a non excel program. So having them named I'm not sure is doable.
Maybe something like a double loop with a counter to run on the data before I copy it over
ill scan down column a, when a filled cell is encountered start a second loop that counts blank cells.
if less then 4 end the sub. I'll try working on this today. Thanks for jogging my brain

[mperrah]

This seems to be working, I'm going to try to input other odd data to setup error catching.
thanks for humoring me.

Sub checkFormat()
Dim i, x, cnt As Integer
Dim lr, lr2 As Range
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks(1)
Set ws = wb.Worksheets(1)
cnt = 0
lr = ws.Cells(Rows.Count, "a").End(xlUp).Row
For i = 3 To lr
If Cells(i, 1).Value <> "" Then
For x = 1 To 4
If Cells(i + x, 1).Value <> "" Then
MsgBox ("One or more Orders do not have products listed The macro will close now"), vbOKOnly, ("Data Format Check")
Exit Sub
End If
Next x
End If
Next i
' MsgBox ("Data format is correct"), vbOKOnly, ("Data Format Check")
End Sub

[mperrah]

I changed exit sub on error to end.
that haults the entire routine, which was the effect I was looking for