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:

[vba]
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
[/vba]

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

[skiptabor]

Thanks,

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

-SKip