Posts by Domenic

Try the following code . . .

    Dim MyChart As Chart
    Dim SourceDataArray As Variant
    Dim lpos As Long
    Dim tpos As Long
    Dim i As Long
    
    'left position in points
    lpos = 200
    
    'top position in points
    tpos = 50
    
    SourceDataArray = Array("A1:B6", "A9:B14", "A17:B22")
    
    For i = LBound(SourceDataArray) To UBound(SourceDataArray)
        Set MyChart = ActiveSheet.Shapes.AddChart2(225, xlColumnClustered, lpos, tpos, 200, 200, 1).Chart
        MyChart.SetSourceData Source:=Range(SourceDataArray(i))
        lpos = lpos + 200
    Next i

Hope this helps!

To filter for values that start with "F" or values that contain 4 characters starting with "VMM", try...

.AutoFilter Field:=113, Criteria1:="F*", Operator:=xlOr, Criteria2:="VMM?"

Or do you specifically want to use a list as part of the criteria?

So it looks like you're using the code that clicks the link. If you simply want check whether the link was found and clicked, you can use a Boolean variable to keep track of the status...

Dim HTMLLink As MSHTML.HTMLAnchorElement
    Dim bClicked As Boolean


    bClicked = False
    For Each HTMLLink In HTMLDoc.getElementsByTagName("a")
        If HTMLLink.innerText = "Cash Flow" Then
            HTMLLink.Click
            bClicked = True
            Exit For
        End If
    Next HTMLLink


    If bClicked Then
        MsgBox "Link found and clicked...", vbInformation
    Else
        MsgBox "Link not found...", vbInformation
    End If

Here's an example that loops through each link, checks for the text "Cash Flow", and then clicks the link...

Dim HTMLLink As MSHTML.HTMLAnchorElement


    For Each HTMLLink In HTMLDoc.getElementsByTagName("a")
        If HTMLLink.innerText = "Cash Flow" Then
            HTMLLink.Click
            Exit For
        End If
    Next HTMLLink

But this simply brings you to the table located at the bottom of the same page. If you want to access the information in that table, you can loop through each table on the page, and check for the text "Cash from Operating Activity" from the second row...

Dim HTMLTable As MSHTML.HTMLTable


    For Each HTMLTable In HTMLDoc.getElementsByTagName("table")
        If HTMLTable.getElementsByTagName("tr")(1).Cells(0).innerText = "Cash from Operating Activity" Then
            'Do stuff
            Exit For
        End If
    Next HTMLTable

Actually, instead of accessing the information from within this loop, you can exit it first, and then do so...

Dim HTMLTable As MSHTML.HTMLTable


    For Each HTMLTable In HTMLDoc.getElementsByTagName("table")
        If HTMLTable.getElementsByTagName("tr")(1).Cells(0).innerText = "Cash from Operating Activity" Then
            Exit For
        End If
    Next HTMLTable


    If Not HTMLTable Is Nothing Then
        'Do stuff
    Else
        MsgBox "The Cash Flow table wasn't found.", vbInformation
    End If

Hope this helps!

Try using the AutoFilter instead...

Sub DeleteNonName()


    With ActiveSheet
        If .FilterMode Then .ShowAllData
        With .UsedRange
            .AutoFilter field:=128, Criteria1:="<>Name"
            .Offset(1, 0).EntireRow.Delete
            .AutoFilter
        End With
    End With


End Sub

Hope this helps!

Re: VBA Array Furmula to Populate TextBox Data

Assuming that the criteria are text values, try...

Win = Evaluate("COUNTIFS('2008 - 2015'!B:B,""" & TB1 & """,'2008 - 2015'!D:D,""" & TB2 & """,'2008 - 2015'!H:H,""Win"",'2008 - 2015'!F:F,"""",'2008 - 2015'!G:G,"""")")

For numerical values, you'll need to remove the quotes from the criteria. So, for example, let's say that the criteria for Column D is a numerical value, you would need to replace...

""" & TB2 & """

with

" & TB2 & "

Hope this helps!

Re: VBA Array Furmula to Populate TextBox Data

Here's a shortened version...

Private Sub ComboBox1_Change()


    Dim Visitor As Variant
    Dim Home As Variant
    Dim i As Integer
    
    ComboBox1.Value = Format(ComboBox1, "dd-mmm-yy")
    
    Sheets("Basic Data").Range("E1").Value = ComboBox1.Value
    
    For i = 1 To 15
        Visitor = Evaluate("INDEX('2008 - 2015'!$A$1:$B$9996,SMALL(IF('Basic Data'!$E$1='2008 - 2015'!$A$1:$A$9996,ROW('2008 - 2015'!$A$1:$A$9996))," & i & "),2)")
        If IsError(Visitor) Then
            Me.Controls("TextBox" & i).Text = ""
        Else
            Me.Controls("TextBox" & i).Text = Visitor
        End If
    Next i
    
    For i = 1 To 15
        Home = Evaluate("INDEX('2008 - 2015'!$A$1:$D$9996,SMALL(IF('Basic Data'!$E$1='2008 - 2015'!$A$1:$A$9996,ROW('2008 - 2015'!$A$1:$A$9996))," & i & "),4)")
        If IsError(Home) Then
            Me.Controls("TextBox" & 15 + i).Text = ""
        Else
            Me.Controls("TextBox" & 15 + i).Text = Home
        End If
    Next i


End Sub

Hope this helps!

Re: VBA Array Furmula to Populate TextBox Data

Try...

Private Sub ComboBox1_Change()


    Dim Visitor As Variant
    
    ComboBox1.Value = Format(ComboBox1, "dd-mmm-yy")
    
    Sheets("Basic Data").Range("E1").Value = ComboBox1.Value
    
    Visitor = Evaluate("INDEX('2008 - 2015'!$A$1:$B$9996,SMALL(IF('Basic Data'!$E$1='2008 - 2015'!$A$1:$A$9996,ROW('2008 - 2015'!$A$1:$A$9996)),1),2)")
    
    If IsError(Visitor) Then
        TextBox1.Text = ""
    Else
        TextBox1.Text = Visitor
    End If
    
End Sub

Hope this helps!

Re: Excel VBA SUM Specific Fields in Collumn

Assuming that Sheet1 contains the column with the category fields, the following will place the total count for each each in B2:B4 of the sheet named National...

Sub test()


    Dim rCriteriaRange As Range
    
    Set rCriteriaRange = Worksheets("Sheet1").Columns("B")
    
    With Worksheets("National")
        .Range("B2").Value = Application.CountIf(rCriteriaRange, "Master")
        .Range("B3").Value = Application.CountIf(rCriteriaRange, "Senior")
        .Range("B4").Value = Application.CountIf(rCriteriaRange, "Junior")
    End With
    
End Sub

Hope this helps!

Re: it's possible no copy the macro on backup???

Once you've initially saved your file, save it again as an .xlsx file under a different name (ie. Backup of Filename.xlsx)...

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "c:\path\backup of filename.xlsx", xlOpenXMLWorkbook
Application.DisplayAlerts = True

Any VBA code will automatically be removed.

Hope this helps!

Re: display all objects and properties in a collection

Have a look at the attached sample workbook. I'm rushing out, so I didn't have the time to include comments. If you have any questions, I'll try to answer them when I get a chance.

Re: Trying to Extract everthing before or after a certain phrase

Quote from shawna98;745094

That solved it! I am not familiar with LOOKUP(2^15, ... what is this telling the formula to do?

When the lookup value for LOOKUP is a number and the number is greater than any other number in the lookup array, the last numerical value is returned. In this case, the lookup value is the number 2^15. And the array of values is returned by FIND. So the lookup number will always be greater than any number returned by FIND since the maximum characters allowed in a cell is 32,767 (the number 2^15 equates to 32,768 and, therefore, greater than the maximum of 32,767). Note that here LOOKUP ignores error values.

Quote

Thank you!!!!!!!

You're very welcome!

Re: Trying to Extract everthing before or after a certain phrase

Try...

=IFERROR(LEFT(F4,LOOKUP(2^15,FIND({"APT","STE","SUITE"},F4,1))-1),F4)

Hope this helps!

Re: VB code for add folder from exelsheet to outlook

You're very welcome. Glad I could help.

Cheers!

Re: VB code for add folder from exelsheet to outlook

Do you mean that you'd like to add those folders to "In Behandeling", which is located in "Loket ASR SK Pensioenen Run"? If so, try...

Set olMainFolder = olNS.Folders("Loket ASR SK Pensioenen Run").Folders("In Behandeling")

Re: VB code for add folder from exelsheet to outlook

In that case, try replacing...

Set olMainFolder = olNS.Folders("Private Folders")

with

Set olMainFolder = olNS.Folders("Loket ASR SK Pensioenen Run")

Does this help?

Re: VB code for add folder from exelsheet to outlook

Quote from Marktheshark;744615

I don't get any message back when i enter the code:

[ATTACH=CONFIG]64999[/ATTACH]

As I have already mentioned, that line of code must be entered in the Immediate Window. From the menu in the Visual Basic Editor, select "View > Immediate Window". Or, alternatively, press Ctrl+R. This should bring up a small window, if it's not already visible. Type that line of code in that window, and press ENTER.

See the attached file...

Re: VB code for add folder from exelsheet to outlook

Actually, when I said that you should go to the Visual Basic Editor, I meant in Outlook, not Excel. Sorry, I should have made it clear. So when you now try to enter that line of code in the Immediate Window of the Visual Basic Editor within Outlook, what do you get?

Re: VB code for add folder from exelsheet to outlook

Did you enter it in the Immediate Window? Just to clear, in the Visual Basic Editor (Alt+F11), bring up the Immediate Window by selecting "View > Immediate Window" (Ctrl+R), and then enter it in that window.

Re: VB code for add folder from exelsheet to outlook

Let's try to determine the path to your main folder/mailbox this way...

1) In Outlook Mail, select the folder from the Folder pane in which you want your folders (ie. Work Mark, 1 Loket team Run inbox, Work John 1 Loket team Run inbox, etc) to reside.

2) In the Visual Basic Editor (Alt+F11), enter the following line in the Immediate Window (Ctrl+R)...

? application.ActiveExplorer.CurrentFolder.FolderPath

What path does it return?