VBA Error/Trapping/Identification

[Nickerzzzzz]

Hi All,

Hoping someone can help me with a bit of code...

The below is an excerpt of someone's code who has unfortunately left the company, this code is for some reason entering the error prevention part of the procedure and giving the Msg "One of your Stock data columns has a blank heading.", "Please fix and re-run!.", "Column Heading Missing!"

I have checked the Stock Data Column Headings and cannot see any Blanks, so not quite sure what's going on!

My aim (with my limited knowledge of vba) is to try and identify the Cell or Cells which it thinks is Blank by Selecting it from within the error prevention part of the procedure before the Msg box pops up.

Would you have any ideas as to a piece of code that I can insert to try and troubleshoot this procedure?

Thanks In Anticipation :)

*************************************************************************** ******************************

Sub UpdatePivots()
'========================================================================== ================
'========================================================================== ================
'====================================== Stock Pivot
Application.ScreenUpdating = False
Application.Cursor = xlWait

RAG = Application.ActiveWorkbook.Name

'Having opened "RAG" already, select the workbook
Application.Workbooks(RAG).Activate

'--------------------------------------------------------
'Clear the reference table
Worksheets("Stock Pivot").Select
Range("L4:AU4").Select
Range(Selection, Selection.End(xlDown)).Clear

'Set Variables Equal to Data Sheet and Pivot Sheet
Set Data_sht = ActiveWorkbook.Worksheets("Stock")
Set Pivot_sht = ActiveWorkbook.Worksheets("Stock Pivot")

'Enter in Pivot Table Name
PivotName = "PivotTable3"

'Dynamically Retrieve Range Address of Data
Set StartPoint = Data_sht.Range("A2")
Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

NewRange = Data_sht.Name & "!" & _
DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then

'Try to Identify the Blank Cell Start
'DataRange.Rows(1).Select
' Try to Identify the Blank Cell Finish

MsgBox "One of your Stock data columns has a blank heading." & vbNewLine _
& "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
Exit Sub
End If

[Paul_Hossler]

Could be lots of things.

Can you post a small sanitized sample workbook?

Click [Go Advanced] and use the 'paperclip' icon to upload a workbook

[Nickerzzzzz]

Could be lots of things.

Can you post a small sanitized sample workbook?

Click [Go Advanced] and use the 'paperclip' icon to upload a workbook

Hi Paul,

Thanks for your reply, I would have to try and re-create at home, when I'm back at work on Monday I'll take another look and see if I can create a sample at home.

I was hoping there was a way to show what Cell the below piece of code is looking at so I could correct or if there's another piece of code I could use to identify the "Blank" that is setting the condition?.

If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0

Cheers


Nick

[p45cal]

In the first instance I'd add the line:
Application.goto DataRange.Rows(1)
to check that it's looking for blanks in the right range.
You've already done similar but commented it out.
Once it's selected, put a formula in a spare cell temporarily like:
=Countblank(here put the range of cells thsat was selected earlier by the code)
What's the result? If it's not 0 and it's not obvious there's a blank cell then check the cell formats to check there's nothing in there which might still put something in the cell even though it's actually empty.
If that doesn't lead anywhere, try changing the range in that temporary cell's formula to a smaller range until you narrow down which cell(s) might be the problem.

Another thing you can do is put a line:
zxzx=DataRange.Rows(1).value
and while stepping through the code examine zxzx in the Locals pane.

edit post posting:
you could also try:
If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
For Each cll In DataRange.Rows(1).Cells
If WorksheetFunction.CountBlank(cll) > 0 Then
cll.Select
Stop
End If
Next cll
which should stop and select the cell which it thinks is blank.

[snb]

Avoid any 'select' and 'Activate' in Excel.
Use With ... End With instead of object variables.

[SamT]

Try

'Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

'NewRange = Data_sht.Name & "!" & _
'DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
If StartPoint.End(xlToRight).Column <> _
StartPoint.CurrentRegion.Columns(StartPoint.CurrentRegion(Columns.Count)).C olumn Then
MsgBox"Missing HEader is " & StartPoint.End(xlToRight).Offset(,1).Address
Exit Sub
End if

Set DataRange = Data_sht.Range(StartPoint, StartPoint.End(xlToRight))
NewRange = Data_sht.Name & "!" & _
DataRange.Address(ReferenceStyle:=xlR1C1)

nb: THat doesn't check if A2 is empty.

[Nickerzzzzz]

In the first instance I'd add the line:
Application.goto DataRange.Rows(1)

That line of code was exactly what I needed!, Many Thanks, and I'm sure the other bit of code will be very useful too!, many thanks again.

The line of code I originally entered didn't seem to work, hence why I commented it out.

On using the line of code that you supplied it revealed the selected cells which is just what I was looking for, and for some reason it was selecting 3 columns right at the end of the selection which I believe have no headings or data in any of the cells.

I only had a few minutes to look at this today so at some point I now need to try and find out what controls the final 3 Columns/Headings selection, I'm presuming its ".SpecialCells(xlLastCell)" in the line of code below, although I don't understand what makes it go to a specific Column/Heading, as I think the last 3 Columns in the selection have no headings or data in any cell, what's a SpecialCell, a cell that contains data ?

Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

Would this be a fair interpretation of the Pseudocode for the above?...
Set The Variable "DataRange" = To The "Stock" Worksheet and Cells A2:Any Cell That Contains Data

Any further advise or direction would be much appreciated :)

Many Thanks

[Nickerzzzzz]

Try

'Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

'NewRange = Data_sht.Name & "!" & _
'DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
If StartPoint.End(xlToRight).Column <> _
StartPoint.CurrentRegion.Columns(StartPoint.CurrentRegion(Columns.Count)).C olumn Then
MsgBox"Missing HEader is " & StartPoint.End(xlToRight).Offset(,1).Address
Exit Sub
End if

Set DataRange = Data_sht.Range(StartPoint, StartPoint.End(xlToRight))
NewRange = Data_sht.Name & "!" & _
DataRange.Address(ReferenceStyle:=xlR1C1)

nb: THat doesn't check if A2 is empty.

Many Thanks, another option should I need it, much appreciated.

[SamT]

Of my two blocks of code, the second will set the DataRange correctly IF there are no empty header cells

Set DataRange = Data_sht.Range(StartPoint, StartPoint.End(xlToRight))

The first block of code checks that all header Row cells at the top of the contiguous block of cells, (CurrentRegion,) of which StartPoint is part of are not empty.

If StartPoint.End(xlToRight).Column <> _
StartPoint.CurrentRegion.Columns(StartPoint.CurrentRegion(Columns.Count)).C olumn Then



CurrentRegion can be very useful.

Add some values to A1, B2, C3, and D4. The CurrentRegions for all four of those cells is Range("A1:D4"). They are the same for any cell touching one of those four cells.ie A3 touches B2 corner to corner. A3's CurrentRegion is also Range("A1:D4"). Note that D5, E5, and E4 all touch D4, but their CurrentRegions are different. A 4x5 block, a 5x5 block and a 5x4 block.

Cells A4.Current Region is Range("A4") and for D1 it's Range("D1"), because they are not touching one of those four value'ed cells.

Assume you have two separate blocks of cells, for example A1:D25 and F1:K5. The CurrentRegion for any cell inside either block will be as expected. But the CurrentRegions for the Cells in F1:F6 will be a rectangle that includes A1:K25.

Yes, all CurrentRegions are rectangles.

[SamT]

I want to rewrite that second block so that it also checks the StartPoint Cell

If WorksheetFunction.CountA(Data_sht.Range(StartPoint, StartPoint.End(xlToRight)) <> _
Data_sht.StartPoint.CurrentRegion.Columns.Count then

[p45cal]

what's a SpecialCell, a cell that contains data ?

Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

Would this be a fair interpretation of the Pseudocode for the above?...
Set The Variable "DataRange" = To The "Stock" Worksheet and Cells A2:Any Cell That Contains Data
SpecialCells is much the same as what you can get when on a sheet, you press F5 on the keyboard to get the Go to dialogue box, and then click Special…
Cick the question mark in the title bar of said dialogue box goes into good detail abpout the various options, and if you were to record a macro of you doing this sort of stuff the resultant code will show you the type of thing you can do.

SamT's suggestions are good, but to be robust we'd need to have a good idea of the possiblitities of the layout of the data on the sheet; are there going to be completely blank rows within the table, are you in fact only interested in part of the table etc. etc.
Attaching such a sheet here would be a good start.

[Nickerzzzzz]

Hi All,

Unfortunately I have not been able to look at this to much this week due to other commitments but please rest assured I shall re-visit this very soon so your efforts to help will not be wasted, many thanks for what you have provided so far its been of great help.

ATB and I'll be Back :)