Posts by Sharid

    I need some help on a loop and I can not work it out. I have been stuck on this bit for sometime. I posted on another forum see link Here . I have not had much luck and now I have posted here, hoping someone can help.


    PROBLEM

    Currently the code is not looping through all the products, it can take off the first, with child elements (THIS IS IMPORTANT). However it only does the first item over and over again.



    My latest code is here or visit the above link for more information


    Hi


    I am stuck on a bit of code and cannot get it to work any further, sometimes it half works, other times it give an error message that i can not work out why.


    When the code does half work, if places the searched data into the FIRST blank CELL in a column, when it should place it across the row, so if no data is found then it should leave that cell in the column blank, or place the word "Nil" if it would make it easier to fix


    I have attached a link for the workbook to be downloaded, the code in button 1 is the issue.


    There are two sheets in the workbook, Sheet2 shows how the data should look.


    I can take this no further as I lack the knowledge and skill to do it, i'm hoping someone can help.;(;(;(;(;(


    Thanks for have a look.


    Download workbook

    Hi


    This is my first post in a long time and the forum has changed a bit, so hopfully I have submitted it correctly, I need a bit of help on my code. I am very limited in excel VBA The code works fine, apart from -


    I need it to extract ONLY emails and URLs and place the email in Sheet1 "Scraper" in the NEXT BLANK ROW


    Currently it extracts anything text, emails or URL and places them in column A or B. I only need Emails or URLs. I have been stuck on this for sometime and can't seem to work it out


    Also


    I am not sure if my DELETE DUPLICATES is deleting duplicate rows or duplicates in column. It SHOULD ONLY delete duplicate rows



    How the code works -


    On Sheet2 "URL List" I have a list of URLs, the code runs through this and places the results onto Sheet1 "Scraper". and deletes any duplicates. My code is only supposed to scraper email and URLs and place them in Column A + B in the NEXT BLANK ROW.


    I have tried to fix the problem but it is out of my scope. i am hoping someone can help


    The code is below the. As stated above the code works fine apart from the code in RED, this is what the problem most likley is. The orange code is the delete options which may also be incorrect as DUPLICATE ROWS should be deleted and not Duplicate values in cells.


    This is a FACEBOOK SCRAPER and scrapes the "about " pages urls here is a link https://www.facebook.com/pg/SalemFor...rnal%5Blink%5D



    Thanks

    [SIZE=14px]Hi


    I have a Command Button on Sheet1 and a List of URL in Sheet2 Column A. When the command button is clicked my code takes the first URL from Sheet2 Column A, opens IE and then imports the URL from that page into Sheet1 Column A, then closes IE and deletes any duplicates, this bit is fine . I can't get it to do this : -


    1) I need the code to repeat but this time go to the next url in Sheet2 and do the same, the code does this until no more URL are left on Sheet2


    2) It pastes the new date in the next blank row, currently it over writes the previous data.



    [CODE[/SIZE]Private Sub CommandButton2_Click()


    'For url extraction
    Dim ie, items, elem As Object
    Dim i, j, k, l As Integer


    i = 2
    k = 2
    l = 2


    'Create IE, open it for limited time, get url from sheet
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.Navigate Sheets("Sheet2").Range("A1").Value
    Application.Wait (Now + TimeValue("00:00:5"))


    Do While ie.Busy Or ie.ReadyState <> 4


    Loop


    For i = 0 To 500
    On Error Resume Next


    'Paste in sheet and column
    Sheets("Sheet1").Range("A" & i).Value = ie.document.getelementsbytagname("a").Item(i).innerText


    'Close IE Browser
    Next i
    ie.Visible = Quit



    'Deletes duplicates in column A
    Columns(1).RemoveDuplicates Columns:=Array(1)



    End Sub[SIZE=14px]][/CODE]


    I've been stuck on this for weeks, this is the best i can do. can some please help


    Thanks[/SIZE]

    Re: ListBox move data to Dormant


    Ok I have put together this code, which copies to dormant and deletes the data in the listbox and the listbox data source sheet, which is Sheet5 (Products)

    The code works fine for the most part.


    Copy and paste is fine, so the first bit of the code is ok.


    The problem is in the second bit.


    If i move 1 item at a time to dormant not including the last item in the list box everything is fine,


    If i move only the last item to dormant, it deletes everything in the list box and sheet5 (product) and has only copied and pasted the last item to dormant.


    can someone have alook at the last bit, THANKS


    I have a userform called ProductList, with 1 ListBox and a Dormant Button, when the form opens it populates from data on sheet5 (Products) I can't work out a code so when the user selects a item in the listbox and clicks on the dormant button. It will copy and pastes that information to sheet6 (Dormant) and remove that data from the listbox and Sheet5(Products),


    e.g
    Userform ProductList opens and is populated from Sheet5 (Products)


    User select data in ListBox1


    User clicks on the Dormant button


    The item that was selected in Listbox1 is now copied and pasted into a Sheet6 (Dormant) in next blank row


    THEN


    The selected data from Listbox1 is removed from listbox1 and Sheet5 (Products) and any blank rows removed Sheet is auto saved, So the next time the user opens the ProductList userform and Listbox1 is populated from Sheet5 (Products) the removed data is no longer showing.


    The data is on sheet5 (Products) Column A to R it will need to copy and past into Sheet6 (Dormant) Column A to R next blank row.

    Re: Listbox Rowsource Not Populating Correctly


    This should also work, might need to change it a bit


    Re: Clear data in row after date in row is 3 days old


    This will clear data and keep fomula, you will have to run it every 3 days, could not send an IF statement. "If less than 3 days from today then run the code"



    [CODESheet1.Range("F5:Z50")][/CODE] This reffers to Sheet1 range Column F Row 5 the next bit is Z50 the Z = upto column Z and 50 rows down. Yours will look like something like this.


    Code
    1. Set rConstants = Sheet1.Range("F5:Z6000").SpecialCells(xlCellTypeConstants)


    Sheet.Range("F5: Then the last column with data in it from row F5 or the longest row with data in it up to 6000 as you have requested. I have used Column Z yours will differ.


    TEST ON ANOTEHR WORKSHEET, BEFORE USING IT

    Re: Conditional Format Macro


    Your code ammended


    This is your problem .Color = 65535



    or you could go with mine

    Re: Conditional Format Macro


    sorry forgot to put it in a code tag


    [hr]*[/hr]
    It would have been less bother to EDIT your post and add them rather than just adding a new reply saying you did forget them...

    Re: Conditional Format Macro


    Re: List Box range issue.


    royUK

    Sorry a bit long
    Thanks for your code, but I'm still having the same problem. The changes are shown on the listbox but the spreadsheet is not updating so when the form loads again the old data is showing in the list box.


    I have 2 userforms


    First UserForm
    On this userform (ProductList) I have a ListBox (ListBox1) and an edit button.


    When the user selects the item in the listbox and then clicks on the edit button my second userform opens up, the second user form is called EditProductForm.


    Second userform.
    On this userform (EditProductList) I have 2 textboxes and a submit button. When this form opens the textboxes are populated with the selected data from listbox1 on the first userform. I have another code that does this
    There is NO LISTBOX on the second userform.




    This bit is fine
    + The second userform is populated from the selected data from userform one ListBox1, this bit is fine
    + When the submit button is clicked, the listbox1 on the first userform (ProductList) is updated, this bit is fine


    This is the problem
    Spreadsheet5 (Products) is not updated when the submit button is clicked on the second userform (EditProductList), therefore when the first userform reloads (ProductList), The ListBox reverts back to the old data as there are no updates to the spreadsheet.Also I need the data to replace the old one and not create a new entry, as the data is being editied not created new.

    Re: Copy same range from certain worksheets and paste to one sheet


    This might help, two methods


    1st Method: -
    Copies from a range and then pastes it to sheet1, first 4 lines, first bit of code copies from sheet 2 and pastes into first 4 rows of sheet1, the second bit of code copies from sheet3 and pastes into Sheet1 A5 the next 4 rows. If you go with this method, then you will need to do this for all your sheets. e.g sheet4 will paste into Sheet1 A9.
    I have set the copy range of 4 rows from the other sheets, yours will differ



    2nd Method:-


    Second method is the same apart from it finds the next blank row to paste into for Sheet1, if your data has to be in some order i.e sheet 10 data is shown before the data of sheet3 has been copied and pasted then the code will look like the one in the last bit of code. As this is how the code will excute.


    Code
    1. Private Sub CommandButton1_Click()
    2. 'Copies range from sheet2 to sheet1 NEXT BLANK ROW
    3. Worksheets("Sheet2").Range("A1:P4").Copy _
    4. Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1)
    5. 'Copies range from sheet3 and pastes in sheet1 NEXT BLANK ROW
    6. Worksheets("Sheet3").Range("A1:P4").Copy _
    7. Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1)
    8. End Sub



    This bit is info only


    hope this helps

    Re: Listbox issue, data is removed


    I've fixed the data vanishing issuefrom the listbox, I have placed the code in an IF statement and disabled the X button on second userform and placed a button with EditProductForm.Hide as the code and the issues has now gone.


    The code below does what i want, it pulls the data from LISTBOX1 and in to the second userform called EditProductForm . On this user form I have two buttons, Submit and Close, the X button is now disabled.


    My problem now is I can't save the changes to Sheet5 (Products)


    The changes show within the listbox ASAP when i click submit, however the changes are not saving to the above sheet. So when the form reloads the changes are gone as the sheet has not been updated.


    The code below is only for the edit button on a diffrent form called ProductList . The submit button is on EditProductForm



    The code

    The code below shows the information selected from my ListBox1, once the user selects the item and click the edit button, the EditProductForm LOADS with the selected data from Listbox1. This bit is fine.


    The problem I have is if the user has selected the wrong Item and then closes the EditProductForm the selected data is removed from my listBox, which should not happen. It is still on my sheet and on next load of the listbox it re-shows. I can't figure this out



    Also On the EditProductForm I have a submit button which will be used to submit the changes, my currect code for my submit button creates a new record of the submitted item, when it should replace the selected data as it has been edited. help on either one will be appreciated.

    I use this code to populate my listbox, with data from the sheet call Products, the code work fine, it does what i want apart from on thing, the range.
    My range will vary depending on how many products are in the range. I set the range to ("A2:C65536") so it will include all products in the two columns.The problem I have is that it now shows all the rows including the blank ones in the list box, these don't need to show as they are empty, the scroll bar appears when it is not need.


    The range will need to vary depending on the amount of products in the columns on the sheet. Can some please have a look. Thanks