Posts by jonny

    I have two tables :


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]PN

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 107"]

    [tr]


    [TD="width: 107"]Model

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]SN74LVC1G08DCKR

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]TLV3492AIDCNT

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]TPS2031DRG4

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [/TABLE]


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Code

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Model

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]DCK

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Golf

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]DRG

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Bora

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]DCN

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Polo

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [/TABLE]


    The purpose is to get for each PN relevant Model, by partial string.

    Re: Syntax of including one SQL query within the other


    It's called nested queries:

    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

    Re: Syntax of including one SQL query within the other


    To all it may concern , here is the solution that I found:

    Code
    strQuery = "SELECT Sum(MyQuery.[CountOfAAA]) AS [SumOfCountOfAAA], Sum(MyQuery.[BBB1]) AS [SumOfSumOfBBB1] " & _
                    "FROM " & "(SELECT Count([AAA]) AS [CountOfAAA], Sum([BBB1]) AS [SumOfBBB1] " & _
                    "FROM [Sheet1$] " & _
                    "GROUP BY [BBB], [CCC], [DDD], [EEE], [FFF], [JJJ], [KKK] " & _
                    "HAVING ((([FFF])='YES') AND (([JJJ])='No') AND (([KKK])='Active'))) as MyQuery"
    
    
    Set rs = cn.Execute(strQuery)

    Guys,
    What's the write VBA syntax to include one query within the other?

    Code
    Query1 = "SELECT Count([AAA]) AS [CountOfAAA], Sum([BBB1]) AS [SumOfBBB1] FROM [Sheet1$] " & _
                      "GROUP BY [BBB], [CCC], [DDD], [EEE], [FFF], [JJJ], [KKK] " & _
                      "HAVING ((([FFF])='YES') AND (([JJJ])='No') AND (([KKK])='Active'))
        Set Query1_rs = cn.Execute(Query1)
       
        strQuery = "SELECT Sum([CountOfAAA]) AS [SumOfCountOfAAA], Sum([SumOfBBB1]) AS [SumOfSumOfBBB1] FROM Query1_rs"
        Set rs = cn.Execute(strQuery)


    The second query here does not work, what I made wrong?

    Required to compare two excel files. File2 must fully match File1.


    -1st compare criteria: all data in column "A" of File1 exists in column "B" of File2.
    -2nd compare criteria: for data that exists, check that combination of all data in column "A", "B" of File1 exists in column "B","D" of File2.
    -3rd compare criteria: for data that exists, check that combination of all data in column "A", "B","C" of File1 exists in column "B","D","E" of File2.
    -4th compare criteria: for data that exists, check that combination of all data in column "A", "B","C","D" of File1 exists in column "B","D","E","G" of File2


    Any non-conformance from each criteria must be indicated in report.


    Whoever is interesting , I will send more details including excel files and sample report.

    Hi guys,
    Could you please help me with the code, getting mess there..
    I'm trying to create an event, if data validation cell changes to "Yes" in "models" sheet being creating number of rows according to relevant qty.

    Re: User defined function of hyperlink to another worksheet


    It doesn't do what I need,
    I will try to elaborate:
    1. In column "A" I have cars models: "A1" = Toyota , "A2" = Honda, ...
    2. Also I have worksheets with same names: Toyota, Honda, ...
    What I need is to run function , will take me to Toyota worksheet, by pressing cell "B1" (or to Honda worksheet, by pressing cell "B2")

    I am writing function that creating Hyperlink to certain worksheet:
    Could you please help and replace with variables?

    Code
    Public Function HyperlinkToTab(Word As String, r As Range)
       ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'102300117'!A1", TextToDisplay:="press"        
    '    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",  SubAddress:=" & r&" & "&r& ", TextToDisplay:=Word
    End Function


    102300117 is a name of worksheet and a same as cell "A1"

    I have following code with static range.
    The letter "D" in the range must be a letter of last right column.

    Code
    Sub CreateChart()
        Range("C1:D1,C3:D6").Select   
        Range("C3").Activate
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=Range("'Report'!$C$1:$D$1,'Report'!$C$3:$D$6")
        ActiveChart.ChartType = xl3DColumnStacked100
        ActiveChart.PlotBy = xlRows
    End Sub


    How can I change this code to dynamic range. I tryed using XlToRight, but I have confused with that.
    Any idea?

    Re: "Object Required" error when using Collections


    You're right , that's a second mistake.
    But actually the error caused by single "New Collection" for few collections.
    Once I changed from:

    Code
    Dim BuyUniqueZero, BuyUniqueSole As New Collection


    to

    Code
    Dim BuyUniqueZero As New Collection
        Dim BuyUniqueSole As New Collection


    It solved!