Posts by Moe Kadhom

    Hi Kj
    This code is working but when I delete a row, the priorities are not auto adjusted.
    For example, top 5 rows have priorities of 1,2,3,4,5. If I delete row 2, the current result is 1,3,4,5,6 but it should adjust all the priorities to be 1,2,3,4,5.
    The code need to re-adjust the priorities every time the number changes ( its doing that at the moment) or a row is deled or added to the spreadsheet.


    See attached sample data.


    Thanks

    Hi
    I have a spreadsheet that has a list of tasks with priorities ( 1,2,3,4,,,,etc) set in column A. I found below code that does the job but the priorities need to be in column B.


    Basically When any of the numbers are changed in column B, the table is sorted automatically to move the rows to the correct position.


    however, there are couple of issues:

    • The macro debugs every time a row is deleted or column added or any other format changes to the priorities.
    • There is a fixed range of data ( 300 rows) in the macro that preferably needs to be dynamic based on how many rows have data In them.

    Would any of you smart people can modify this macro to rectify the issues listed above?


    Happy New Year In advance.



    Thanks

    Hi


    The workbook has many pictures in it. The macro deletes all the pictures but leaves the ones that is called "crown" and the one that matches the file name.


    the code is below:

    Code
    1. Sub FleetNationalDeletePic()
    2. Dim pic As Picture
    3. wbName = WorksheetFunction.Replace(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, "."), 5, "")
    4. Sheets("Title").Select
    5. For Each pic In ActiveSheet.Pictures
    6. If pic.Name <> wbName And LCase(pic.Name) <> "crown" Then
    7. pic.Delete
    8. End If
    9. Next pic
    10. End Sub


    When the macro is run, an error appears and the debug highlights "If pic.Name <> wbName And LCase(pic.Name) <> "crown" Then". however, running the macro again does not run an error.


    I don't understand why this is happening and I need to do this for a lot of workbooks. Can any of you smart people help me solve this issue?


    the file is a bit too big to attach here so I am happy to email through If required.



    thanks

    Re: Average a range based on multiple Condition


    Hi Charles
    The result in Q3 should show the average of the months that have 4 weeks. so if i do the formula manually
    4 Week Average Q3=AVERAGE(B2,D2,E2,G2,H2,J2,K2,M2,N2)= 13,348
    5 week Average Q4=AVERAGE(C2,F2,I2,L2) = 11,212


    I would have done this excatly as done above but the months will change so the formula needs to automaticly average any month that is 4 weeks and also 5 weeks.


    I hope I have made it clear. thanks for your help

    Hi
    Please refer to the attached for a sample of my data.


    I have data set out for 12 months starting from April that shows number of jobs that will be in each month. I have another set of data that shows how many weeks each month will have in them.


    What I want is for a formula to get the average of the 4 weeks months and 5 weeks months separately. I tired to use vlookup but my formula got too big.


    is there any way this can be done?


    thanks for your help in advance

    Files

    • Sample.xlsx

      (9.38 kB, downloaded 58 times, last: )

    Re: Seprate Unique values for three tables into seprate sheets


    Hi Charles
    much appreciate your help with this.


    I ran the macro but some sheets are showing the incorrect tables. as an example please refer to sheet "NSW North Area 1". the first table has "NSW ACT Trade Area 1" data which should not be in this sheet. the second table has the correct date but the incorrect heading.


    thanks for you help

    Hi
    Please see attached the " Data" workbook.


    What I want is for a macro to separate each unique value in column A into a separate sheet but keep the heading for each table. So basically three tables for every unique value in column A into separate sheet. if there are no data for some of the tables, then nothing needs to be shown.


    See attached the desired result. This will save me a lot of time every month so I would appreciate it if someone can lend a hand and come up with a macro that does above.


    Let me know if you have any questions.


    Much appreciate your help.


    Thanks

    Files

    • Data.xlsx

      (37.67 kB, downloaded 64 times, last: )
    • Result.xlsx

      (153.33 kB, downloaded 59 times, last: )

    Hi
    I have a proposal template ( which is in a word format) that I need to populate using a user form. Most of the template is generic and does not need to be changed but there are around 20-25 text that need to be linked to the user form so they can be populated.


    The user form will have data entry boxes, drop down boxes and possibly check boxes. It also needs a button to clear so they can start over, a cancel button to exit and a button to convert the file to PDF.


    The file is too big to attach here but Once the project is accepted I will communicate exactly what is required via email but above is basically what I am trying to achieve.


    10 % deposit has already been paid to Ozgrid.


    Thanks

    Hi All
    I have below sample data. also see attached.


    I want a conditional formatting formula to highlight the range A:H when the value in B ( FSR) and value in H (Serial number) are duplicated. The condition should be when both are duplicated on the same row.


    so for example, FSR 1243 should be highlighted as it has the serial number J905727 and both are repeated on the third line.


    can one you wonderful people assist with a formula?


    thanks for your help


    [TABLE="width: 887"]

    [tr]


    [td]

    Sample Data

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Date

    [/td]


    [td]

    FSR

    [/td]


    [td]

    Lead

    [/td]


    [td]

    Service Tech Name

    [/td]


    [td]

    Points

    [/td]


    [td]

    Customer Name

    [/td]


    [td]

    Job card Number

    [/td]


    [td]

    Serial Number

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42552[/TD]
    [TD="align: right"]1243[/TD]

    [td]

    Overhaul

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    J905727

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42552[/TD]
    [TD="align: right"]3511[/TD]

    [td]

    Overhaul

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    9A163652

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42552[/TD]
    [TD="align: right"]1243[/TD]

    [td]

    Overhaul

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    J905727

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42552[/TD]
    [TD="align: right"]3811[/TD]

    [td]

    Overhaul

    [/td]


    [td][/td]


    [TD="align: right"]200[/TD]

    [td][/td]


    [td][/td]


    [td]

    1A187451R

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42555[/TD]
    [TD="align: right"]3346[/TD]

    [td]

    Traction Battery

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    1A178318R

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42555[/TD]
    [TD="align: right"]1549[/TD]

    [td]

    Traction Battery

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    9A141366R

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42555[/TD]
    [TD="align: right"]2468[/TD]

    [td]

    Traction Battery

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    H3968

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42555[/TD]
    [TD="align: right"]2125[/TD]

    [td]

    Traction Battery

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    W3402

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42555[/TD]
    [TD="align: right"]2468[/TD]

    [td]

    Traction Battery

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    H3968

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42555[/TD]
    [TD="align: right"]2959[/TD]

    [td]

    Traction Battery

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    W3443SH

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]42555[/TD]
    [TD="align: right"]3346[/TD]

    [td]

    Traction Battery

    [/td]


    [td][/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [td][/td]


    [td]

    J905727

    [/td]


    [/tr]


    [/TABLE]

    Files

    • Sample.xlsx

      (12.6 kB, downloaded 60 times, last: )