Posts by jonny

Re: Searching partial string

Three rows given only for a reference. The original file is huge with a thousand of rows.
I would prefer to have a function , equivalent to VlookUp. So finally I will get :
[TABLE="width: 233"]

PN

Model

SN74LVC1G08DCKR

Golf

TLV3492AIDCNT

Polo

TPS2031DRG4

Bora

[/TABLE]

Re: Searching partial string

Thanks , but I'm looking for a general solution.
There are thousands rows in the file.

I have two tables :

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

[TABLE="width: 155"]

[TD="width: 155"]PN

[/TABLE]
[/TD]

[TABLE="width: 107"]

[TD="width: 107"]Model

[/TABLE]
[/TD]

[TABLE="width: 155"]

[TD="width: 155"]SN74LVC1G08DCKR

[/TABLE]
[/TD]

[TABLE="width: 155"]

[TD="width: 155"]TLV3492AIDCNT

[/TABLE]
[/TD]

[TABLE="width: 155"]

[TD="width: 155"]TPS2031DRG4

[/TABLE]
[/TD]

[/TABLE]

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

[TABLE="width: 64"]

[TD="width: 64"]Code

[/TABLE]
[/TD]

[TABLE="width: 64"]

[TD="width: 64"]Model

[/TABLE]
[/TD]

[TABLE="width: 64"]

[TD="width: 64"]DCK

[/TABLE]
[/TD]

[TABLE="width: 64"]

[TD="width: 64"]Golf

[/TABLE]
[/TD]

[TABLE="width: 64"]

[TD="width: 64"]DRG

[/TABLE]
[/TD]

[TABLE="width: 64"]

[TD="width: 64"]Bora

[/TABLE]
[/TD]

[TABLE="width: 64"]

[TD="width: 64"]DCN

[/TABLE]
[/TD]

[TABLE="width: 64"]

[TD="width: 64"]Polo

[/TABLE]
[/TD]

[/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:

Re: Syntax of including one SQL query within the other

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

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)

Re: Syntax of including one SQL query within the other

The 2nd query is based on recordset of 1st query.
Any thought of how to combine?

The right VBA syntax to include one SQL query within the other

.

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

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.

Re: Data validation event

I've added the file with more data, could you comment please ..

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.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim QTY As Integer




    If Not Intersect(Target, Range("Produced")) Is Nothing And Target.Value = "Yes" Then
        QTY = Target.Offset(0, -1).Value
        numRows = Worksheets("Models").Range("Models").Rows.Count
        numColumns = Worksheets("Models").Range("Models").Columns.Count




        Worksheets("Models").Range("Models").Resize Range("$A$1:$F$" & numRows + QTY)
    End If
End Sub

Re: Can't apply "Text To Columns" delimited by space

This is an automatically exported report. Is any way to fix this by macro?

Can someone understand , why can't I apply text functions on strings into the file?
For example: can't apply "Text To Columns" delimited by space on "Status Entry Date" column.

Re: Embedding a file into protected worksheet

Here is the OLEObject:

[/B]ActiveSheet.OLEObjects.Add(Filename:=SelectedFile, DisplayAsIcon:=True, Left:=800.75, Top:=71, Width:=93, Height:=22.5).Select[B]

[/B]Thanks, it's working!

I am using

ActiveSheet.OLEObjects.Add

to embed a file into the worksheet.
If the file is protected , I'm getting 'Error 1004'.
How it can be solved?

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?

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"

Re: Turn static range into a dynamic range

That's not good enough , I need something in VBA code with RANGE...XlToRight...
Can't use CurrentRegion here.
Any idea?

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

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:

Dim BuyUniqueZero, BuyUniqueSole As New Collection

to

Dim BuyUniqueZero As New Collection
    Dim BuyUniqueSole As New Collection

It solved!