CODEWITHSUNDEEP
excelvba
CptGoodar
CptGoodar

Reputation: 303

Using Variables to define a range in find mode

I'm pretty sure this is possible to do but, i have a Variable "DataRowStart" which is assigned a values by the user through an input box, e.g. "A" is there a way to use that variable to define the start of an search? I need this as the location of the data search will change from use to use.

I currently have this line of code for the search; 

LastRowNumber = Range("DataRowStart:DataRowStart").Find(What:="", after:=Range(Cells(DataColumn, 1)), searchdirection:=xlPrevious).Row

And whenever i run the code i get

Method 'Range' of Object'_ Global failed

error.

I defined the variable as a Global DataRowStart as String in the macro that runs this.

I found that this was the best solution as this part of the code is run in a User Form.

Is someone able to point me in the right direction to solve this issue?

Many thanks

Mark

Upvotes: 0

Views: 23

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

You need to take the variable DataRowStart outside the ".

Change:

LastRowNumber = Range("DataRowStart:DataRowStart")

to 

LastRowNumber = Range(DataRowStart & ":" & DataRowStart)

And also:

Range(Cells(1, DataColumn)

To:

Range(DataColumn & 1)

Upvotes: 1

Related Questions