Replacing one value to multiple values in rotations

[Rashidz]

Hi I would like to replace one value with multiple values in rotation,

In sheet 1, I have a value "DATA 10" in column A, row 2, 5, 7, 10, 15, 20, 23 and so on.

How can i replace this value (DATA 10) with multiple values (10 LADY, 10 CHILDREN, 10 MAN and 10 HAZMAT) in rotation, so that

row 2 will be replace to 10 LADY
row 5 = 10 CHILDREN
row 7 = 10 MAN
row 10 = 10 HAZMAT
row 15 = 10 LADY
row 20 = 10 CHILDREN
row 23 = 10 MAN
and so on in rotation


Thank you in advance,

Rashidz

 

[Special-K99]

1. Formula or VBA solution ?

2. Regardiing the phrase "and so on"

Your data starts on row 2 and the row increases with the following steps +3, +2, +3, +5, +5
The next row increase by +3
So is that sequence of +3 +2 +3 +5 +5 repeating again or is it something different?

 

[Rashidz]

1. Formula or VBA solution ?

2. Regardiing the phrase "and so on"

Your data starts on row 2 and the row increases with the following steps +3, +2, +3, +5, +5
The next row increase by +3
So is that sequence of +3 +2 +3 +5 +5 repeating again or is it something different?

1. Well it can be a formula or VBA, whichever works

2. "and so on" mean these data will reach till rows 100

There is no patterns to these step "+3, +2, +3, +5". It can be anywhere in the rows. its not repeating.

To be precise, in my "DATA" sheet rows, I have "DATA 10", "DATA 20" and "DATA 30"

For DATA 10, i need to replace with these:

10 LADY
10 CHILDREN
10 MAN
10 HAZMAT

For DATA 20, need to replace with these:

20 GLOBAL
20 EQUALITY
20 IN DEPTH

And for DATA 30. need to replace with these:

30 BROADER
30 INSPIRATIONAL
30 STORY MATTER

All each DATA need to be replace as a rotation, one after another.

Thanks

 

[Fluff]

Hi & welcome to the board.

This should do your DATA 10

Sub replaceData()

   Dim Ary10 As Variant
   Dim Cnt As Long
   Dim Qty As Long
   Dim Fnd As Range
   Dim i As Long
   
   Ary10 = Array("10 LADY", "10 CHILDREN", "10 MAN", "10 HAZMAT")
   Qty = WorksheetFunction.CountIf(Columns(1), "DATA 10")
   For Cnt = 1 To Qty
      Set Fnd = Columns(1).Find("DATA 10", , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Value = Ary10(i)
         i = i + 1
         If i > UBound(Ary10) Then i = 0
      End If
   Next Cnt

End Sub

 

[Rashidz]

Hi & welcome to the board.

This should do your DATA 10

Sub replaceData()

   Dim Ary10 As Variant
   Dim Cnt As Long
   Dim Qty As Long
   Dim Fnd As Range
   Dim i As Long
   
   Ary10 = Array("10 LADY", "10 CHILDREN", "10 MAN", "10 HAZMAT")
   Qty = WorksheetFunction.CountIf(Columns(1), "DATA 10")
   For Cnt = 1 To Qty
      Set Fnd = Columns(1).Find("DATA 10", , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Value = Ary10(i)
         i = i + 1
         If i > UBound(Ary10) Then i = 0
      End If
   Next Cnt

End Sub

Thank you so much Fluff. This is what I actually need. Perfect.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Rashidz]

Glad we could help & thanks for the feedback

Hi Fluff,

Just another question.

How do I reverse this formula, from "10 LADY", "10 CHILDREN", "10 MAN", "10 HAZMAT" to "DATA 10".

Thanks

 

[Rashidz]

Hi & welcome to the board.

This should do your DATA 10

Sub replaceData()

   Dim Ary10 As Variant
   Dim Cnt As Long
   Dim Qty As Long
   Dim Fnd As Range
   Dim i As Long
   
   Ary10 = Array("10 LADY", "10 CHILDREN", "10 MAN", "10 HAZMAT")
   Qty = WorksheetFunction.CountIf(Columns(1), "DATA 10")
   For Cnt = 1 To Qty
      Set Fnd = Columns(1).Find("DATA 10", , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Value = Ary10(i)
         i = i + 1
         If i > UBound(Ary10) Then i = 0
      End If
   Next Cnt

End Sub

I actually try to add more to this formula but it gives me error (Run-Time error '9': Subscript out of range).
Is it possible to add more to it like the one below?

Sub replaceData()

   Dim Ary10 As Variant
   Dim Cnt As Long
   Dim Qty As Long
   Dim Fnd As Range
   Dim i As Long
   
   Ary10 = Array("10 LADY", "10 CHILDREN", "10 MAN", "10 HAZMAT")
   Qty = WorksheetFunction.CountIf(Columns(1), "DATA 10")
   For Cnt = 1 To Qty
      Set Fnd = Columns(1).Find("DATA 10", , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Value = Ary10(i)
         i = i + 1
         If i > UBound(Ary10) Then i = 0
      End If
   Next Cnt

   Ary10 = Array("30 STORY MATTER", "30 CLIMATE CHANGE", "30 INSPIRATIONAL", "30 PERSPECTIVE")
   Qty = WorksheetFunction.CountIf(Columns(1), "DATA 30")
   For Cnt = 1 To Qty
      Set Fnd = Columns(1).Find("DATA 30", , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Value = Ary10(i)
         i = i + 1
         If i > UBound(Ary10) Then i = 0
      End If
   Next Cnt

End Sub

 

[markmzz]

Another way (with formulas in a new column - if possible):

1) Create the tables DATA10, DATA20 and DATA30 with the names DATA_10, DATA_20 and DATA_30


2) Put the formula below in B2 and copy down. And then, copy the range B2:B19 to the range E2:E19 and to the range H2:H19:

=IF(A2="","",IFERROR(VLOOKUP(MOD(COUNTIF(A$2:A2,A2)-1,
MAX(INDEX(INDIRECT(SUBSTITUTE(A2," ","_")),,1)))+1,
INDIRECT(SUBSTITUTE(A2," ","_")),2,0),""))

[TABLE="class: grid, width: 859"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data[/TD]
[TD]New Data[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]New Data[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]New Data[/TD]
[TD][/TD]
[TD][/TD]
[TD]Tables[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DATA 10[/TD]
[TD]10 LADY[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 GLOBAL[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 BROADER[/TD]
[TD][/TD]
[TD]Range Name[/TD]
[TD="align: right"]1[/TD]
[TD]10 LADY[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DATA_10[/TD]
[TD="align: right"]2[/TD]
[TD]10 CHILDREN[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DATA 10[/TD]
[TD]10 CHILDREN[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 EQUALITY[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 INSPIRATION[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]10 MAN[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]10 HAZMAT[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]DATA 10[/TD]
[TD]10 MAN[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 IN DEPTH[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 STORY MATTER[/TD]
[TD][/TD]
[TD]Range Name[/TD]
[TD="align: right"]1[/TD]
[TD]20 GLOBAL[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DATA_20[/TD]
[TD="align: right"]2[/TD]
[TD]20 EQUALITY[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]DATA 10[/TD]
[TD]10 HAZMAT[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 GLOBAL[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 BROADER[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]20 IN DEPTH[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Range Name[/TD]
[TD="align: right"]1[/TD]
[TD]30 BROADER[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DATA_30[/TD]
[TD="align: right"]2[/TD]
[TD]30 INSPIRATION[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]DATA 10[/TD]
[TD]10 LADY[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 EQUALITY[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 INSPIRATION[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]30 STORY MATTER[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]DATA 10[/TD]
[TD]10 CHILDREN[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 IN DEPTH[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 STORY MATTER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]DATA 10[/TD]
[TD]10 MAN[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 GLOBAL[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 BROADER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]DATA 10[/TD]
[TD]10 HAZMAT[/TD]
[TD][/TD]
[TD]DATA 20[/TD]
[TD]20 EQUALITY[/TD]
[TD][/TD]
[TD]DATA 30[/TD]
[TD]30 INSPIRATION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]********[/TD]
[TD]************[/TD]
[TD]**[/TD]
[TD]********[/TD]
[TD]************[/TD]
[TD]**[/TD]
[TD]********[/TD]
[TD]****************[/TD]
[TD]**[/TD]
[TD]************[/TD]
[TD]******[/TD]
[TD]*****************[/TD]
[/TR]
</tbody>[/TABLE]


I hope this helps.

Markmzz

 

[Fluff]

This will reverse the changes

Sub ReverseChange()
   Dim Ary10 As Variant
   Dim Cnt As Long
   
   Ary10 = Array("10 LADY", "10 CHILDREN", "10 MAN", "10 HAZMAT")
   For Cnt = 0 To UBound(Ary10)
      Columns(1).Replace Ary10(Cnt), "DATA 10", , , False, , False, False
   Next Cnt

End Sub

The code you supplied in post#8 works for me. Which line gave the error?