Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Address of Excel.Range & Range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default Address of Excel.Range & Range
Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$1 5," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$ 13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r

--
http://excelvba.altervista.org/blog/...ble/Excel-VBA/
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Address of Excel.Range & Range
You are one over the limit. If you remove one cell address from the value
of s, then it works.


"r" wrote in message
...
Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$1 5," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$ 13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r

--
http://excelvba.altervista.org/blog/...ble/Excel-VBA/


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default Address of Excel.Range & Range
Public Sub m4()
Dim i As Long, arr
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"

s = Replace(s, "$", "")

If Len(s) <=255 Then
Set rng = Range(s)
Else
arr = Split(s, ",")
Set rng = Range(arr(0))
For i = 1 To UBound(arr)
Set rng = Union(rng, Range(arr(i)))
Next
End If
rng.Select

End Sub

Regards,
Peter T


"r" wrote in message
...
Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$1 5," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$ 13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r

--
http://excelvba.altervista.org/blog/...ble/Excel-VBA/


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default Address of Excel.Range & Range
non è un problema per me risolvere l'inconveniente ...
la mia era solo una segnalazione ...
sapere se eravate a conoscenza.

Sub test()
Dim rng As Excel.Range
Dim s As String

s = "$C$1:$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49:$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79:$A$81,$A$83,A90,a91,a95:a 97"

Set rng = Range2(s)
If TypeName(rng) = "Range" Then
rng.Select
Else
MsgBox "Nessun range"
End If
End Sub


Function Range2(s As String) As Range
Dim rng As Excel.Range
Dim RE As Object
Dim v
Set RE = CreateObject("vbscript.regexp")
RE.Global = True
RE.Pattern = "\$?[A-z]+\$?\d+(:\$?[A-z]+\$?\d+)?"
If RE.test(s) Then
Set rng = Evaluate(CStr(RE.Execute(s)(0)))
For Each v In RE.Execute(s)
Set rng = Application.Union(rng, Evaluate(CStr(v)))
Next
End If

If TypeName(rng) = "Range" Then
Set Range2 = rng
End If
End Function

saluti
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Peter T" wrote:

Public Sub m4()
Dim i As Long, arr
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"

s = Replace(s, "$", "")

If Len(s) <=255 Then
Set rng = Range(s)
Else
arr = Split(s, ",")
Set rng = Range(arr(0))
For i = 1 To UBound(arr)
Set rng = Union(rng, Range(arr(i)))
Next
End If
rng.Select

End Sub

Regards,
Peter T


"r" wrote in message
...
Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$1 5," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$ 13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r

--
http://excelvba.altervista.org/blog/...ble/Excel-VBA/



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default Address of Excel.Range & Range
sorry I forgot to translate ...

is not a problem for me to solve the problem ...
I was just a warning ...
whether you were aware

"r" wrote:

non è un problema per me risolvere l'inconveniente ...
la mia era solo una segnalazione ...
sapere se eravate a conoscenza.

Sub test()
Dim rng As Excel.Range
Dim s As String

s = "$C$1:$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49:$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79:$A$81,$A$83,A90,a91,a95:a 97"

Set rng = Range2(s)
If TypeName(rng) = "Range" Then
rng.Select
Else
MsgBox "Nessun range"
End If
End Sub


Function Range2(s As String) As Range
Dim rng As Excel.Range
Dim RE As Object
Dim v
Set RE = CreateObject("vbscript.regexp")
RE.Global = True
RE.Pattern = "\$?[A-z]+\$?\d+(:\$?[A-z]+\$?\d+)?"
If RE.test(s) Then
Set rng = Evaluate(CStr(RE.Execute(s)(0)))
For Each v In RE.Execute(s)
Set rng = Application.Union(rng, Evaluate(CStr(v)))
Next
End If

If TypeName(rng) = "Range" Then
Set Range2 = rng
End If
End Function

saluti
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Peter T" wrote:

Public Sub m4()
Dim i As Long, arr
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"

s = Replace(s, "$", "")

If Len(s) <=255 Then
Set rng = Range(s)
Else
arr = Split(s, ",")
Set rng = Range(arr(0))
For i = 1 To UBound(arr)
Set rng = Union(rng, Range(arr(i)))
Next
End If
rng.Select

End Sub

Regards,
Peter T


"r" wrote in message
...
Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$1 5," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A $15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$ 13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r

--
http://excelvba.altervista.org/blog/...ble/Excel-VBA/



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
getting the absolute range address from a dynamic named range junoon Excel Programming 2 March 21st 06 01:29 PM
Return Range Address from Active Range ExcelMonkey Excel Programming 1 February 10th 06 12:00 PM
select range and put range address in variable [email protected] Excel Programming 2 January 25th 06 01:28 AM
Range address from Excel in C# ojv[_2_] Excel Programming 0 January 3rd 06 10:10 AM
Deleting Range name's listed in the range address box. Satnam Patel Excel Discussion (Misc queries) 4 May 5th 05 01:42 PM


All times are GMT +1. The time now is 10:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement