Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 20th, 2007, 09:25 AM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default varying data range

Hi,

I have a workbook with 1 sheet that contains data and 7 sheets that have pivot tables building off of the data from the first sheet. This data gets imported in daily and varies in range. I use workbook.refreshall to run the fresh data through the pivot tables.

How can I set up the pivot tables to get all the data from the 1st sheet regardless of what the range is?

Thanks,
David

 
Old July 22nd, 2007, 07:58 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use a dynamic range name for the pivot table data range. The general formula is;
=OFFSET(SheetName!$A:$1,0,0,COUNTA(SheetName!$A:$A ),COUNTA(SheetName!$1:$1))
A1 is the first cell of the data range.
 
Old July 24th, 2007, 01:55 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks FullSquat! that worked great.
Now, One more thing I'm having trouble with. I have a couple of formulas that go along with the data I'm bringing in. They are in columns beside the data. I need for the formulas to "drag down" with the rows of data...i.e. J3x4,J4x4,J5x4,J6x4.....The formula is the same it just changes w/ the row number. How can I do this?

thanks,
David

 
Old July 25th, 2007, 01:16 AM
Registered User
 
Join Date: Jul 2007
Location: winchester, calafornia, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I dont understand whats wrong.
i did f1*4 and i dragged it down and it workes fine.
= f2*4 f5*4 and so on...

 
Old July 25th, 2007, 12:41 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I want it to be automated w/ vba, so when new data is imported in the formula it will drag down with the new data and stop at the last row of data.
 
Old July 25th, 2007, 12:48 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think I've almost got it working like I want.
I put this code in a module:
Code:
Function LastCell(ws As Worksheet) As Range
  Dim LastRow&, LastCol%
  On Error Resume Next

  With ws
    LastRow& = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
    LastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
  End With
  Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function
Then I call that function from the macro I created, like so:
Code:
Dim sLast As String
sLast = LastCell(Sheet1).Row

    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-16]=""0247"",""W"",LEFT(RC[ 18],1))"
    Selection.AutoFill Destination:=Range("X2:X" + sLast), Type:=xlFillDefault
    Range("X2:X" + sLast).Select
    ActiveWindow.ScrollRow = 2

The only thing now is that sLast is getting a value of 11 every time and I haven't been able to figure out why yet. There are about 8,000 rows of data, so 11 is a tad off.

 
Old July 27th, 2007, 02:30 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is a suggestion. Select a column with continuous data and use VBA code similar to the following:

    Dim WS As Worksheet
    Dim Rcell As Range

    Set WS = Sheets("SheetName")
    Set Rcell = WS.Range("A2") 'First cell of data


    Do Until IsEmpty(Rcell.Value) 'Stops at last cell in the column

        'Enter Formula in offset columns

        Rcell.Offset(0, 3).FormulaR1C1 = "=45*RC[-2]"

        'Moves Rcell to the next row

   Set Rcell = Rcell.Offset(1, 0)

    Loop


The program will enter the formula in the column offset to the right as specified in the Offset method, move down the column to the next row and stop at the last row in the column.







Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Access data whith varying columns dbellavi SQL Server DTS 15 January 30th, 2008 06:23 AM
Counting Number of Rows Between Data Range eusanpe Excel VBA 6 September 21st, 2006 07:17 AM
Display data within data range? ktsis ASP.NET 1.0 and 1.1 Basics 2 June 9th, 2006 03:06 PM
Return Data from Excel 97 Range into table interrupt Javascript How-To 3 March 4th, 2005 07:14 AM
External Data Range Properties ioates Excel VBA 1 December 3rd, 2004 08:03 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.