Posts by Eggcel

Re: Navigate to Active IE page from excel

These all seem to be dead links, at least for me.

Re: Capture values from active IE page

If you can post your code to access the webpage(make sure to use code tags), then I will help you modify it to capture the value. I am confused though because now you are saying that you want the price and seller info as was stated in your original post, but the post prior to mine states that you want the price and shipping details. Please clarify.

Re: Capture values from active IE page

This will get your shipping info

For y = 1 To IE.Document.getelementsbytagname("Table").Length - 1
    r = IE.Document.getelementsbytagname("Table").Item(y).innertext
    If InStr(r, "Shipping and handling") > 0 Then
     For n = 0 To IE.Document.getelementsbytagname("Table").Item(y).Rows.Length - 1
                            For j = 0 To IE.Document.getelementsbytagname("Table").Item(y).Rows(n).Cells.Length - 1
                                 ' change this to whatever sheet and cells you need
                                Sheet3.Cells(n + 1, j + 2).Value = IE.Document.getelementsbytagname("Table").Item(y).Rows(n).Cells(j).innertext
                            Next j
                        Next n

Re: Capture values from active IE page

In your original post you mentioned that you wanted the seller info, which you also highlighted, not the shipping values. Also, if you are manually opening the page, isn't it just as easy to do a copy/paste of the information manually as opposed to writing code to grab them? If you look at the code in the workbook I attached previous you will see how to get the price. It does not include how to get the shipping details, since that was not noted in your earlier objective.

You do not need to get the active window to make this work, you can just open an instance of IE with VBA and then pause the code(stop is an easy one, or a loop that is waiting on some action) then navigate where you want manually. When you get to the page that you want start the code to pull your data.

This is the part that gets your price

Sheets(3).Range("A1")= IE.Document.getelementbyid("v4-27").innertext 'change this to whatever sheet and cell you need

Re: VBA to change pivot table filter using a cell value only works twice

This has worked for me in the past.

Field.ClearAllFilters

followed by

Field.EnableMultiplePageItems = False

Re: Capture values from active IE page

forum.ozgrid.com/index.php?attachment/42313/Play with the attached file. In the macro it goes to the ebay, searches for the item, gets all of the links for the items then goes to each page and writes the price and seller name to cells on sheet3. It's a bit rough, since I was just playing with it, but if all you want is to get those two values from the page then you can use that part from the code.

Re: Data Comparison from Two Sheets

Try this (briefly tested)
notify if any bugs

Option Explicit
Sub this()
    Dim a(), b, c()
    Dim i As Integer, x As Long, y As Integer, z As Integer, ii As Integer
    Dim rCtr As Long, cCtr As Integer
    Dim r As Range
    Dim cell As Object
    Dim Rw()
    b = Sheets("MasterList").Range("A1").CurrentRegion.Value
    With Sheets(2).AutoFilter.Range
        On Error Resume Next
        Set r = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
         
        If r Is Nothing Then Exit Sub
        ReDim Rw(1 To .Rows.Count)
        ReDim a(1 To .Rows.Count - 1, 1 To .Columns.Count)
        rCtr = 0
        For Each cell In r.Cells
            rCtr = rCtr + 1
            Rw(rCtr) = cell.Row
            For cCtr = 1 To .Columns.Count
                a(rCtr, cCtr) = cell.Offset(0, cCtr - 1).Value
            Next cCtr
        Next cell
    End With
    ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
    For i = 1 To UBound(a, 1)
        For x = 1 To UBound(b, 1)
            If a(i, 4) = b(x, 4) And a(i, 5) = b(x, 5) And a(i, 6) = b(x, 6) And a(i, 8) = b(x, 8) Then
            Sheets(2).Rows(Rw(i)).Delete Shift:=xlUp
            Exit For
            End If
     Next
    Next
End Sub

Re: Data Comparison from Two Sheets

That makes it much different than the original scenario. You cannot simply just do a compare and replace since the data being deleted is filtered and not all values are showing. This would eliminate all data that is currently filtered.

Re: Data Comparison from Two Sheets

Can you post a sample workbook of the change, or explain in better detail what it is doing. The macro can be modified to accomodate, but I want to be sure to make the right alterations.

Re: Data Comparison from Two Sheets

Is this what you are after?

Option Explicit
Sub this()
    Dim a, b(), c()
    Dim i As Integer, x As Long, y As Integer, z As Integer
    Dim rCtr As Long, cCtr As Integer
    Dim r As Range
    Dim cell As Object
    
    a = Sheets(2).Range("a1").CurrentRegion.Value
    With Sheets("MasterList").AutoFilter.Range
      On Error Resume Next
      Set r = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
      On Error GoTo 0
    
      If r Is Nothing Then Exit Sub
      ReDim b(1 To .Rows.Count - 1, 1 To .Columns.Count)
      rCtr = 0
      For Each cell In r.Cells
       rCtr = rCtr + 1
       For cCtr = 1 To .Columns.Count
        b(rCtr, cCtr) = cell.Offset(0, cCtr - 1).Value
       Next cCtr
      Next cell
    End With
    ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
    For i = 1 To UBound(a, 1)
        For x = 1 To UBound(b, 1)
            If a(i, 4) = b(x, 4) And a(i, 5) = b(x, 5) And a(i, 6) = b(x, 6) And a(i, 8) = b(x, 8) Then
                Exit For
            End If
        Next
        If x = UBound(b, 1) + 1 Then
            z = z + 1
            For y = 1 To UBound(b, 2)
                c(z, y) = a(i, y)
            Next
        End If
    Next
    With Sheets(2).Cells(1, 1).Resize(UBound(c, 1), UBound(c, 2))
        .ClearContents
        .Value = c
    End With
End Sub

Re: Capture values from active IE page

I don't see a macro attached to your file. How are you getting to the page to retrieve the information?

Re: Data Comparison from Two Sheets

It can, but did you mean that the H column is to be checked in addition to D, E, & F for duplicates?

Re: IE automation secure page popup hinderence

You can disable this in your IE security settings
(instructions found by searching your issues)
1) Open Internet
2) Click on Tools
3) Click on Internet Options
4) Click on the Security Tab
5) Click on the Custom Level button
6) Under the Miscellaneous section look for "Display Mixed Content"
7) Click on Disable for Display Mixed Content instead of Prompt
Click on OK twice
9) Close InternetExplorer and reopen
10) Open the webpage that was displaying the warning message, the message should not appear now.

Make sure you realize what you are changing before you change it.

Re: VBA Coding for extracting data from a Pivot Table

You could just use the row data filter in the pivot table to display only the names you want on your table. Is that what you mean?

Re: Getting 1 list of data from 2 sources

Could you provide a sample workbook with sensitive data removed. Also a sheet with your expected results would be helpful. This can be done via macro, but having an example to provide the particulars would be nice.

Re: VBA Coding for extracting data from a Pivot Table

forum.ozgrid.com/index.php?attachment/42249/See if the attached helps. I removed the grand total option from the pivot table and used an if statement to check if there is data in the pivot to move to the table. You can fill down the formulas for as many rows as you anticipate, even leaving room for growth. If there is not a value in the pivot table then it will not be displayed.

Re: Attempting to use dependent checkboxes to autofilter data

you could try something like this, where it checks to see if the other box is also checked and then runs a separate macro that would filter for both.

Private Sub CheckBox20_Click()
If CheckBox20 = True Then
If checkbox21 = True Then
Call macro 'your macro here
Else
Call Macro1
End If
Else
Call Macro2
End If
End Sub

Re: Data Comparison from Two Sheets

Try this

Sub this()
Dim a, b, c()
Dim i As Integer, x As Long, y As Integer, z As Integer
a = Sheets(2).Range("a1").CurrentRegion.Value
b = Sheets("MasterList").Range("a1").CurrentRegion.Value
ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
  For x = 1 To UBound(b, 1)
    If a(i, 4) = b(x, 4) And a(i, 5) = b(x, 5) And a(i, 6) = b(x, 6) Then
     Exit For
    End If
  Next
     If x = UBound(b, 1) + 1 Then
      z = z + 1
      For y = 1 To UBound(b, 2)
       c(z, y) = a(i, y)
      Next
     End If
Next
With Sheets(2).Cells(1, 1).Resize(UBound(c, 1), UBound(c, 2))
 .ClearContents
 .Value = c
End With
End Sub

Re: Concatenate Column based on 2nd Column Criteria

If that is the data then this should get your result. If not then a mock data worksheet and the required result would be in order.

Function Attendees(Names As Range, Dates As Range) As String
    Dim NamesArray As Variant
    Dim DatesArray As Variant
    Dim i As Integer
     
    NamesArray = Names.Value
    DatesArray = Dates.Value
    For i = 1 To UBound(NamesArray)
        If DatesArray(i, 1) = "Y" Then
                Attendees = Attendees & NamesArray(i, 1) & " "
        End If
    Next
   
End Function

Re: Concatenate Column based on 2nd Column Criteria

some sample data would be helpful, but I think this will get you closer.

Function Attendees(Names As Range, Dates As Range) As String
    Dim NamesArray As Variant
    Dim DatesArray As Variant
    Dim i As Integer
    
    NamesArray = Names.Value
    DatesArray = Dates.Value
    For i = 1 To UBound(NamesArray)
        If DatesArray(i, 1) = "Y" Then
        If Attendees <> "" Then
          Attendees = Attendees & NamesArray(i, 1)
        Else
        Attendees = NamesArray(i, 1)
        End If
        End If
    Next
    Attendees = Mid(Attendees, Len(Attendees) + 1)
End Function