Excel Macro, Can Range() refer to a variable/dynamic column?

%3CLINGO-SUB%20id%3D%22lingo-sub-895496%22%20slang%3D%22en-US%22%3EExcel%20Macro%2C%20Can%20Range()%20refer%20to%20a%20variable%2Fdynamic%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895496%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20bit%20help%20on%20the%20Range()%20in%20Excel%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EResult%20%3D%20Sht2.Worksheets(Company.Value2).Range(%22a%3Aa%22).Find(Sht1.Worksheets(Company.Value2).Cells(i%2C%20FindKey1.Column)%2C%20LookIn%3A%3DxlValues%2C%20lookat%3A%3DxlWhole)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20I'm%20using%20this%20code%20to%20test%20if%20the%20tested%20value%20can%20be%20found%20in%20Sheet2%2C%20worksheet%20X%2C%20under%20column%20A.%3C%2FP%3E%3CP%3EHowever%20the%20key%20column%20sometimes%20is%20not%20column%20A%2C%20it%20could%20be%20B%2C%20C%2C%20or%20D.%3C%2FP%3E%3CP%3EWith%20the%20help%20of%20code%20below%20I%20can%20locate%20the%20key%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESet%20FindKey1%20%3D%20Sht1.Worksheets(Company.Value2).Range(%22A10%3AG20%22).Find(what%3A%3D%22Time%22%2C%20LookIn%3A%3DxlValues%2C%20lookat%3A%3DxlPart)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20question%20is%20-%20How%20do%20I%20put%20the%20whole%20column%20in%20Range()%20function%3F%3C%2FP%3E%3CP%3EI%20think%20this%20is%20the%20most%20efficient%20way%20for%20my%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20may%20negotiate%2C%20if%20the%20idea%20above%20is%20not%20feasible.%20Say%2C%20the%20search%20can%20be%20done%20within%20a%20larger%20area%20-%20Range(%22the%20cell%20on%20the%20left-top%2C%20the%20cell%20on%20the%20bottom-right)%2C%20like%20Range(%22A10%2CE5000%22)%2C%20though%20it%20would%20take%20more%20time%2C%20but%20at%20least%20it%20does%20the%20thing.%3C%2FP%3E%3CP%3EWhereas%2C%20the%20problem%20is%2C%20the%20last%20used%20cell%20is%20not%20fixed.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I've%20tried%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESet%20Result%20%3D%20Sht2.Worksheets(Company.Value2).Range(Cells(10%2C%201)%2C%20Cells(LRow2.Row%2C%20FindKey2.Column)).Find(Sht1.Worksheets(Company.Value2).Cells(i%2C%20FindKey1.Column)%2C%20LookIn%3A%3DxlValues%2C%20lookat%3A%3DxlWhole)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enot%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EDummy810%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-895496%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895533%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Macro%2C%20Can%20Range()%20refer%20to%20a%20variable%2Fdynamic%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895533%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412472%22%20target%3D%22_blank%22%3E%40dummy810%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20give%20you%20an%20idea%2C%20you%20may%20adopt%20the%20following%20approach...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EDim%20ws%20As%20Worksheet%0ADim%20Rng%20As%20Range%0A%0ASet%20ws%20%3D%20Worksheets(%22Sheet1%22)%0A%0ASet%20Rng%20%3D%20ws.Range(%22A10%3AG20%22).Find(what%3A%3D%22Time%22%2C%20LookIn%3A%3DxlValues%2C%20lookat%3A%3DxlPart)%0A%0AIf%20Not%20Rng%20Is%20Nothing%20Then%0A%20%20%20%20Set%20Rng%20%3D%20ws.Columns(Rng.Column)%0A%20%20%20%20MsgBox%20Rng.Address%0AEnd%20If%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhenever%20you%20use%20Range.Find%20method%2C%20always%20check%20if%20the%20value%20being%20searched%20was%20found%20as%20showed%20in%20the%20above%20code%20with%20the%20line%20%3CSTRONG%3EIf%20Not%20Rng%20Is%20Nothing%20Then%20%3C%2FSTRONG%3Eotherwise%20if%20the%20value%20being%20searched%20is%20not%20found%2C%20the%20code%20will%20throw%20an%20error%20if%20you%20rely%20on%20range%20obtained%20from%20the%20Range.Find%20method%20and%20use%20it%20further%20in%20your%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895626%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Macro%2C%20Can%20Range()%20refer%20to%20a%20variable%2Fdynamic%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895626%22%20slang%3D%22en-US%22%3EHi%20Subodh%2C%3CBR%20%2F%3EThank%20you%20for%20your%20advice%20even%20though%20it%20doesn't%20address%20my%20issue.%20While%20it%20is%20still%20a%20very%20good%20tip.%20Will%20pack%20it%20up%20for%20late%20usage.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers.%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

Hi there,

 

I need a bit help on the Range() in Excel VBA.

 

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)

 

not working.

 

Any suggestions?

Thanks.

 

Regards,

Dummy810

 

2 Replies
Highlighted

@Deleted 

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)
    MsgBox Rng.Address
End If

 

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.

Highlighted
Hi Subodh,
Thank you for your advice even though it doesn't address my issue. While it is still a very good tip. Will pack it up for late usage.

Cheers.