Macro to replace rows sequentially 1

[JohnnyG23]

Hello, I have an excel workbook with some messed up values. I am trying to replace the number in the parenthesis with a value, starting at one and continuing until the data stops. The data is as follows from Cells B, C, and D on Rows 3, 4, 5, and 6:

A1A'SP (311)'!I1 A1A'SP (311)'!C49 A1A'SP (311)'!C50...
A1A'SP (16)'!I1 A1A'SP (16)'!C49 A1A'SP (16)'!C50...
A1A'SP (16)'!I1 A1A'SP (16)'!C49 A1A'SP (16)'!C50...
A1A'SP (16)'!I1 A1A'SP (16)'!C49 A1A'SP (16)'!C50...

The data starts at column B and at Row 3. I was hoping to get it to look like this:

A1A'SP (1)'!I1 A1A'SP (1)'!C49 A1A'SP (1)'!C50...
A1A'SP (2)'!I1 A1A'SP (2)'!C49 A1A'SP (2)'!C50...
A1A'SP (3)'!I1 A1A'SP (3)'!C49 A1A'SP (3)'!C50...
A1A'SP (4)'!I1 A1A'SP (4)'!C49 A1A'SP (4)'!C50...

If any more information is needed please ask and thanks for any help.

 

[SkipVought]

Hi,

Dim r As Range, i As Integer, c  As Range, a1, a2

i = 1
For Each r In Range([A3], [A3].End(xlDown))
  For Each c In Range([A3], [A3].End(xlToRight))
    With Cells(r.Row, c.Column)
       a1 = Split(.Value, "(")
       a2 = Split(a1(1), ")")
       .Value = a1(0) & "(" & i & ")" & a2(1)
    End With
  Next
  i = i + 1
Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JohnnyG23]

Wow, thanks. I appreciate that. The line:

a2 = Split(a1(1), ")")

is returning a run-time error '9', subscript out of range. I'm not really sure what my subscript is so I have no idea on which value to change. I'm guessing it deals with the value a1, not too sure though. Any idea which part of that line is giving the error? Thanks.

 

[SkipVought]

Since you did not make it clear what data was in what column, my assumption was...
[tt]
A B C
A1A'SP (1)'!I1 A1A'SP (1)'!C49 A1A'SP (1)'!C50
A1A'SP (2)'!I1 A1A'SP (2)'!C49 A1A'SP (2)'!C50
A1A'SP (3)'!I1 A1A'SP (3)'!C49 A1A'SP (3)'!C50
A1A'SP (4)'!I1 A1A'SP (4)'!C49 A1A'SP (4)'!C50
[/tt]
Just tested and runs correctly with this assumption.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JohnnyG23]

Oh, right sorry about that. I just noticed in my first post I put cell instead of column. Ok, well I fixed it and it works now. Thanks so much for the help.

 

[SkipVought]

And i see where you did say B, C & D, where I used A as the first column. Sorry!

Just change A3 to B3

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]