Can't find out my mistake using a variable

[ValerieT]

I am sure it is obvious, but I can't see it:

I'm building a variable key (My_Search dim as string) built by: raw header & "-" & column header in order to find a value attached to it.
(example: A-B)
When I try to find the value (in another sheet), I keep having errors, not matter what I tried (find, vlookup).

Sub Macro1()
Dim My_Search As String
Dim My_Result As String

'FIND TABLE LIMIT
For i = 2 To 9999999
    If Cells(i, 1) = 0 Then
       EndLign = i - 1
       Exit For
    End If
Next i
For i = 2 To 9999999
    If Cells(1, i) = 0 Then
      EndCol = i - 1
      Exit For
   End If
Next i

'CREATE KEY
For L = 2 To EndLign             'ligne
My_L = Cells(L, 1)
For C = 2 To EndCol          'colonne
My_C = Cells(1, C)
My_Search = My_L & "-" & My_C    ' Create key to find
If My_L <> My_C Then              'don't process identical
 '       Cells(L, C) = My_Search '         'check


'GET RESULT
My_Result = ""
'1ST ATTEMPT:  error message => Object variable not set
'My_Result = Sheets("DATA").Columns(4).Find(What:=My_Search, LookIn:=xlValues, LookAt:= _
'            xlWhole, SearchOrder:=xlByColumns).Row

'2ST ATTEMPT:  error message => Unable to get the Vlookup property of the worsheet function class
'My_Result = WorksheetFunction.VLookup(My_Search, Worksheets("DATA").Columns("D:E"), 2, 0)
'       If IsError(My_Result) Then
'       Cells(L, C) = "!!"
'             Else
 '     Cells(L, C) = My_Result
 '      End If
End If
Next C
Next L
End Sub

[snb]

refer to the sheet you are working in:

Sub M_snb()
  on error resume next

  for each cl in feuille1.columns(1).specialcells(2)
    msgbox feuille2.columns(4).find(cl.value,,,1).address
  next
End Sub

[p45cal]

A few tweaks to your code; both ways work, the .Find version is commented out with double apostrophes.

Sub Macro1()
Dim My_Search As String
Dim My_Result As Range
Dim myVLookupResult
Dim my_ResultRow As Long
'FIND TABLE LIMIT
For i = 2 To 9999999
  If Cells(i, 1) = 0 Then
    EndLign = i - 1
    Exit For
  End If
Next i
For i = 2 To 9999999
  If Cells(1, i) = 0 Then
    EndCol = i - 1
    Exit For
  End If
Next i

'CREATE KEY
For L = 2 To EndLign  'ligne
  My_L = Cells(L, 1)
  For C = 2 To EndCol  'colonne
    My_C = Cells(1, C)
    My_Search = My_L & "-" & My_C  ' Create key to find
    If My_L <> My_C Then  'don't process identical
      '       Cells(L, C) = My_Search '         'check
      'GET RESULT
      ''      my_ResultRow = 0
      ''      '1ST ATTEMPT:  error message => Object variable not set
      ''      Set My_Result = Sheets("DATA").Columns(4).Find(What:=My_Search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns)
      ''      If Not My_Result Is Nothing Then
      ''        my_ResultRow = My_Result.Row
      ''        Cells(L, C) = My_Result.Offset(, 1).Value '? Is this what is required?
      ''      End If
      '2ST ATTEMPT:  error message => Unable to get the Vlookup property of the worsheet function class
      myVLookupResult = Application.VLookup(My_Search, Worksheets("DATA").Columns("D:E"), 2, 0)
      If IsError(myVLookupResult) Then
        Cells(L, C) = "!!"
      Else
        Cells(L, C) = myVLookupResult
      End If
    End If
  Next C
Next L
End Sub

[ValerieT]

Hello

Thanks for the tips, it works well. This forum is great to keep learning!