Define a Range with Assembled String

  • Thread starter Thread starter Gandalph
  • Start date Start date
G

Gandalph

Greetings from Norfolk (UK)

I am trying to generalise a specific piece of code I 'threw' together.
The first verison causes an error (as noted below) and the second runs OK.
On looking with Debug the content of "FirstCell" is the same , i.e. A1

Running Excel 2000 under Win XP

The following are the two versions of the set up and call in the main
procedure, followed by the relevant piece of code from the called procedure

SearchCol = "A"
Call DateRng(SearchCol)

*****************************

Sub DateRng(SearchCol As String)

Dim FirstCell As String * 4

FirstCell = SearchCol & GetRealFirstRow()
Range(FirstCell).Select
etc.
Generates
Run Time Error 1004
Method 'Range ' of object '_Worksheet' failed
******************************************
The following works OK

Call DateRng()

****************************

Sub DateRng()

Dim FirstCell As String * 4

FirstCell = "A" & GetRealFirstRow()
Range(FirstCell).Select

Your comments and advice would be appreciated.
 
I'd change

FirstCell = SearchCol & GetRealFirstRow()
Range(FirstCell).Select

to
Cells(GetRealFirstRow(),SearchCol).Select

If I were you, I'd declare the worksheet that you are acting on. I presume
it's the active worksheet . I'd do it this way

Dim aWS as Worksheet
Set aWS = ActiveSheet

and for the first one, I'd change
Cells(GetRealFirstRow(),SearchCol).Select

to
aWS.Cells(GetRealFirstRow(),SearchCol).Select


If you do it this way, you don't need to select the cells, but can act
directly on them. Selecting cells takes longer to execute than just acting
on the cells directly.
 
I'd check the value in SearchCol and what's returned from the GetRealFirstRow
function.

Maybe you don't have a real address.

If that's not the problem, I'd make sure I selected the correct worksheet first
and then selected the range.

Worksheets("somesheetnamehere").select
Worksheets("somesheetnamehere").Range(FirstCell).Select
 
Barb Reinhardt said:
I'd change

FirstCell = SearchCol & GetRealFirstRow()
Range(FirstCell).Select

to
Cells(GetRealFirstRow(),SearchCol).Select

If I were you, I'd declare the worksheet that you are acting on. I presume
it's the active worksheet . I'd do it this way

Dim aWS as Worksheet
Set aWS = ActiveSheet

and for the first one, I'd change
Cells(GetRealFirstRow(),SearchCol).Select

to
aWS.Cells(GetRealFirstRow(),SearchCol).Select


If you do it this way, you don't need to select the cells, but can act
directly on them. Selecting cells takes longer to execute than just acting
on the cells directly.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.
Click to expand...
Thank you for your suggestions, I will look at them in detail, but, it does
not explain why the first version
(FirstCell = SearchCol & GetRealFirstRow()
Range(FirstCell).Select
fails, and the second version
FirstCell = "A" & GetRealFirstRow()
Range(FirstCell).Select
Works OK.
Is it that strings 'assembled' by the programme are not acceptable to
Range(*).Select

I am not being difficult, but wicsh to know so that I can use this step in
future.
 
Thank you for your reply, but you appear to have missed the principal point
of the question.
The first listed code runs upto the statement
Range(FirstCell).Select
On actioning this statement the error is generated.

The second code fragment runs correctly (to me) and generates no error, and
continues through the sub routine to the end.

The code 'changes' were made using the comment (') to select either one or
the other, no other changes being made.

I wsh to know WHY for future reference etc.
 
Back
Top