1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
|
Option Explicit
Sub cherche_A1_AAU723()
'
Const f1 As String = "feuil1"
Const f2 As String = "feuil2"
'
Dim iCell As Range
'
Dim i As Long, r As String
'
i = 0
'
For Each iCell In Range("A1:AAU723")
'
If (iCell.Value <> "") Then
'
i = i + 1
r = iCell.Address
'
Sheets(f2).Cells(i, 1).Value = "A" & Mid$(r, InStrRev(r, "$") + 1)
Sheets(f2).Cells(i, 2).Value = "B" & Mid$(r, InStrRev(r, "$") + 1)
Sheets(f2).Cells(i, 3).Value = Mid$(Left$(r, InStrRev(r, "$") - 1), 2) & Mid$(r, InStrRev(r, "$") + 1)
Sheets(f2).Cells(i, 4).Value = Mid$(Left$(r, InStrRev(r, "$") - 1), 2) & "1"
Sheets(f2).Cells(i, 5).Value = Mid$(Left$(r, InStrRev(r, "$") - 1), 2) & "2"
'
Sheets(f2).Cells(i, 7).Value = r
'
End If
'
Next iCell
'
MsgBox " nb : " & i
'
End Sub |
Partager