keithmcvean
New Member
- Joined
- Dec 20, 2013
- Messages
- 13
I have a bit of a weird one so bear with me.
I have an macro-enabled Excel spreadsheet (.XLSM) created in Excel 2010. On the first sheet (HoldsList) I have data formatted as a table called Holds (see below) with the autofilter turned on. What I'm trying to do is, using VBA, filter the table where Column Q = True.
It should be simple but it's not.
In column X I have a formula calling a function I wrote myself which works just fine that many other formulas in other columns depend upon. The problem is when I filter the data.
If I filter the data by hand (i.e. clicking the down arrow in the column header and removing the values of FALSE from Column Q) everything works fine. The column is filtered and all data and data derived from functions is correct.
If I filter it using VBA however, Column X updates to #VALUE (a value used in the formula is of the wrong data type).
What is even stranger is that if I make no changes to the data but just click in one of the cells in column x or y and press enter, everything resolves again as it should.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Q[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]TRUE[/TD]
[TD]=VBAFUNCTION(Column Y)[/TD]
[TD]Column Y Data[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD]=VBAFUNCTION(Column Y)[/TD]
[TD]Column Y Data[/TD]
[/TR]
</tbody>[/TABLE]
I'm pretty sure the problem isn't the filtering code (see below).
Filtering Subroutine:
Dim FilterColumn As Integer
FilterColumn = [MATCH("Available", Holds[#Headers], FALSE)]
If Sheets("HoldsList").AutoFilter.FilterMode = False Then
Range("Holds").AutoFilter Field:=FilterColumn, Criteria1:=True
Else
ActiveSheet.ShowAllData
End If
Which leaves the VBAFunction itself. The function itself is long and complicated, but in a nutshell, it takes the data from Column Y, manipulates the text, and returns the result. That function is defined as a string, as is every variable in it, as is all the functions it in turn calls.
I've tried turning on and off the calculations, screen updates, etc. but to no avail.
Any ideas?
I have an macro-enabled Excel spreadsheet (.XLSM) created in Excel 2010. On the first sheet (HoldsList) I have data formatted as a table called Holds (see below) with the autofilter turned on. What I'm trying to do is, using VBA, filter the table where Column Q = True.
It should be simple but it's not.
In column X I have a formula calling a function I wrote myself which works just fine that many other formulas in other columns depend upon. The problem is when I filter the data.
If I filter the data by hand (i.e. clicking the down arrow in the column header and removing the values of FALSE from Column Q) everything works fine. The column is filtered and all data and data derived from functions is correct.
If I filter it using VBA however, Column X updates to #VALUE (a value used in the formula is of the wrong data type).
What is even stranger is that if I make no changes to the data but just click in one of the cells in column x or y and press enter, everything resolves again as it should.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Q[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]TRUE[/TD]
[TD]=VBAFUNCTION(Column Y)[/TD]
[TD]Column Y Data[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD]=VBAFUNCTION(Column Y)[/TD]
[TD]Column Y Data[/TD]
[/TR]
</tbody>[/TABLE]
I'm pretty sure the problem isn't the filtering code (see below).
Filtering Subroutine:
Dim FilterColumn As Integer
FilterColumn = [MATCH("Available", Holds[#Headers], FALSE)]
If Sheets("HoldsList").AutoFilter.FilterMode = False Then
Range("Holds").AutoFilter Field:=FilterColumn, Criteria1:=True
Else
ActiveSheet.ShowAllData
End If
Which leaves the VBAFunction itself. The function itself is long and complicated, but in a nutshell, it takes the data from Column Y, manipulates the text, and returns the result. That function is defined as a string, as is every variable in it, as is all the functions it in turn calls.
I've tried turning on and off the calculations, screen updates, etc. but to no avail.
Any ideas?
