VBA errors with Dim

[jae113]

Hi All,

I'm very new to VBA and was trying to combine a macro that I had recorded with a macro that someone gave me. Basically my hope was to have the last row and last column with data selected for both sorting and pivots. I keep getting errors even if I move the statements.

Right now it doesn't like the finalRow variable. I moved the

Set WSD = Worksheets("Data") & Set PTOutput = Worksheets("By SGD & Vendor")
​

down also, but I don't think that was right.


Any and all help very much appreciated!

Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Dim PTOutput As Worksheet
Dim PTCache As PivotCache
Dim PRange As Range

Dim finalRow As Long
Set finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column

Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SamplePivot")

Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A[]"
.RightHeader = ""
.LeftFooter = "&F[]"
.CenterFooter = "&P of &N[]"
.RightFooter = "&D &T[]"
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "Approver"
Range("A1").Select
Columns("AO:AO").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ActiveSheet.Select
ActiveSheet.Name = "Data"
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("finalCol:FinalRow"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("finalCol: FinalRow")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With
Range("AT1").Select
ActiveCell.FormulaR1C1 = "Image"
Range("AT2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"
Range("AT2").Select
Selection.AutoFill Destination:=Range("FinalCol:FinalRow"), Type:=xlFillDefault
Range("FinalCol:FinalRow").Select

Cells.Select
Cells.EntireColumn.AutoFit
Set WSD = Worksheets("Data")

Set PTOutput = Worksheets("By SGD & Vendor")
pt.ManualUpdate = True

pt.AddFields RowFields:=Array( _
"SOURCING_GROUP_DESC", "VENDOR_NAME")

With pt.PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "_($* #,##_);_($* (#,##0);_($* ""-""_);_(@_)"
End With
pt.ManualUpdate = False
Cells.Select
Cells.EntireColumn.AutoFit

End Sub

 

[RoryA]

You need those two lines at the start of the routine since you currently try to use the variables before you set them.

 

[jae113]

Thanks! I didn't think that could be right.

Buy, why doesn't it like

Dim finalRow As Long
Set finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

 

[jae113]

What I was trying to do is to set a range, or at least automatically select all the cells in a table with data. I don't really know how to use FinalRow or FinalCol.

I could really use help - thanks!

 

[RoryA]

FinalRow is a number so it's just:

Dim finalRow As Long
 finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

since you only use Set with Object variables.

 

[jae113]

Ahhhh, that makes sense! Ok now it doesn't like this part at the bottom:

Sub Monthly_Print_Process()

' ' ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("FinalRow:FinalCol"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("FinalRow:FinalCol")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AT1").Select
ActiveCell.FormulaR1C1 = "Image"
Range("AT2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"
Range("AT2").Select
Selection.AutoFill Destination:=Range("AT2:FinalRow"), Type:=xlFillDefault
Range("AT2:FinalRow").Select

Cells.Select
Cells.EntireColumn.AutoFit

End Sub

​

I guess I can't use finalRow as a variable? How can I have it select the final row in that column?

 

[RoryA]

You would use:

Cells(finalRow, finalCol)

but that would only be one cell. What is the range supposed to be?

 

[jae113]

First I would like it to sort the entire worksheet by column AO. I used this:
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("FinalRow:FinalCol"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("FinalRow:FinalCol")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

But I'm seeing now I don't specify column AO. How do I make it select everything and then sort descending by AO?

Then I want it to select all of the rows in column AT that have data and put in the formula for the hyperlink.

I guess I don't know how to code for anything by column with all rows. Does that make sense?

 

[jae113]

Here is the messy, messy code that I am trying to use to select the entire workbook, and then sort descending by amount of column AO:

' ' ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("FinalRow:FinalCol"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("FinalRow:AO")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

​

Then here I am trying to paste the IF HYPERLINK formula into all the rows in column AT that have data:

Range("AT1").Select
ActiveCell.FormulaR1C1 = "Image"
Range("AT2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"
Range("AT2").Select
Selection.AutoFill Destination:=Range("AT2:FinalRow"), Type:=xlFillDefault
Range("AT2:FinalRow").Select

Cells.Select
Cells.EntireColumn.AutoFit
​

I'm getting a "runtime error 1004 range of object global failed" at the first real line of code in the first section above. I know I'm not defining my range, but I don't know how.

Thanks for all your help!

 

[RoryA]

I think you want:

With WSD.Sort.SortFields
   .Clear
   .Add Key:=WSD.Range("AO1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
   With .Sort
      .SetRange WSD.Range("A1", WSD.Cells(FinalRow, FinalCol))
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
End With

and:

Range("AT1").Value = "Image"
 Range("AT2:AT" & finalRow).FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"

 Cells.EntireColumn.AutoFit