runtime error 1004

[DavidVernon]

Hi I get runtime error 1004, Activate method of Range Class failed when I run the macro below using a double click from a sheet called "Europe".
I have highlighted the code where the error occurs - any ideas anyone?


Private Sub Workbook_SheetBeforedoubleclick(ByVal Tgt As Range, Cancel As Boolean)
'define the sheet name as a string
Dim CountryName As String
'make sure the target cell has a country name associated with it
If Cells(Tgt.Row, 4) <> "" And Tgt.Column < 21 Then
CountryName = Cells(Tgt.Row, 3)
'tell the user if there is a problem
Else
MsgBox "No valid metric selected"
End If
'ok, so we are looking for the contents of the double-clicked cell...
SearchVal1 = Cells(Tgt.Row, Tgt.Column)
' target may have a lot of decimal places so the search will fail
' so we need to include a second searchval with one decimal place
Cells(4, 1) = SearchVal1
SearchVal2 = Round(SearchVal1, 1)
Cells(4, 2) = SearchVal2
' then activate the correct worksheet where the value is to be searched for
Worksheets(CountryName).Activate
' search for the value and activate the cell
Cancel = False
' THIS IS THE PROBLEM SECTION:
Cells.Find(SearchVal2, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

 

[Richard Schollar]

Hi

Presumably happening as the Find is not finding a cell on the CountryName sheet with the required value. What do you want to happen in this case?

 

[DavidVernon]

Hi RichardScholar - thanks for looking at this.
I think the value exists (i can see it), is there a quick way to check whether the find code can see it?

 

[Richard Schollar]

I think you need to explicitly tell the code which sheet to look on for the Find to apply to the desired sheet and not the sheet in which the code is written. Also note that the sought for value must exist within the cell (I see you have used rounding so I hope the number within the cell is also rounded and not just formatted to not display all the decimal places as this won't then work).

Private Sub Workbook_SheetBeforedoubleclick(ByVal Tgt As Range, Cancel As Boolean)
'define the sheet name as a string
Dim CountryName As String
'make sure the target cell has a country name associated with it
If Cells(Tgt.Row, 4) <> "" And Tgt.Column < 21 Then
CountryName = Cells(Tgt.Row, 3)
'tell the user if there is a problem
Else
MsgBox "No valid metric selected"
End If
'ok, so we are looking for the contents of the double-clicked cell...
SearchVal1 = Cells(Tgt.Row, Tgt.Column)
' target may have a lot of decimal places so the search will fail
' so we need to include a second searchval with one decimal place
Cells(4, 1) = SearchVal1
SearchVal2 = Round(SearchVal1, 1)
Cells(4, 2) = SearchVal2
' then activate the correct worksheet where the value is to be searched for
With Worksheets(CountryName)
  ' search for the value and activate the cell
  Cancel = False
  ' THIS IS THE PROBLEM SECTION: 
  Set r = .Cells.Find(SearchVal2, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
  If Not r Is Nothing Then
    .Activate
    r.Activate
  End If
End With
End Sub

Code changes are shown in red.

 

[DavidVernon]

RichardScholar - thank you! this is working perfectly now - I was so close, yet so far, I may as well have been miles (and weeks) away from the solution.