PDA

View Full Version : [SOLVED] Can't find out my mistake using a variable



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

snb
04-18-2014, 09:25 AM
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
04-18-2014, 03:17 PM
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
04-22-2014, 01:09 AM
Hello

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