vba code to search and find values in multiple sheet

[Dalia]

Hi,

Attached is the sample file. Below is the instruction
corresponding to the date in" report" data we need to match the date with number in column A for every sheet
Note that for the same date temparature,humidity and sky remains same in each sheet
now take A6 from the merge cell and search it in report
like that search A5 and A5B in report sheet
then enter the data Eg. for 15 sep 2016 in report sheet we will take the number 15 here and enter the data
as there are two 15-sep for A6 we need to add the sales and time to get the total volume and time
like that we need to go to each sheet and perform the same procedure

 

[jindon]

Try

Option Explicit

Sub test()
    Dim a, i As Long, ii As Long, dic As Object, w, myDay As Long
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With Sheets("report")
        a = .Range("a3:g" & .Range("b" & Rows.Count).End(xlUp).Row).Value
        For i = 1 To UBound(a, 1)
            If a(i, 2) <> "" Then
                If a(i, 1) = "" Then a(i, 1) = a(i - 1, 1)
                If Not dic.exists(a(i, 1)) Then Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
                myDay = Day(a(i, 2))
                If Not dic(a(i, 1)).exists(myDay) Then
                    ReDim w(1 To 5)
                    For ii = 1 To 3
                        w(ii) = a(i, ii + 2)
                    Next
                Else
                    w = dic(a(i, 1))(myDay)
                End If
                For ii = 4 To 5
                    w(ii) = w(ii) + a(i, ii + 2)
                Next
                dic(a(i, 1))(myDay) = w
            End If
        Next
    End With
    OutPutToWS dic
End Sub

Private Sub OutPutToWS(dic As Object)
    Dim ws As Worksheet, a, i As Long, ii As Long, iii As Long
    For Each ws In Worksheets
        If ws.Name <> "Report" Then
            With ws.Range("a3", ws.Cells.SpecialCells(11))
                a = .Value
                For ii = 1 To UBound(a, 2)
                    If dic.exists(a(1, ii)) Then
                        For i = 10 To UBound(a, 1)
                            If dic(a(1, ii)).exists(a(i, 1)) Then
                                For iii = 1 To 3
                                    a(i, iii + 1) = dic(a(1, ii))(a(i, 1))(iii)
                                Next
                                For iii = 0 To 1
                                    a(i, ii + iii) = dic(a(1, ii))(a(i, 1))(iii + 4)
                                Next
                            End If
                        Next
                    End If
                Next
                .Value = a
            End With
        End If
    Next
End Sub

 

[Dalia]

Hi,
Thank you. The code is working in the sample file but not working in my main file. Can you please make me understand how u did it. Am not so aware about dictionary thing. I need your help to understand how the variable a is working.a(i,2) how is it working. Is "a" carrying any value here or it is referring to cells command.

 

[jindon]

I don't understand how deep you know about vba, so I just give you a suggestion.

Google the key words like
VBA Array Dictionary object

If you get basic idea of those, ask where you specifically don't understand.

 

[Marc L]

Hi !​

The code is working in the sample file but not working in my main file.

So the clever idea when creating a thread is to shared
a sample workbook reflecting exactly the working one !

You can also search for Dictionary in VBA inner help …
​