.find inside a ranges union

[Maryca]

Hi!

I have a problem with the function .find.

Excel starts the debug when I need to find the value "minRow" inside "myRange" that is the union of two different ranges.

I don't know why, can someone help me?

thanks!


Set myRange1 = Worksheets("CAST SEGMENTS half day").Range("K2:P2")
Set myRange2 = Worksheets("CAST SEGMENTS half day").Range("R2:S2")

myRange = Application.Union(myRange1, myRange2)

minRow = Application.WorksheetFunction.Min(myRange)


Set rng = Range("myRange").find(What:=minRow, LookAt:=xlWhole, lookin:=xlValues)
num_colonna = rng.Column

 

[AlphaFrog]

Hi and welcome to the board.

Try something like this...

    [COLOR=darkblue]Dim[/COLOR] myRange [COLOR=darkblue]As[/COLOR] Range, rng [COLOR=darkblue]As[/COLOR] Range, minRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR], num_colonna [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=#ff0000]Set myRange[/COLOR] = Worksheets("CAST SEGMENTS half day").Range("K2:P2,R2:S2")
    minRow = Application.WorksheetFunction.Min(myRange)
    
    [COLOR=darkblue]Set[/COLOR] rng = [COLOR=#ff0000]myRange[/COLOR].Find(What:=minRow, LookAt:=xlWhole, LookIn:=xlValues)
    num_colonna = rng.Column

 

[Maryca]

Thanks for answering.

There are still problems, excel is saying "Duplicate declaration in current scope".

 

[Rick Rothstein]

...excel is saying "Duplicate declaration in current scope".

That means you already have Dim'med one or more of the variables AlphaFrog included in his Dim statement... just remove the duplicated ones so only each variable is Dim'med only once within your Sub.