Posts by smuzoen

    Re: Search and retrieve information from columns in Excel into list view


    Quote

    Could the same be done with a list box instead of a list view?


    As my code was using a listbox already and not a listview control I misunderstood you - listview is not a standard control in the toolbox - usually has to be added as additional control
    Anyway glad its sorted.
    Anthony

    Re: Drag a range of data if a cell condition is met


    I need to get a clearer idea of what you want.
    1. You want to be able to select from a list the available stocks e.g. 0001.HK, 0002.HK, 0003.HK
    2. When you select the stock what data do you want pulled from sheet in Hong Kong Blue Chip Stocks Data(Test) and placed into the Chosen Stock workbook - if you select 0001.HK from the list what data in the sheet named 0001.HK do you want pasted into the Chosen Stock workbook - I know you want Open, High, Low, Close, Volume data but is it all the data in the sheet or only certain rows e.g. based on date range


    It is easy to generate a form with a listbox containing all the stocks (essentially a list of the worksheet names in the Hong Kong Blue Chip Stocks Data(Test) workbook (except the summary sheet I assume)
    Once the stock is selected from the listbox I just need to know what data is collected and placed into the Chosen Stock workbook


    Please be very explicit in what you require and I will write the code for you


    Regards
    Anthony

    Re: Search and retrieve information from columns in Excel into list view


    I am not sure what you mean by a List View - this form contains a control called a Listbox and you can navigate to website from the Listbox control.
    You need to add a sub to fire when you click on a result in the Listbox. Try to think of it like this - The Listbox is an object and you need to add code to give instructions what to do with this object when certain events happen to that object e.g. click .
    Add this code to the form

    Code
    Private Sub lbResults_Click()
    Dim navigatetoURL as variant
    navigatetoURL = lbResults.Value
    ActiveWorkbook.FollowHyperlink Address:=navigatetoURL, NewWindow:=True
    
    
    End Sub


    This code will work if the cell contains e.g. http://www.computer.com. What type of hyperlinks are you talking about - to websites? to cell references? workbooks? - the code I have included works for wesbites
    Let me know if this helps
    Anthony

    Re: Adding work times


    I think I have what you want = your result for row 3 differs from mine but according to the rules (if I understand correctly) I think you made an error. All the other results are the same as what you would expect according to the table you uploaded for comparison


    I hope this helps
    Anthony


    PS - you represent the hours as decimals e.g. 3.75 (3.75 hours) = My answer is in the format 3:45 (three hours and 45 mins) - the result is the same just different format

    Re: Converting long list of vertical data to horizontal line


    Robert has already given you a solution however here is an alternative for what it is worth - it will calculate how many rows and how many columns and then paste it all out onto one row


    Hope this is of some value
    Anthony

    Re: Adding work times


    Format Start Time column A and End Time column B to h:mm AM/PM (custom formating), Lunch Column C format to h:mm (custom formating), and Hours Worked Column D to h:mm custom format. Then in Hours worked column use the following formula and copy down column D as many times as required
    =IF(A2="","",B2-C2-A2+IF(A2>B2,1))
    This is End time minus lunch minus start time - it will calculate an exact number hours worked in format e.g. 8:23 (8 hours, 23 minutes). If will account for the possibility that the start time is the day before the end time (start 10PM Monday and finish 3pm Tuesday)
    Have a look at attached workbook


    Hope this helps
    Anthony

    Re: Copy multiple dynamic ranges at one time


    You cannot do what you are trying to do unless you are using CONTIGUOUS ranges. If you try and combine 4 ranges with the Union command and the ranges are not the same length you will get an error of "That command cannt be used on multiple selections".
    You can use Union to combine ranges together but you will not be able to copy them unless they are contiguous.
    The following code for example would work


    If the ranges were set as follows then you would get an error

    Code
    Set a = Range("B2:T3")
    Set b = Range("B6:U8")


    If the ranges are differing lengths then just copy them one at a time - you can always calculate which row is the next blank row to paste the second range and so on.
    Upload your workbook if this does not help and I will have a look at it for you = just be explicit about what you want to achieve


    Hope this helps
    Anthony

    Re: Is text to rows possible?


    I assume that the string will not always be the same length e.g you could have


    1 x 610000394-2206
    2 x 544454-223
    2 x 65555454-44877
    1x610045554400394-2227985554


    If that is the case then in column B use the following formula
    =IF(A1="","",MID(A1,(FIND("x",A1,1)+1),(FIND("-",A1,1)-(FIND("x",A1,1)+1))))
    In column C use the following formula
    =IF(A1="","",MID(A1,(FIND("-",A1,1)+1),(LEN(A1)-FIND("-",A1,1))))


    This way it does not matter how long the Batch number is or the case number - you will always get them separated by using these formulas.
    If they are always in the format of
    1 x 610000394-2206 (9 numbers as Batch and 4 numbers as case always in the same format)
    then the answer is much easier
    Column B formula is =MID(A1,5,9)
    Column C formula is = Right(A1,4)


    Hope this helps
    Anthony

    Re: VLookup returns wrong value from lookup table


    For what it is worth - if you have time to waste...


    Have a look at the sheet I have uploaded - it appears to me that certain combinations of results to make the final average affect the lookup. If I use 100%, 85%, 70%, 65% to get an average of 80% then the lookup fails. If I use 90%,90%, 70%,70% to get an average of 80% the lookup works.
    It has to be the way excel calculates - even if you sum the results and divide by 4 the lookup will fail with certain combinations of values.


    If you load ExcelTeachers original spread sheet and change one of the rows results to 90% 90% 70% 70% you will find that the lookup works on ExcelTeachers spreadsheet.
    It is more a quirk than anything and is just a potential time waster but for what it is worth....


    Anthony

    Re: VLookup returns wrong value from lookup table


    Hi ExcelTeacher
    To tell you the truth I have never come across this problem before. My suspicion is that when excel calculates the average if you were to iterate the value to many decimal points that they would not all be zeros. That is only my opinion - I am not really sure. I would not necessarily make using round when calculating averages standard practice however keep it in mind.
    In terms of other calculations none come to mind however there are lots of smart people here that may be able to advise you better with their experience if they have found similar problems with other functions.
    If you have any problems though post them here - they will always get sorted out for you
    Regards
    Anthony

    Re: VLookup returns wrong value from lookup table


    This is a problem with the way excel calculates the average. Instead of using
    =AVERAGE(C7:F7) to calculate the final score use
    =Round(AVERAGE(C7:F7),4) to calculate the final score and you will find the lookup will then work


    Anthony