Fixing Rows...Help please..

[jhong]

Hi There!

I have a report that sum up all values from all sheets within a
workbook, the formula is like these ='Sheet1:Shee2'!A9, i copied it
up to column Z to summarize all the values.

My question, can I create a macro that can check if all my data's from
all sheets starts in row9 excluding the column heading which starts in
row8?


Thanks in Advance! Have a nice day!


Jerome

 

[Keith R]

Low tech solution: in an empty cell, multipy the value of A9 on each sheet.
If any are blank, Excel will treat it as a zero and the formula result will
be zero. If one of those cells contains text, it will return #VALUE.

However, that will only work if your list never starts with a value of zero;
if zero is a valid entry, you will need a more complex formula, or of
course, VBA. I'm not sure what your tests are, so I left that to you below.

Aircode:

Dim Sht as Sheet
For Each Sht in ActiveWorkbook
TempValue = Sht.Range("A9").value
**test for non-numeric values**
Next

Of course, this is focused on ensuring that your data starts /by/ row 9. If
you are worried that your data might start /earlier/ than row 9 (report
header issues, etc.) then I'd also test A8 in the same loop to make sure it
was what you expect (text header, or blank)

HTH,
Keith