Lavorare con gli Intervalli

 

Come lavorare con gli intervalli di celle dislocati in posizioni diverse usando sempre la stessa routine o funzione

E' un uso comune, per esplorare un intervallo, comportarci in questo modo:

Dim R, C
For R = 1 To 10
    For C = 1 To 5
        A = Cells(R, C)
    Next
Next

Con questo è chiaro che esploriamo un intervallo di celle che va dalla cella "A1" alla cella "E10".

Se l'intervallo su cui intendiamo lavorare si trova a partire dalla cella "K11", siccome la cella K11 è dislocata alla riga 11, colonna 11, i riferimenti alle celle dovrebbero essere modificati così:

Dim R, C
For R = 11 To 20
    For C = 11 To 15
        Cells(R, C).Select
    Next
Next

Fin qui è tutto giusto e non c'è nulla di nuovo se siamo coscienti che la postazione da cui lavoriamo è lo stesso foglio di Excel. Come i più sanno, e qualche volta è stato ribadito anche in questa sede, se Range o Cells sono utilizzati senza un qualificatore, è sottinteso che questo qualificatore è da intendersi ActiveSheet o Worksheets o Sheets che è il padre di Range o Cells. Quindi le istruzioni appena viste sottintendono un riferimento a ActiveSheet o Sheets("fogliox").

Nota importante: nell'ambiente in cui stiamo lavorando molto importante è la gerarchia dei vari oggetti che così possiamo schematicamente rappresentare:

Application rappresenta l'intera applicazione Microsoft Excel e comprende l'oggetto Workbook

Workbook rappresenta una cartella di lavoro di Microsoft Excel e comprende l'insieme Worksheet

Worksheet rappresenta un foglio di lavoro e comprende l'insieme Range

Range rappresenta una cella, una riga, una colonna, un intervallo di celle
 

Se gli oggetti gerarchicamente superiori sono attivi, nei nostri riferimenti all'oggetto di livello inferiore possiamo sottintenderli. Così, invece di scrivere la seguente istruzione che sintatticamente è valida:

Application.Workbooks("Mia_Cartella.xls").Sheets("Foglio4").Range("A5").Select

oppure quest'altra:

Application.Workbooks("Mia_Cartella.xls").Sheets("Foglio4").cells(5,1).Select

possiamo semplicemente scrivere:

Range("A5").Select

Ma quello che chi sta alle prime armi non sa e che molto spesso sfugge anche a chi sta avanti nella programmazione, è che possiamo lavorare con un intervallo in un'altra maniera. Semplicemente cambiando la postazione da cui intendiamo lavorare.

Se per lavorare ci spostiamo tra le celle del foglio anziché rimanere nel foglio ci accorgeremo che possiamo lavorare usando gli indici da 1 a quello che vogliamo, per esplorare le celle ovunque esse siano.

Se ci poniamo sulla cella "A1" e vogliamo esplorare un intervallo di 10 righe e 5 colonne possiamo usare questa:

With Range("A1")
Dim R, C
For R = 1 To 10
    For C = 1 To 5
        .Cells(R, C).Select    ' da notare il punto prima di Cells
    Next
Next
End With

Potremmo scrivere anche:

Range("A1").Select
ActiveCell.Cells(4, 3).Select

Ma non finisce qui. Potremmo definire un intervallo e lavorare con lo stesso intervallo:

Set Intervallo = Range("A1").CurrentRegion
Intervallo(2, 5).Select

Ma se ci spostassimo sulla cella "K11" anziché rimanere nella cella "A1" possiamo usare le stesse istruzioni (indici compresi) avendo cura di cambiare solo il riferimento "A1" con "K11".

With Range("K1")
Dim R, C
For R = 1 To 10
    For C = 1 To 5
        .Cells(R, C).Select    ' da notare il punto prima di Cells
    Next
Next
End With

Range("K1").Select
ActiveCell.Cells(4, 3).Select

Set Intervallo = Range("K1").CurrentRegion
Intervallo(2, 5).Select

Con questo tipo di notazione (Cells(R, C)) possiamo addirittura usare dei riferimenti con numeri negativi.

ActiveCell.Cells(-4, -3).Select

Attenzione

Questo uso, tuttavia, è deprecabile, quindi sconsigliato, in quanto può causare confusione e, soprattutto, errori nell'esecuzione della macro. Se, infatti ci troviamo in prossimità della prima riga o della prima colonna del foglio, l'istruzione ci porterebbe fuori del foglio stesso, con relativa arrabbiatura del VBA.

Non solo: possiamo usare anche la notazione A1. Così potremmo scrivere:

With Range("K11")
.Range("A1").Select        'sarà selezionata la K11 (stessa cella di partenza)
.Range("K11").Select      'sarà selezionata la U21 (che sta 11 righe più sotto e 11 colonne più a destra rispetto alla K11)
End With

Oppure:

ActiveCell.Range("A1").Select
ActiveCell.Range("K11").Select

ottenendo gli stessi spostamenti riferiti, però, alla cella attiva.

Attenzione:

Tuttavia io sconsiglierei anche questa notazione, anche se consentita, perchè potrebbe far insorgere confusione in chi legge il codice. Infatti siamo abituati a chiamare A1 la vera cella A1 ed a chiamare K11 la vera cella K11.

Possiamo lavorare usando uno o due indici.

Usando un solo indice

Diversi sono gli spostamenti che possiamo ottenere se facciamo riferimento ad un intervallo che si estende su una sola colonna o ad un intervallo che si estende su più colonne. Gli spostamenti ottenuti usando un solo indice seguono una sorta di tunnel delimitato dalle colonne comprese nel'intervallo.


Uso di un solo indice
Set Intervallo = Range("F9")
With Intervallo
For indice = 1 To 100
.Cells(indice).Select
Next
End With

Se viene indicato un solo indice e lavorando su una sola colonna, ad ogni passo del ciclo viene selezionata una cella sottostante quella precedente (F9, F10, F11, ecc.).

1
2
3
1

Set Intervallo = Range("F9").CurrentRegion
With Intervallo
For indice = 1 To 100
.Cells(indice).Select
Next
End With

Con questo è più chiaro che lo spostamento non avviene verso le celle sottostanti, come sembrerebbe nell'esempio appena visto, ma lungo un sorta di corridoio delimitato tra la prima e l'ultima colonna dell'intervallo su cui si sta lavorando come si può vedere dallo schema che segue e che spiega l'andamento della seconda serie di istruzioni.

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20

 

Usando due indici

Diverso è il modo di lavorare con due indici. Partendo da un intervallo che può essere formato da una sola cella o da più celle disposte su più colonne o righe, possiamo spaziare su tutto il foglio.


Uso di due indici

Set Intervallo = Range("F9")
With Intervallo
For R = 1 To 100
       For C = 1 To 10
              .Cells(R, C).Select
       Next
Next

Se vengono indicati due indici come riferimento alle celle di un intervallo è possibile selezionare qualsiasi cella del foglio e, a prescindere dal tipo di intervallo su cui lavoriamo, una sola cella, più celle sulla stessa colonna, più celle che si estendono su più colonne, gli spostamenti tra le celle faranno sempre riferimento alla cella attiva dell'intervallo che in genere è la prima in alto a sinistra.

Nell'esempio qui a fianco facciamo riferimento alla sola cella F9.


Set Intervallo = Range("F9").CurrentRegion
With Intervallo
For R = 1 To 100
       For C = 1 To 10
              .Cells(R, C).Select
       Next
Next
End With
End Sub


Nell'esempio qui a fianco facciamo riferimento ad un intero intervallo grosso non importa quanto.

Se volessimo essere sicuri di restare nell'intervallo indicato potremmo istruire i cicli in maniera diversa, come è indicato nell'esempio che segue.


Set Intervallo = Range("F9").CurrentRegion
For R = 1 To Intervallo.Rows.Count
       For C = 1 To Intervallo.Columns.Count
              Intervallo(R, C).Select
       Next
Next
End Sub


In questo ultimo esempio, invece di usare Cells per spostarci, usiamo lo stesso Intervallo corredato dei soliti indici e per essere sicuri di rimanere nell'intervallo istruiamo i cicli con:

For R = 1 To Intervallo.Rows.Count

e

For C = 1 To Intervallo.Columns.Count


 

Arrivati a questo punto penso sia chiaro a tutti che questo è senz'altro un modo di lavorare più comodo e che potremmo usare e riciclare le nostre istruzioni a prescindere la zona del foglio a cui ci riferiamo.

Così facendo avremo molti vantaggi tra cui:

A completamento di quanto è stato detto finora si può osservare la seguente routine.

 

Ricerca di un articolo
 

  E F G H I     K L M N
8             8        
9   Articoli Q.tà Prezzo Totale 9 Articoli Q.tà Prezzo Totale
10   Chiodi 100 €     0,04 4 10 Chiodi 100 €     0,04 4
11   Trapano 5 €   38,00 190 11 Trapano 5 €   38,00 190
12   avvitatore 5 €   28,20 141 12 avvitatore 5 €   28,20 141
13   Chiodi 100 €     0,02 2 13 Chiodi 100 €     0,02 2
14   Tasselli 100 €     0,25 25 14 Tasselli 100 €     0,25 25
15   Mola 7 €   38,50 269,5 15 Mola 7 €   38,50 269,5
16   Martello 15 €     3,04 45,6 16 Martello 15 €     3,04 45,6
17   Viti 200 €     3,50 700 17 Viti 200 €     3,50 700
18           18 Mola 7 €   38,50 269,5
19           19 avvitatore 5 €   28,20 141
20           20 Mola 7 €   38,50 269,5
21           21 Mola 7 €   38,50 269,5

 

Come vedete in un foglio abbiamo 2 tabelle una a partire dalla cella F9 e l'altra a partire dalla cella K9.

Normalmente per lavorare con più tabelle occorrerebbe impostare gli indici di riga e di colonna per determinare l'inizio e la fine di ciascuna di esse. Ma col metodo che stiamo analizzando ora vedremo come poter lavorare con ciascuna di esse usando la stessa routine e gli stessi indici.

Innanzitutto identifichiamo la tabella con la quale vogliamo lavorare: quella che comincia dalla cella F9 o quella che inizia da K9

In questo caso chiediamo all'utente, mediante InputBox, di indicare quale articolo vuol esaminare ed in quale intervallo intende lavorare

ArtQuale = InputBox("Dimmi quale articolo cercare", "Cerca")
IntervQuale = InputBox("in quale intervallo cercare:" & vbCr & "in ""F9""" & vbCr & "in ""K9""", "Dove cerco")

 

Creazione di una stringa con doppi apici

Nell'ultima riga che leggiamo qui sopra notiamo una sequenza abbastanza inusuale di doppi apici e caratteri &. Questo serve per costruire la stringa che contiene anche i doppi apici:

  • la coppia di doppi apici serve per accettare nella stringa un doppio apice
  • il carattere & serve per concatenare in una unica stringa tutti i vari elementi
  • alla fine dell'operazione avremo una stringa che contiene "F9" e "K9" (apici compresi)

Definiamo l'intervallo (anche qui la storia dei doppi apici si ripete)

Set Intervallo = Range("" & IntervQuale & "").CurrentRegion

Quindi istruiamo i due cicli per esplorare le righe e le colonne

For R1 = 1 To Intervallo.Rows.Count
For C1 = 1 To Intervallo.Columns.Count

Fatto questo non ci resta che lavorare con l'intervallo individuato usando come indici gli stessi contatori dei cicli For che possono tranquillamente partire da 1 fino al numero massimo determinato da Intervallo.Rows.Count e Intervallo.Columns.Count proprio come si farebbe con una normale matrice a due dimensioni

Intervallo(R1, C1)

 

Passiamo ad un esempio pratico

In questo esempio cerchiamo di individuare la cella dove è memorizzato l'articolo da cercare.

 

Due note interessanti

Il confronto che viene eseguito, tra il dato che è stato digitato nella InputBox ed il dato presente nel foglio viene effettuato adottando la funzione StrComp aiutata dalla costante vbTextCompare per non tener conto della differenza tra caratteri maiuscoli e minuscoli:

If StrComp(IntervQuale, "F9", vbTextCompare) = -1

If StrComp(ArtQuale, Intervallo(R1, C1), vbTextCompare) = 0 Then

E' da tener presente che i possibili risultati di questo confronto possono essere:

string1 è minore di string2 -1
string1 è uguale a string2 0
string1 è maggiore di string2 1

 

La restituzione dell'indirizzo dell'articolo trovato usando la proprietà Address della cella trovata aiutato RowAbsolute:=False e ColumnAbsolute:=False che nella istruzione viene abbreviata con Intervallo(R1, C1).Address(False, False) per ottenere la restituzione dell'indirizzo nel formato A1 che diversamente sarebbe $A$1

Questo è il codice di esempio:

 

Sub CercaInElenco()
Dim ArtQuale, IntervQuale
Dim Intervallo As Range
Dim R1, C1, Mess, Indirizzo
'    chiedo il nome dell'articolo
ArtQuale = InputBox("Dimmi quale articolo cercare", "Cerca")
If ArtQuale = "" Then Exit Sub
'    ora chiedo in quale intervallo cercare
IntervQuale = InputBox("in quale intervallo cercare:" & vbCr & "in ""F9""" & vbCr & "in ""K9""", "Dove cerco")
'    controllo se l'ultima risposta è esatta
If StrComp(IntervQuale, "F9", vbTextCompare) = -1 And StrComp(IntervQuale, "K9", vbTextCompare) = -1 Then
        MsgBox "risposta sbagliata"
        Exit Sub
End If
Mess = "Articolo non trovato"
Set Intervallo = Range("" & IntervQuale & "").CurrentRegion
For R1 = 1 To Intervallo.Rows.Count
        For C1 = 1 To Intervallo.Columns.Count
'                se l'articolo cercato viene trovato se ne rileva l'indirizzo
                If StrComp(ArtQuale, Intervallo(R1, C1), vbTextCompare) = 0 Then
                        Indirizzo = Intervallo(R1, C1).Address(False, False)
                        Mess = "Trovato: "
                End If
        Next
Next
If Mess = "Trovato: " Then Mess = Mess & ArtQuale & " in " & Indirizzo
MsgBox Mess, vbOKOnly
End Sub

Qui si possono vedere  le finestre generate da questo codice.

Date anche un'occhiatina al metodo usato per la comparazione tra le stringhe tramite StrComp di cui si potrà parlare in un altro articolo.

 

Buon lavoro ed alla prossima volta