Replacing one value to multiple values in rotations

R

Rashidz

New Member
Joined
Feb 8, 2018
Messages
9
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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?
 
Upvote 0
Special-K99 said:
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?
Click to expand...

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
 
Upvote 0
Hi & welcome to the board.

This should do your DATA 10
Code: 
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
 
Upvote 0
Fluff said:
Hi & welcome to the board.

This should do your DATA 10
Code: 
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
Click to expand...

Thank you so much Fluff. This is what I actually need. Perfect.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Fluff said:
Hi & welcome to the board.

This should do your DATA 10
Code: 
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
Click to expand...

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?

Code: 
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
 
Upvote 0
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
 
Upvote 0
This will reverse the changes
Code: 
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Formula / Script to do Find and Replace for Multiple Groups of 10 rows replacing with different words / phrase each time
Replies
5
Views
220
hagia_sofia
hagia_sofia
R
  • Solved
Replace cell reference
Replies
1
Views
325
dreid1011
dreid1011
R
  • Question Question
Data Presentation #SPILL
Replies
4
Views
119
rcocrane99
R
S
  • Solved
Split numbers in one column to multiple columns
Replies
3
Views
229
sharshra
S
L
  • Question Question
Compile Error when Adding Attachments
Replies
5
Views
202
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top