Range R1C1 notation & the problem with my Macro
 

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


--
skiptabor
------------------------------------------------------------------------
skiptabor's Profile: http://www.excelforum.com/member.php...o&userid=23732
View this thread: http://www.excelforum.com/showthread...hreadid=374056

Range R1C1 notation & the problem with my Macro
 

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"
wrote in messagenews:skiptabor.1plyn6_1117059008.8099@excel forum-nospam.com...

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

skiptabor

Range R1C1 notation & the problem with my Macro
 

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

--
skiptabo
-----------------------------------------------------------------------
skiptabor's Profile: http://www.excelforum.com/member.php...fo&userid=2373
View this thread: http://www.excelforum.com/showthread.php?threadid=37405