Posts by afriboy

    [SIZE=13px]The attached Worsheet has different sheets that communicate with each other.[/SIZE]

    [SIZE=13px]"Lead Data" is the main worksheet where all data is entered.[/SIZE]

    [SIZE=13px]I already have a Macro which adds a new row above the last row of the table (above row "Total") and it works perfectly.[/SIZE]

    [SIZE=13px]In Sheet "Forecasted Sales", the data from Lead data is taken and reorganised.[/SIZE]

    [SIZE=13px]I would like the Macro to also add a new Row in the table in "Forecasted Sales" using the same conditions.[/SIZE]

    This is the Macro that I'm using now

    I would like a Macro that Auto generates a custom Serial number.

    There are 2 types of Serial Numbers with the following nomenclature:

    JO-[year][Serial] - Example: JO-201634
    WA-[year][Serial] - Example: WA-201622

    Both types are Independent of each other.
    In the attached file, there is a first sheet, "Form" where I would enter Information.
    If the type of Job is "Normal" then a new Serial starting with JO- will be generated
    If the type is Warranty, then it will be WA-

    The macro needs to use the Information entered in the Form and transpose them in lines in the second sheet "Follow-up".
    It will Need to check the last Serial number by type and whenever a new one is added, it will have to continue the series.
    eg.: If the last normal Serial number is JO-201632, and now a new normal Serial number is requested; the new line should Show: JO-201633

    Same with warranty.

    Frankly speaking, I have no idea where to start.....Any help will be tremendously appreciated.

    I have quite a large workbook with several sheets that talk to each other.

    This sheet is updated every once in a while and shared with a team.
    The team is divided into 2 main groups(in the future maybe more); admin and regular staff.

    Therefore, each group will have access to specific sheets (if a group does not have access to a specific sheet, the latter will be hidden).

    Now, the problem that I have is that, everytime I Need to update that data in one of the sheets, I have to save different worksheets based on what group the team members are part of.
    This is a bit annoying on the Long run as it requiers a lot of manual work and is prone to mistakes.

    To simply things, here are sample sheets:

    Home (admin) - Only Admin members have access to
    Home (regular) - Only Regular members have access to

    Report Admin - Only Admin members have access to
    Report Regular - Only Regular members have access to

    DB - Both groups have access to it.

    At the moment, I have 2 files:
    Workbook - Admin (showing only sheets accessible to admin: Home (admin) - Report Admin and DB
    Workbook - Regular (showing only sheets accessible to regular: Home (regular) - Report Regular and DB

    I would therefore like to have one file, "Workbook", with a Login access.
    So, if a member of Admin staff enters their login details, it will automatically show sheets accessible to Admin
    Same for Regular.

    Is this possible?

    (same question was posted here too:…ser-sign.html#post4616823)

    Re: VBA code for loop

    Quote from pike;774140

    maybe just

    Range("B12:B21").FormulaR1C1 = "=IF(RC[-1] ="""","""", R[-1]C[1] + 1)"

    Wow, that's great.

    Could you walk me through the syntax of it? (so I won't have to bother you guys later)

    I need to convert the below code into a Loop.
    I'm having Trouble with the range and the other cells.


    Re: Add and calculate formula VBA

    Quote from MrRedli;774123

    attached updated... not sure but when I changed your macro from Wenn to IF it worked for me fine.

    Worked here...
    Even tried the same way in a different Macro and it worked.

    Thanks a lot!

    Re: Add and calculate formula VBA

    Quote from S O;774106

    Unless you know what they are for, don't turn them off.

    But in your code you would need to do:

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

    Ok thank you.
    I haven't turned anything off though (I won't know how to anyways!)

    I tried adding this piece of code, but I'm still stuck with the same Problem.

    Re: Add formula within VBA and calculate said formula

    Quote from S O;774086

    OK, perhaps you've got events switched off too or something, does this work?

    Sub test() 
        With Range("B1") 
            .Formula = "=IF(A2=2,1,5)" 
        End With 
    End Sub

    Sorry, but still no Change.
    How do I turn Events and Automatic calculation on?

    Re: Add formula within VBA and calculate said formula

    Quote from S O;774080

    Okay, you could try this then instead:

    Sub test() 
        With Range("B1")
            .Formula = "=IF(A2=2,1,5)" 
        End With
    End Sub

    Tried this method, but I'm facing the same initial problem, same error too

    Re: Add formula within VBA and calculate said formula

    Quote from S O;774077

    It sounds like you have calculation mode set to manual,

    There are a number of ways you can do this - but the easiest way is probably to just not use a formula and have VBA do the check instead:

    Sub test() 
        Range("B1").Value = IIf(Range("A2").Value = 2, 1, 5)
    End Sub

    Thank you.
    This works, but only if the data is already set.
    I Need something more dynamic, whereas I can Change the data in the other cells and see the formula results.

    I'm trying to add a formula into a cell, here, B1, and force it to calculate itself.

    I'm able to insert the formula in the right cell using the below code.

    Sub test()
    Range("b1").Formula = "=if(a2=2,1,5)"
    End Sub

    However, I get the following error in the cell #NAME
    But, when I click on the cell, the formula bar shows the right data; i.e: =if(a2=2,1,5)

    Now, if I select the formula bar, and press enter, the formula gets activated and it gives me the right answer.

    How can I automate this process?

    Re: Functions for: Average of 2 columns and a far more challenging thing!

    Quote from KjBox;762177

    You're welcome.

    One more thing please :)
    I've embedded your code into a new sheet and everything is working.
    Now, working on the original sheet, I'd like to automate as much as possible the adding of a new row.
    In my new sheet, the data in the first column has the following format:
    and so on and so forth.

    I need a macro that would add a line with the same format upon double clicking on the cell of the first empty row in the first column.
    i.e: if the table goes until "R-6" in cell: A7
    Then double clicking on A8 should add a new row and fill A8 with "R-7".

    Need to bear in mind that R-7 may be deleted if there's a mistake or something, so the macro needs to check the data in the previous row and not go through a counter.

    Thank you again!

    Re: Functions for: Average of 2 columns and a far more challenging thing!

    Great thanks!

    Quote from KjBox;762054

    If you put your data in a proper Excel table then you can use a Worksheet_Change Event code to do everything you need.

    Try the attached, to add a new row of data just type in the next serial number in the row immediately below the table. The table will automatically extend to include the new row, then edit Status.

    The code is:

    sorry for this bad title, but the "challenging thing" is hard to explain in a few words.

    Challenging thing:
    In the attached file, sheet 1, you will see rows from 1 to infinity.
    I am using it to monitor the statuses of documents (each document is on a row).
    The statuses always follow a specific route, first stage is RFQ, second is Quote issued - Direct, third is Quote issued - Local, etc...

    For each row, I want to be able to only change the status every time each stage is hit and I'd like the date on which the specific status has been changed to be set in the right cell.

    Here's an example.
    Row 1:
    On 13 December: Status is set to RFQ; date in cell C2 should automatically show as 13 december
    On 15 December: status is set to Quote issued - Direct; date in Cell D2 should automaticall show as 15 December while the one in C2 should remain the same
    And so on and so forth.

    I'm struggling with keeping the initial date not to change with a formula.
    Maybe a Macro? (although formula is preferable)

    Average of 2 columns:

    In sheet 2 of the same file, I'd like to monitor what's happening in sheet 1.
    You will see in row 1 that I'm trying to calculate the average duration it takes to switch statuses from RFQ to Quote issued - Direct. Basically average of D2-C2, D3-C3, etc e
    So, I need a formula which would help calculate that in the simplest way.

    Bear in mind that when you fill Sheet 1, if, let's say you are at row 6 and you only got to the RFQ stage of this row, then D6-C6 will be negative which will confuse the result.
    The formula should take into consideration the possibility of a negative result in one or many rows which should not be taken into account when calculating the average.

    Can you help please?

    Re: Dependent Data Validation - Numerous lists and multiple data

    Quote from MickG;754634

    Try this:-

    Hi MickG,
    that's brilliant thanks.

    2 things though:
    1) How can I spread the data validation in sheet 2 over many rows. If I drag it, it won't work
    2) Any chance I can tell how this is done so I can replicate it? :)

    Thank you

    I have the following case (see sample for more details):
    I have a table with 3 columns:
    Serial Number

    There are several customers who can own different machines with a specific model.
    Different customers may own several machines that have the same model
    Customer 1 may have model 1 and model 2
    Customer 2 may have model 2 and model 3

    However, all machines have a unique serial number.
    So model 2 of customer 1 and 2 will have different serial numbers depending on each machine.

    Attached is a sample file.
    What I’m trying to do is a dependent data validation on sheet 2 of the same file where I can choose the customer in column 1 then in column 2 it will only show me the models that are owned by the chosen customer and in column 3, the serial number for this model that is owned by the customer.

    I cannot use the index range technique since I have upwards of 200 customers and I cannot assign a range for each list.
    Moreover, my list in sheet 1 is ever growing since it is populated a few times a week.
    What is the best way to approach this?

    Thank you!

    I'm looking for a macro that does the following:

    In sheet 1, I have column from: B1 to B5 with information entered in it.
    In sheet 2, I have a table that goes from A2 to M2 over as many rows as possible

    I would like the macro that finds the last empty row in the table in sheet 2 and to automatically transpose the following

    Sheet 1 - B1 to Sheet 2 - A3
    Sheet 1 - B2 to Sheet 2 - A7
    Sheet 1 - B3 to Sheet 2 - A1



    Below is the code that I have, but for some reason it keeps on jumping to the last row in sheet5.
    Whereas, if use the same sheet "Macros" as the origin AND destination, the macro works