ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Address of Excel.Range & Range (https://www.excelbanter.com/excel-programming/429126-address-excel-range-range.html)

r
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/

JLGWhiz[_2_]
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/



Peter T
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/



r
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/




r
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/





All times are GMT +1. The time now is 08:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com