Range R1C1 notation & the problem with my Macro

[skiptabor]

Ok here goes,

I am trying to write a Macro that will search a defined range for a
specified term. Once the term is located a defined value will be
written in column A (C1) on the same row the search term was located.

I keep getting "Runtime Error '1004': Method 'Range' of Object
'_Global' failed." I am definitely a VB novice although this isn't my
first macro, but I can't seem to figure out what I am doing wrong. I
worked on this for about three hours today and couldn't get it. I am
probably just doing some stupid little thing wrong, but it might be a
bigger problem than that. If anyone could help me with this I would
really appreciate it (and so would my boss.)

Thanks in advance.

Here is the code for reference:


Sub TheAttributer()

' Define Variables
Dim searchString As String
Dim attName As String
Dim searchRange As String
Dim d As Long


' Gather Variable Data by Popup Prompts
searchString = Application.InputBox(prompt:="Enter the string you wish
to search for", Type:=2)
attName = Application.InputBox(prompt:="Enter a name for this
attribute", Type:=2)
searchRange = Application.InputBox(prompt:="Enter A Cell Range to
search with in, i.e. x1:y2", Type:=2)


' Set range to be searched
With Worksheets("Product_Categories_5-25-05").Range(searchRange)

' Look for searchString in set range
Set c = .Find(What:=searchString, LookIn:=xlValues)

' If you don't find it keep going
If Not c Is Nothing Then
firstAddress = c.Address

' Write the defined value in col 1 on the same row that
contains search term
Do

d = c.Row
Range(Cells(d, 1)).Activate
ActiveCell.Value = attName
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress


End If
End With

End Sub

 

[Jim Cone]

Couple of things...

1. Declare the two variables you skipped:
Dim c as Range
Dim firstaddress as string

2. Change "Range(Cells(d, 1)).Activate" to _
Cells(d, 1).Activate

Jim Cone
San Francisco, USA

 

[skiptabor]

Thanks,

I had figured it out already, but you hit the nail on the head, havin
the range before the Cells was the problem. Doh. Again, thanks for th
help.

-SKi