Hi all,
This has been quite a wonderful forum and great place to learn.
I have the below problem which I require some advice.
1. I am trying to consolidate the sales of a few items from 3 different sources (3 different worksheets)
2. These 3 different worksheets name all these items differently.
3. What I did was I wrote a simple macro to find and replace all these different variations into one consistent naming
4. The issue is I used arrays in my macro and there are way too many items to cater for
5. Like to ask if there is an easier way to do it if I continue with the find and replace method?
6. Or if there is even an easier solution to tackle this (without using find and replace)?
Below are my working files and VBA text. Thank you sirs in advance
Working files
Working file A
[TABLE="width: 144"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orangie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Applie[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Pineapplie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Working File B
[TABLE="width: 128"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]applie[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orange2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Pineapp[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Apple2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Mago[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Orangie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mannngo[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Working File3
[TABLE="width: 164"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Pineap[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orange2[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Pineapp[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Apple3[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Apple4[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Mangooo[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Orangie[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Mannngo[/TD]
[TD="align: right"]27[/TD]
[/TR]
</tbody>[/TABLE]
Desired output
all combined into one file
[TABLE="width: 162"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]
Macro text
Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("Apple1", "apple2", "mago", "Orangie")
rplcList = Array("Apple", "Apple", "Mango", "Orange")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
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
This has been quite a wonderful forum and great place to learn.
I have the below problem which I require some advice.
1. I am trying to consolidate the sales of a few items from 3 different sources (3 different worksheets)
2. These 3 different worksheets name all these items differently.
3. What I did was I wrote a simple macro to find and replace all these different variations into one consistent naming
4. The issue is I used arrays in my macro and there are way too many items to cater for
5. Like to ask if there is an easier way to do it if I continue with the find and replace method?
6. Or if there is even an easier solution to tackle this (without using find and replace)?
Below are my working files and VBA text. Thank you sirs in advance
Working files
Working file A
[TABLE="width: 144"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orangie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Applie[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Pineapplie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Working File B
[TABLE="width: 128"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]applie[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orange2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Pineapp[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Apple2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Mago[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Orangie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mannngo[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Working File3
[TABLE="width: 164"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Pineap[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orange2[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Pineapp[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Apple3[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Apple4[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Mangooo[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Orangie[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Mannngo[/TD]
[TD="align: right"]27[/TD]
[/TR]
</tbody>[/TABLE]
Desired output
all combined into one file
[TABLE="width: 162"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]
Macro text
Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("Apple1", "apple2", "mago", "Orangie")
rplcList = Array("Apple", "Apple", "Mango", "Orange")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
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