CODEWITHSUNDEEP
excelfindrangevba
pshemek
pshemek

Reputation: 1419

Excel vba finds a range but cannot activate it

I am working with a database extract file in xlsx format. The structure looks like 

ID     DC1      DC2 ............  DCn
1      data     data ...........  data
2      data     data ...........  data
3      data     data ...........  data

I use this code to find and activate the ID cell.

Set foundSourceRange = Sheets(sourceSheet).Cells.Find(What:=sourceColumn, LookIn:=xlValues, LookAt:=xlWhole)
foundSourceRange.Activate

Find gives the requested cell but Activate throws as error.

Runtime Error 1004
Activate method of Range class failed.

If I go to the extract file and edit the headers (retype them manually) then my code works as expected. I have already tried to change an extract file format to csv but it gives the same error. Is there anything I can do with the code to solve this?

Upvotes: 1

Views: 37

Answers (1)

SierraOscar
SierraOscar

Reputation: 17647

You need to activate the sheet first:

Set foundSourceRange = Sheets(sourceSheet).Cells.Find(What:=sourceColumn, LookIn:=xlValues, LookAt:=xlWhole)
Sheets(sourceSheet).Activate
foundSourceRange.Activate

You can only .Activate a range on the ActiveSheet object.

Upvotes: 2

Related Questions