Home

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
dummy810
Occasional Contributor

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

@dummy810 

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.

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.
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies