Result = Sht2.Worksheets(Company.Value2).Range("a:a").Find(Sht1.Worksheets(Company.Value2).Cells(i, FindKey1.Column), LookIn:=xlValues, lookat:=xlWhole)
Basically I'm using this code to test if the tested value can be found in Sheet2, worksheet X, under column A.
However the key column sometimes is not column A, it could be B, C, or D.
With the help of code below I can locate the key column.
Set FindKey1 = Sht1.Worksheets(Company.Value2).Range("A10:G20").Find(what:="Time", LookIn:=xlValues, lookat:=xlPart)
The question is - How do I put the whole column in Range() function?
I think this is the most efficient way for my code.
But I may negotiate, if the idea above is not feasible. Say, the search can be done within a larger area - Range("the cell on the left-top, the cell on the bottom-right), like Range("A10,E5000"), though it would take more time, but at least it does the thing.
Whereas, the problem is, the last used cell is not fixed.
So I've tried
Set Result = Sht2.Worksheets(Company.Value2).Range(Cells(10, 1), Cells(LRow2.Row, FindKey2.Column)).Find(Sht1.Worksheets(Company.Value2).Cells(i, FindKey1.Column), LookIn:=xlValues, lookat:=xlWhole)
Just to give you an idea, you may adopt the following approach...
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Sheet1")
Set Rng = ws.Range("A10:G20").Find(what:="Time", LookIn:=xlValues, lookat:=xlPart)
If Not Rng Is Nothing Then
Set Rng = ws.Columns(Rng.Column)
Whenever you use Range.Find method, always check if the value being searched was found as showed in the above code with the line If Not Rng Is Nothing Then otherwise if the value being searched is not found, the code will throw an error if you rely on range obtained from the Range.Find method and use it further in your code.