• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code for search, find and pick the appropriate data

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.

Thanks in Advance.

Sincerely Yours
Anbuselvam K
 

Attachments

  • VBA Model File.xlsx
    10.6 KB · Views: 8
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 !​
 
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.
 
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
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
Those are Your made limits ...
In the end of Your writing was:
Can you please suggest and give a start to finish the code by myself.
... myself ... didn't it mean ... You ... who would to finish the code?
> Modify as below ...
Screenshot 2019-08-26 at 18.39.39.png
 
Last edited:
Back
Top