Set Column choice to a variable from a Drop Down

[Howard]

This is a segment of a working macro where I would like to be able to choose the column in a drop down to for Set rngFirst =


strFindWhat = Range("G1")

Set rngFirst = Range("A:A").Find(What:=strFindWhat, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


Basically I want to change A:A to B:B or C:C by selecting it from a drop down and look for "strFindWhat" in that column.

So if I chose C:C in the drop down then I would have:

Set rngFirst = Range("C:C").Find(What:=strFindWhat,

Thanks.
Howard

 

[Claus Busch]

Hi Howard,

Am Mittwoch, 30. Oktober 2013 09:27:48 UTC+1 schrieb Howard:

This is a segment of a working macro where I would like to be able to choose the column in a drop down to for Set rngFirst =

e.g. in F1 the dropdown with 1; 2 and 3 then:

Set myRng = Columns(Range("F1"))
Set rngFirst = myRng.Find(What:=strFindWhat, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Kind Regards
Claus B.

 

[Howard]

e.g. in F1 the dropdown with 1; 2 and 3 then:



Set myRng = Columns(Range("F1"))

Set rngFirst = myRng.Find(What:=strFindWhat, _

LookAt:=xlPart, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False)



Kind Regards

Claus B.

That plugs in quite nicely.

Thank, Claus.

Howard

 

[Claus Busch]

Hi Howard,

Am Mittwoch, 30. Oktober 2013 12:32:49 UTC+1 schrieb Howard:

That plugs in quite nicely.

that also works with A, B and C into the dropdown in F1:

Set myrng = Columns(Range("F1").Text)
Set rngfirst = myrng.Find(What:=strFindWhat, _
LookIn:=xlValues, lookat:=xlPart, _
SearchOrder:=xlByRows)

Kind Regards
Claus B.

 

[Howard]

that also works with A, B and C into the dropdown in F1:



Set myrng = Columns(Range("F1").Text)

Set rngfirst = myrng.Find(What:=strFindWhat, _

LookIn:=xlValues, lookat:=xlPart, _

SearchOrder:=xlByRows)



Kind Regards

Claus B.

Using numbers 1 and 2 in F1 I dimmed myRng as Range which works.

Using A, B, C in F1 I get Object Required or Type Mismatch when Dimming myRng as Range, String or Variant.

Howard