Replacing Text evenly from 1 sheet to another sheet

[Rashidz]

Hi,

I need help for creating a macro that can replace text on Sheet 1 to the text on Sheet 2. (see attachment file) Replacing data equally.xlsx


Eg.

In sheet 1

Part 1, it consists of
Data 10
Data 15
Data 35
Data 20
Data 30
Data 10

Part 2, it consists of
Data 35
Data 50
Data 10
Data 15
Data 30

Part 3, it consists of
Data 30
Data 32
Data 10
Data 15
Data 10



In sheet 2

It consists of
10 LADY
10 CHILDREN
10 MAN
10 HAZMAT

30 BROADER
30 INSPIRATIONAL
30 STORY MATTER

How can I replace all DATA 10 to 10 LADY, 10 CHILDREN, 10 MAN and 10 HAZMAT rotate evenly over all the PART of sheet 1
and also replace DATA 30 with 30 BROADER, 30 INSPIRATIONAL and 30 STORY MATTER rotate evenly over all the PART of sheet 1.

P.S. There are more that 10 parts, I'm giving example for 3 parts only.

I tried this formula but dont really work my cases.

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array("DATA 10")
rplcList = Array("10 LADY", "10 CHILDREN", "10 MAN", "10 HAZMAT")


For x = LBound(fndList) To UBound(fndList)

For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht

Next x

End Sub



Please help.

Thanks