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

 

[Rashidz]

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?

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



This line
Fnd.Value = Ary10(i)

 

[Fluff]

Try adding the line in red

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
   
   [COLOR=#ff0000]i = 0[/COLOR]
   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

 

[Rashidz]

Here is the error

 

[Fluff]

put

i=0

just before each line starting with

Ary10=Array

 

[Rashidz]

Try adding the line in red

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
   
   [COLOR=#ff0000]i = 0[/COLOR]
   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

Oh great it solve the problem.



Just another question.

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

Thanks

 

[Fluff]

Have a look at post#10

 

[Rashidz]

Once again thank you so much.
You help me reduce my work loads by so much.

 

[Fluff]

Glad to help & thanks for the feedback

 

[markmzz]

To reverse with formula, you can use this in C2 and copy down:

=IFERROR("DATA_"&CHOOSE(COUNTIF(DATA_10,B2)+COUNTIF(DATA_20,B2)*2+COUNTIF(DATA_30,B2)*3+1,1/0,10,20,30),"")​

Markmzz​