Try the below.

With ActiveSheet

v_look_for = "3 (i.e. all week-end)"
v_replace_with = "3"
Selection.Replace What:=v_look_for, Replacement:=v_replace_with, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End With

If this post helps click Yes
---------------
Jacob Skaria


"BlueWolverine" wrote:

Hello,
MS ACCESS/EXCEL 2003 on XP PRO.

I am writing code in ACCESS to format an excel document exported from the
access file. I have an error.

v_look_for = "3 (i.e. all week-end)"
v_replace_with = 3
.Selection.Replace What:=v_look_for, Replacement:=v_replace_with, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

This returns an error
<<<Run-time error '9':
subscript out of range
The error occurs on the .selection line

It's not a TYPE issue because I have tried to make v_replace_with="3" as
well and it doesn't work. The range in excel that I have selected DEFINITELY
has exactly v_look_for in it. The rest of my code for embedding excel vba in
access is working. and here's my setup.

Set obj_excel = CreateObject("Excel.Application")
obj_excel.Visible = True
obj_excel.DisplayAlerts = False

Dim str_target As String, row_count As Long, str_target_col As String,
lcv As Long

With obj_excel

'do all sorts of excel stuff

end with


Any idea what's dying? Excel help says it's an array problem, but there's
no dimmed array to kill.

Thanks!

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!