Copy/paste of a variable cell range in VBA

Hello, I can help you correct your mistakes and optimize this program. Here is a cleaned and improved translation of your VBA request:

- Select column C between rows g and h (see macro)
- Normally the values are in ascending order
- Copy the selection and paste (see macro)
- The selection criterion is in column E and must be between two values from another sheet (see macro)

Original VBA (formatted for readability, with issues noted):
Sub Nom()
Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Dim e As Range
Dim f As Range
Dim g As Integer
Dim h As Integer

Sheets("Paramètres").Select
a = Range("S3").Select
b = Range("T3").Select
Sheets("Name").Select

For i = 2 To 500
c = Range("E" & i).Select
If c >= a Then
g = i
d = Range("C" & g).Select
Exit For
End If
Next i

For j = g To 500
e = Range("E" & j).Select
If e >= Range("E" & g).Select Then
If e <= b Then
If (Range("E" & j + 1).Select > e) Then
Else
h = j
f = Range("C" & h).Select
Exit For
End If
End If
End If
Next j

Range("C" & g & ":C" & h).Select
Application.CutCopyMode = False
Selection.Copy
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
End Sub

Proposition de version corrigée et optimisée, en conservant l’esprit et les objectifs:
- Utilise des références pleinement qualifiées (no Selects inutiles)
- Évite les boucles et Select/Activate répétitifs
- Gère les types correctement (Long pour les indices, Application.Transpose si nécessaire)
- Copie en valeurs vers I2 sans passer par le presse-papier de manière inutile

Option 1: Version directe et simple
Sub Nom()
Dim a As Variant
Dim b As Variant
Dim g As Long
Dim h As Long
Dim i As Long
Dim j As Long
Dim wsParam As Worksheet
Dim wsName As Worksheet
Dim v As Variant

Set wsParam = ThisWorkbook.Sheets("Paramètres")
Set wsName = ThisWorkbook.Sheets("Name")

a = wsParam.Range("S3").Value
b = wsParam.Range("T3").Value

g = 0
For i = 2 To 500
v = wsName.Parent.Sheets(wsName.Name).Range("E" & i).Value
If v >= a Then
g = i
Exit For
End If
Next i

If g = 0 Then Exit Sub

h = g
For j = g To 500
v = wsName.Parent.Sheets(wsName.Name).Range("E" & j).Value
If v >= wsName.Range("E" & g).Value Then
If v <= b Then
' Trouver le dernier index où E(j+1) > E(j)
If j + 1 <= 500 Then
If wsName.Range("E" & (j + 1)).Value > v Then
h = j
Exit For
End If
Else
h = j
Exit For
End If
End If
End If
Next j

If g > 0 And h >= g Then
wsName.Range("C" & g & ":C" & h).Copy
wsName.Range("I2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
End Sub

Option 2: Version encore plus robuste (sans dépendance à desactivations de sélection)
Sub NomRobuste()
Dim a As Variant, b As Variant
Dim g As Long, h As Long
Dim i As Long, j As Long
Dim val As Variant
Dim wsParam As Worksheet, wsName As Worksheet

Set wsParam = ThisWorkbook.Sheets("Paramètres")
Set wsName = ThisWorkbook.Sheets("Name")

a = wsParam.Range("S3").Value
b = wsParam.Range("T3").Value

g = 0
For i = 2 To 500
val = wsName.Range("E" & i).Value
If Not IsEmpty(val) And val >= a Then
g = i
Exit For
End If
Next i

If g = 0 Then Exit Sub

h = g
For j = g To 500
val = wsName.Range("E" & j).Value
If Not IsEmpty(val) And val >= wsName.Range("E" & g).Value Then
If val <= b Then
If j + 1 <= 500 Then
If wsName.Range("E" & (j + 1)).Value > val Then
h = j
Exit For
End If
Else
h = j
Exit For
End If
End If
End If
Next j

If g > 0 And h >= g Then
Dim src As Range
Dim dest As Range
Set src = wsName.Range("C" & g & ":C" & h)
Set dest = wsName.Range("I2")
dest.Resize(src.Rows.Count, src.Columns.Count).Value = src.Value
End If
End Sub

Si vous pouvez préciser vos valeurs en S3 et T3 et la logique exacte sur la plage à copier, je peux adapter parfaitement le code et ajouter des gestionnaires d’erreurs et des commentaires en français. Voulez-vous que je vous fournisse une version prête à coller dans votre VBA avec des explications en ligne?