ValerieT
04-18-2014, 06:21 AM
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
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