Printable View
I want to do two things that I can't think of how to do.
1) I want to find the first cell with something in it. I know how to find the last cell but no idea how to find the first cell.
2) I also want to check if the current cell is pivot table or not?
Djblois,
Give the following a try, I've put the methods into functions so you can see what needs to be done:[vba]Sub UseThemBoth()
MsgBox IsInPivotTable(Range("A1"))
MsgBox IsInPivotTable(FirstCell)
End Sub
Function IsInPivotTable(ByVal RG As Range) As Boolean
Dim PT As PivotTable
On Error Resume Next
Set PT = RG.PivotTable
On Error GoTo 0
IsInPivotTable = Not PT Is Nothing
End Function
Function FirstCell(Optional WS As Worksheet) As Range
Dim FC As Long, FR As Long
If WS Is Nothing Then Set WS = ActiveSheet
On Error Resume Next
FC = WS.Cells.Find("*", WS.Cells(WS.Rows.Count, WS.Columns.Count), xlValues, _
xlWhole, xlByColumns, xlNext).Column
FR = WS.Cells.Find("*", WS.Cells(WS.Rows.Count, WS.Columns.Count), xlValues, _
xlWhole, xlByRows, xlNext).Row
If FC = 0 Then
'Nothing on the sheet, set it to A1
Set FirstCell = WS.Range("A1")
Else
Set FirstCell = WS.Cells(FR, FC)
End If
End Function[/vba]Matt
I can't get it to work. I want it to pop up a custom form if it is a pivottable.
If you have that IsInPivotTable function in your project, you could do something like:[vba] If IsInPivotTable(ActiveCell) Then UserForm1.Show
'or
If IsInPivotTable(Workbooks("book.xls").Sheets("sheet").Range("A3")) Then UserForm2.Show[/vba]If you still can't get it to work, can you post what you're trying to do exactly that isn't working?
I have created an option on the toolbar of my Add-in to sort row with a few more features than what is offered with Excel's built in sort. Since the sort Dialog does not work on Pivot Tables I have created an error screen to tell my users why the feature would not work. (One thing microsoft is terrible at is error screens) Here is my code:
[VBA]If IsInPivotTable(ActiveCell) Then
Error005.Show
End
End If[/VBA]
and the rest is the functions you supplied me
Hmmmm.. it worked in all my tests, unless the pivot table was copied/paste-special-values over itself, but you could sort that anyways.
Try putting a breakpoint on that line (F9), or put the word "Stop" in the line directly above that If block, then F8 through it to see what is shown. You haven't said there is an error or anything so I'm guessing it is returning false, maybe stepping through is all you can do, aside from sharing the whole add-in
I'm going to attach a workbook I just made for this, showing a basic pivot table, using the IsInPivotTable function as a UDF, as well as from VBA (theres a button on the pivot sheet that just has "MsgBox IsInPivotTable(ActiveCell)" .. I'm curious to see if even that works for you
By the way, I've probably asked you this before, but where in NY are you located?
Matt
I live in Staten Island but I work in New Jersey and go to school in Long Island (Hempstead).
Cool.. im on the far other side of the state (Rochester), not even close :)
Off Topic: do you know anyone at Pae Tec (telecom company)? Stan [born in Watertown, so maybe we can swap some snow stories:beerchug: ]Quote:
Originally Posted by mvidas
I used to be in the ARmy National Guard and had to go to Watertown a few times a year.
what great is watertown got like 10' of snow over a weekend a few weeks ago, and it is still coming down somewhat regularly. makes me jealous
and Stan, I dont know anyone at paetec but I'm very aware of that company. not only did they buy the naming rights to our soccer team's new stadium, they're a huge competitor. I work at the major telecom company in the area :)
Let me guess - Global Crossing? I worked for BTI for 13 years, then they merged with ITC, my boss went to Paetec... Now they are merging with US Lec so he may be headed back down to the Carolinas. StanQuote:
Originally Posted by mvidas
I did technically work for GX until they sold us in 2001, I work for Frontier.
I drove through NC last week going to/from SC, first on 95 (stopping in Clayton to go to JRs and get some bojangles), then on 77 on the way back (stopping in jonesville to sleep). i have thought of moving there, but i think the summers would be too hot/humid for me
djblois - did you ever get the first cell / check if pivot table thing working as you needed it?