CODEWITHSUNDEEP
excelvbapivot-table
Davinox
Davinox

Reputation: 21

Convert SourceData to Range

When i call SourceData on PivotTable i get an String :

MsgBox  ActiveSheet.PivotTables("PivotTable1").SourceData

The retuen is "'Sheet1'!L18C1:L1471C23"

I would like to tranform this string for a RANGE but did not working !

Sheet = Split(Source, "'")(1)
adress = Split(Source, "'")(2)
adress = Right(adress, Len(adress) - 1)
Cell1 = Split(adress, ":")(0)
Cell2 = Split(adress, ":")(1)
Debug.Print "Sheet : " & Sheet
Debug.Print "Cell1 : " & Cell1
Debug.Print "Cell2 : " & Cell2

Debug.Print Worksheets(Sheet).Range(Cell1).Value

Debug windows

Sheet : Sheet1

Cell1 : L18C1

Cell2 : L1471C23

"Execution error 1004".

Thanks

Upvotes: 2

Views: 145

Answers (1)

sous2817
sous2817

Reputation: 3960

I think your code can be simplified a bit. Also, this based off an English (American) version of VBA. You should be able to modify it for the "L1C1" notion. Last bit, there's probably other ways to solve your problem, this is just how I was able to do it. 

Sub test1()
Dim source As String

Dim pivotData As String
Dim pivotRanage As Range

pivotData = "'Sheet1'!R29C15:R200C24"

Set pivotRange = Range(Application.ConvertFormula(pivotData, xlR1C1, xlA1))

Debug.Print pivotRange.Item(1, 1).Value

End Sub

In a nutshell, it converts your string in to a range. Here is the documentation on the ConvertFormlua function (https://learn.microsoft.com/en-us/office/vba/api/excel.application.convertformula) but in a nutshell, it takes a string and makes it a range. Then, using that range it's simple to get the first item.

Hope that helps!

Upvotes: 1

Related Questions