Using Variables to define a range in find mode

Question

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

is "A" you are getting through an InputBox suppose to be a Column or Row ? — Shai Rado
– Shai Rado, Commented Dec 11, 2017 at 15:08
@ShaiRado yes the "A" is meant to det which column is serached for data — CptGoodar
– CptGoodar, Commented Dec 11, 2017 at 15:11
@QHarr If i swap out DataRowStart for A the program runs with no issue — CptGoodar
– CptGoodar, Commented Dec 11, 2017 at 15:12

Shai Rado · Accepted Answer · 2017-12-11 15:23:24Z

1

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)

edited Dec 11, 2017 at 15:23

answered Dec 11, 2017 at 15:12

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CptGoodar Over a year ago

when i run this code i now get "Application-defined or object error" which is think is from the Range(Cells(DataColumn,1)) section. Would i be right in thinking this? IGNOR THIS MISTYPED A LETTER

CptGoodar Over a year ago

Unfortunetly, when i run this code, after this letter mistype i still get the saame Metho'Range... error

Shai Rado Over a year ago

@CptGoodar yes, you are correct. see edited code. I changed it according to DataColumn represents a Column.

CptGoodar Over a year ago

That solved it and is now working as intend. Thank you very much,

Collectives™ on Stack Overflow

Using Variables to define a range in find mode

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related