# VBA code for search, find and pick the appropriate data

#### Anbuselvam K

##### Member
Hi

In the attached excel file have 3 worksheets called Permanent Formulas and the Temporary Formulas which are the source data and Master Data which is input data.

In the Sheet Master Data Range A9: B12 is the input data. With this input data, it has to find the appropriate formulas from both formulas sheets and the desired output comes as like the Master data range C9: H12

To achieve these results what kind of code I can write? Can you please suggest and give a start to finish the code by myself.

Sincerely Yours
Anbuselvam K

#### Attachments

• 10.6 KB Views: 8

#### Attachments

• 23.7 KB Views: 8
• Anbuselvam K

#### Marc L

##### Excel Ninja
Hi !​
According to the attachment another starter demonstration​
to paste to the Main worksheet module (or this worksheet must be active) :​
Code:
``````Sub Demo1()
Const S = "¤"
Dim N%, V, R&, C%, K\$
With CreateObject("Scripting.Dictionary")
For N = 2 To 3
V = Sheets(N).[A5].CurrentRegion.Value2
For R = 2 To UBound(V)
For C = 3 To UBound(V, 2):  .Item(V(R, 1) & S & V(R, 2) & S & V(1, C)) = V(R, C):  Next
Next
Next
V = [A8].CurrentRegion.Value2
For R = 2 To UBound(V)
For C = 3 To UBound(V, 2)
K = V(R, 1) & S & V(R, 2) & S & V(1, C)
If .Exists(K) Then V(R, C) = .Item(K)
Next
Next
.RemoveAll
End With
[A8].CurrentRegion.Value2 = V
End Sub``````
Do you like it ? So thanks to click on bottom right Like !​

• Anbuselvam K

#### Anbuselvam K

##### Member
Dear Vletm & Marc L

Both of your solutions are working well and good. I will implement this to my original file and I will contact you here If I am struck with the modifications.

Thanks a ton for your valuable and prompt support.

#### Marc L

##### Excel Ninja
As the Dictionary is only a Windows object, another demonstration in 'pure VBA' working on MAC & Windows :​
Code:
``````Sub Demo2()
Const S = "¤"
Dim N%, V, R&, C%, oDic As New Collection
For N = 2 To 3
V = Sheets(N).[A5].CurrentRegion.Value2
For R = 2 To UBound(V)
For C = 3 To UBound(V, 2):  oDic.Add V(R, C), V(R, 1) & S & V(R, 2) & S & V(1, C):  Next
Next
Next
With [A8].CurrentRegion
V = .Value2
'            On Error Resume Next
For R = 2 To UBound(V)
For C = 3 To UBound(V, 2)
V(R, C) = oDic(V(R, 1) & S & V(R, 2) & S & V(1, C))
Next
Next
'            On Error GoTo 0
.Value2 = V
End With
Set oDic = Nothing
End Sub``````
You may Like it !​

• Anbuselvam K

#### Anbuselvam K

##### Member
Dear Vletm
In the sample file, it is not allowing to add RM 7 and RM8... etc.

I have totally 55 RM and further, it will be an increase

can you please change the code or advice which part of the code to be changed to add the RM?

#### vletm

##### Excel Ninja
Anbuselvam K
Why did You give below kind of specs of this thread?
In the Sheet Master Data Range A9: B12 is the input data. With this input data, it has to find the appropriate formulas from both formulas sheets and the desired output comes as like the Master data range C9: H12 