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