Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2005
    Location
    Australia
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Having trouble with variable string (Excel/XP)

    Hi Guys,

    Im having some problem putting a variable into some code that creates a pivot table, the variable is the last row number. The code is below, I have tried a few different approaches but have not had any luck.

    Dim row, cell
    Dim ref
    Range("a1").Select
    Selection.End(xlDown).Select
    cell = ActiveCell.Address
    row = Right(cell, 4)
    ref = "R1C1:R" & row & "C20"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    "'CBI Despatches'! ref ").CreatePivotTable TableDestination:="", _ (This line contains the ref string which I want to be variable.)
    TableName:="PivotTable5", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Stock # ")
    .Orientation = xlRowField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable5").PivotFields("Despatch Qty"), "Count of Despatch Qty", xlCount
    Range("B13").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields ("Count of Despatch Qty"). _
    Function = xlSum

    Hope this makes sense......

    Cheers
    kd

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Having trouble with variable string (Excel/XP)

    There are several problems with the approach you are using, not the least of which is using Row for a variable name since it is also an Excel Property name. If you are trying to find the row number of the last used cell in column A, then you can do something like this:

    <pre>Dim lLastRow As Long
    lLastRow = Range("A65536").End(xlUp).Row
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    May 2005
    Location
    Australia
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Having trouble with variable string (Excel/XP)

    Thanks for your help Legare, but identifying the row value was not my problem. I am having problems createing a macro that will create a pivot table from a data source where the number of rows in the data can change each time the macro is run.

    Cheers,
    kd

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Having trouble with variable string (Excel/XP)

    You will have a problem if you have an empty cell in Column A in the middle of your data. It would help you to study Legare's answer, because it looks like what he returns as 'lLastRow' is what you want for your variable 'row' (which as he points out is a bad choice of name for an Excel variable). Try something like (untested):

    Dim myrow as Long
    Dim ref as Range

    myrow = Range("A65536").End(xlUp).Row
    ref = Worksheets("'CBI Despatches").Range("A1:T" & myrow)
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    ref).CreatePivotTable TableDestination:="", _
    ... etc.

    See also advice in this Forum and the Excel Forum on the value of using "Option Explicit" and on Typing declared variables. (And check that "CBI Despatches" is the correct spelling of the Worksheet.)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •