Macro VBA

Bonjour,

Je viens d'écrire une macro qui me rajoute des colonnes dans une feuille excel et qui les remplit en fonction des autres colonnes. Malheuresement j'ai des erreurs d'incompatibilité de type 13 qui revient. Le programme est le suivant:

Sub recherche_adresse_id()
'programme qui permet de remplir les champs adresses_id à partir du fichier proba

Dim last_cellule As Variant
Sheets("export_offre").Select

last_cellule = Sheets("proba").Range("A1").End(xlDown).Address

Dim j As Integer
Dim intitule As String
Dim cellule As String

cellule = ""
j = 2

cellule = [A1:CC1].Find(What:="adresse_id", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns).Address

cellule = Replace(cellule, "$1", "$")
last_ligne = Replace(last_cellule, "$A$", "")

While Sheets("export_offre").Range("A" & j).Value <> ""

Select Case Range(cellule & j)
Case ""
ActiveCell.Range(cellule & j).Formula = "=Vlookup(export_offre!A" & j & ",proba!A1:C" & last_ligne & ",2,FALSE)"

End Select
j = j + 1
Wend

End Sub

>>>> C'est à l'instruction Case que ca pose un problème.

Même problème ici:

Sub classes_surf()

Dim last_cellule As String
last_cellule = Sheets("export_offre").Range("A1").End(xlDown).Address

Dim j, i As Integer

j = 2

Sheets("export_offre").Select
Dim x, x2, x3 As String

x = ""
x = [A1:CC1].Find(What:="tranche surface", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns).Address
x = (Replace(x, "$1", "$"))

x2 = ""
x2 = [A1:CC1].Find(What:="Total de lot_surface", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns).Address
x2 = (Replace(x2, "$1", "$"))

While Sheets("export_offre").Range("A" & j).Value <> ""

Select Case Range(x2 & j).Value
Case Is <= 500
Range(x & j).Value = "< 500 m²"
Case 500 To 999
Range(x & j).Value = "500 - 1 000m²"
Case 1000 To 2999
Range(x & j).Value = "1 000 - 3 000m²"
Case 3000 To 4999
Range(x & j).Value = "3 000 - 5 000m²"
Case 5000 To 9999
Range(x & j).Value = "5 000 - 10 000 m²"
Case 10000 To 19999
Range(x & j).Value = "10 000 - 20 000 m²"
Case 20000 To 49999
Range(x & j).Value = "20 000 - 50 000 m²"
Case Else
Range(x & j).Value = "> 50 000 m²"
End Select
j = j + 1

Wend
x3 = ""
x3 = [A1:CC1].Find(What:="sup 10000", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns).Address
x3 = (Replace(x3, "$1", "$"))

i = 2
While Sheets("export_offre").Range("A" & i).Value <> ""

Select Case Range(x & i).Value
Case Is < 10000
Range(x3 & i).Value = "< 10000 m²"
Case Else
Range(x3 & i).Value = "> 10 000 m²"
End Select
i = i + 1
Wend

End Sub


Toujours à l'instruction case et même erreur

Merci d'avance si quelqu'un peut m'aider.