Convert Long number to Integer:

[pedie]

Hi, not sure how to post this but...I am trying convert long number to Integer...

I have a pivot that will not accept the rowsource if i use long to set last row [runtime error: 13]


for example....

[/FONT]
[FONT=Courier New]Dim lr as long[/FONT]
[FONT=Courier New]lr = Range("A" & rows.count).end(xlup).row[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]ActiveWorkbook.Worksheets("Pivot").PivotTables("PivotTable1").PivotCache. _
        CreatePivotTable TableDestination:="Sheet4!R" & lr & "C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion10

Thanks for helping...

 

[Hermanito]

it should work fine with a long...

try to put a breakpoint on the line that starts with ActiveWorkbook and then type this in the Immediate window:
?"Sheet4!R" & lr & "C1"

What does it give? I expect it gives something like
Sheet4!R 123C1
or even
Sheet4!R 123 C1

If so, try using this instead:
"Sheet4!R" & Trim(Str(lr)) & "C1"
which should give this as result:
Sheet4!R123C1

 

[pedie]

Harmanito....thanks alot for that...

I also tried this way and worked perfectly but at times it comes back runtime error 13:

however if i set lr to integer and make it lr = 161 then it works .....


So my question here is
Do we have anything as converting long number type to integer number type?

Thanks again....

[FONT=Courier New]Dim myCache As Range[/FONT]
[FONT=Courier New]Dim lr As Long[/FONT]
 
[FONT=Courier New]lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]Set myCache = Sheets("Sheet1").Range("B3").CurrentRegion[/FONT]
 
[FONT=Courier New]   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _[/FONT]
[FONT=Courier New]       myCache, Version:=xlPivotTableVersion10).CreatePivotTable _[/FONT]
[FONT=Courier New]       TableDestination:="Sheet6!R1C1", TableName:="MyPivot", DefaultVersion _[/FONT]
[FONT=Courier New]       :=xlPivotTableVersion10[/FONT]

I also reviwed "runtime error 13" in help and it shows cases on when it can error....

 

[Hermanito]

have you tried my suggestion?

maybe there is a difference between the implicit conversion to string from an int versus a long... I know that the implicit conversion sometimes adds a space, which can mess things up.

Converting a long to an int is called a narrowing conversion, and you might get into trouble with that...
If you're sure you will never use rows above the int limit, you might try
Cint(theLongVariable)
to convert it

 

[HalfAce]

Hi Pedie,
I don't really think this has anything to do with the data type being Long or Integer.
You can change it to Integer if you want. The only problem that will arise is if lr is a row number greater than 32,767.

Long data type can handle whole numbers from -2,147,483,648 to 2,147,483,647.
Integer data type can only handle whole numbers from -32,768 to 32,767.

Does that help at all?

 

[pedie]

Hi Dan!...yes it does...Thanks alot!!!

Hermanito, yes i also tried your suggestion...

[/FONT]
[FONT=Courier New]?"Sheet1!R" & trim(Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row) & "C1"[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]?"Sheet1!R" & 161 & "C1"[/FONT]
[FONT=Courier New]?"Sheet1!R" & Trim(Str(lr) & "C1"
Sheet1!R161C1[/FONT]
[FONT=Courier New]

all of it gave me
Sheet1!R161C1
i also tried it in msgbox ...its cool!!!

I appriciate your help...
The thing is that i do not have actual data untill tomorrow to test and get back if problem persist....

Thanks again for helping out!!!

 

[Jonmo1]

I would advise NOT using Integer for a row variable..
Stick with Long because Integer is only good up to 32,767, while we have 65000+ rows in existence.


Perhaps try

TableDestination:="Sheet4!" & Cells(lr, 1).Address

 

[pedie]

I would advise NOT using Integer for a row variable..

Stick with Long because Integer is only good up to 32,767, while we have 65000+ rows in existence.


Perhaps try

TableDestination:="Sheet4!" & Cells(lr, 1).Address

Thanks jonmo1...
yes i'll stick to long...
bout

[/FONT]
[FONT=Courier New]TableDestination:="Sheet4!" & Cells(lr, 1).Address

in post#1 i free typed "TableDestination:="Sheet4!R" & lr & "C1"" and that was not what i was actually using...


Thanks again sticking to long
after Dan stated...it makes alot of sense...