Posts by smuzoen

    Re: Transpose/Extract data from multiple forms to another excel workbook


    With respect to error handler it is good programming practice to have error handling in any module. Its a good habit to form when learning to program as users find more ways to stuff code than you can think of. I would always recommend some error handling in your code.
    Yes it is possible to use titles to populate your database. What I meant by different problems this problem was copying data from one sheet to another. The next problem may be how do I get a list of all worksheets in a certain directory. Try to think of the events you want to happen and ask questions based on this.
    Bottom line - I will help you if you need it and as I said there are lots of people here who can help
    Have a look at this http://www.cpearson.com/excel/errorhandling.htm

    Re: Transpose/Extract data from multiple forms to another excel workbook


    What you can do is use the error handler to place "Blank" into the respective cell because xRng = Nothing so just make the changes as below

    Code
    ErrHandler:
    If Nothing Is xRng Then
    MsgBox "Nothing Found"
    Cells(iRow, I + 1) = "Blank"
    End If
    Resume Next


    In terms of your other aspirations re process all the forms etc I am happy to guide and give you advice but I cannot write it for you. By all means if you need help just ask as there are plenty of smart people in these forums who can help.
    I would recommend you get a clear idea of what you want to achieve, give it a go yourself and if you run into problems start another thread (assuming it is a completely different problem)


    Best of luck

    Re: Find Value in a Column, Make Separate Averages That Skip Those Found Values


    Hi there
    This should do what you want. Mind though there is minimal error checking. I have ensured that the last row contains -1 otherwise the last calculation would not occur. This also is written with the column of numbers in column A - if your not clear load the attached workbook. Results placed in row with -1 value in column H.


    Hope this helps

    Re: Transpose/Extract data from multiple forms to another excel workbook


    Try this



    This should do what you want

    Re: Transpose/Extract data from multiple forms to another excel workbook


    I can give you a hand if you like however you have to realise that what you are asking is not simple. For example in your form you make reference to sheets that are not even in your sample workbook. (Parts and Logistics for example) = it is difficult to debug without a more complete example.
    Secondly break it down as you have 3 modules = which module is causing you grief = which module do you want to run and what do you want it to do
    Take it one step at a time = explain what you want to happen in detail, include all the sheets with sample data and refer to the module that you want to run and what it is to do.
    No one has answered your question as it is too difficult to know what you want to achieve.
    If you are happy to take it a step at a time I will help you out
    Hope you happy to do this

    Re: Clearing contents


    Hi there
    If you run the sub does it clear the drop down list? It should. I don't see how choosing from a drop down list on a worksheet will cause this macro to run. The problem I think is that you need to fire off the Sub Clear_1 by some event such as worksheet change using target.column to narrow the worksheet change.
    Choosing from the drop down list on the worksheet wont cause this macro to fire unless you run the macro manually.
    I may have misunderstood - upload a sample workbook and I will have a look


    Hope this helps

    Re: VBA if column = XXX copy all rowws with value


    As a really easy solution to speed things up replace the following code

    Code
    lastAusRow = lastAusRow + 1
    End If
    Next i


    with

    Code
    lastAusRow = lastAusRow + 1
    Else
    Exit For
    End If
    Next i


    This will exit the loop once the condition of NOT Australia is met


    To copy over the old data just change

    Code
    lastAusRow = Sheets("Australia").Range("A" & Rows.Count).End(xlUp).Row


    to

    Code
    lastAusRow = 4


    Hope this answers your questions

    Re: Copy multiple ranges of cells using a loop


    You could try something like


    Best I can do without a sample

    Re: VBA if column = XXX copy all rowws with value


    If your data is always presented in the format that you supplied to me then to speed the macro there are some options such as to exit the loop once Australia is not found. Secondly you could set a range to copy by finding last instance of Australia and copy range rather than copying one line at a time.
    Anyway this should get you on your way

    Re: VBA if column = XXX copy all rowws with value


    Sorry for the delay
    I have attached a workbook with the code to do what you want. It could be tweaked based on some assumptions however it should get you on your way. I placed the code in module 1 but it is here as well


    Hope this helps

    Re: Automatically updating formulas for four different columns


    Have a look at this and see if I am on the right track. You may have missed my last post where I asked for some more detail but if I have it right this should happen
    1. Enter value in column C then formulas in column A & B copied to next cell and then sheet is locked. Only then next available cell in column C is left unlocked
    2. Enter values in Column H, O & P then copy down formula in column Q and then lock entries and only allow next available cells in columns H,O P to be available for next entry


    Try worksheet - this should at least get you on the way. You need to consider error checking and data validation to ensure rubbish is not entered into workbook

    Re: How to FreezePane a row in Spreadsheet control


    Sorry - I thought you were working with a spreadsheet not a spreadsheet control. (Should have read your title better !!!) I cannot work out how to access the freezepanes member of OWC11.Window - in theory what you are asking can be done with a spreadsheet control on a form however I cannot for the life of me work out how to do it. Anyone have any suggestions?
    PS - On the commands and options button on the spreadsheet control under the sheet tab there is a freeze panes button - perhaps this can be set as property of the control rather than doing it via code

    Re: How to FreezePane a row in Spreadsheet control


    Hi there


    To freeze the top row replace this code

    Code
    Me.Spreadsheet1.Rows("1:1").Select
    Me.Spreadsheet1.ActiveWindow.FreezePanes = True


    with this


    Code
    Sheets("Sheet1").Select
    With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True


    Hope this helps

    Re: Automatically updating formulas for four different columns


    Ok. I understand better what you want to do. Some questions. In your sample workbook you have merged rows 1 & 2 and the text in here is just "Header Rows"
    1. What are the headings for Column A, B, C, (H is Start date, P is End Date, Q is days elapsed) - and what is header for column O
    2. What data is going to be entered into column C and what data is going to be entered into column O
    You really dont need to use VBA to do what I think you want done - you can copy the formulas down as many rows as you want and they wont do any calculations until there is data in the relevant rows. Of course VBA could add in the formula each time data is entered into the workbook but it really is not required. If you want particular cells locked after data is entered then VBA is certainly an option.
    Could you upload a workbook with 5 rows of sample data - so make sure columns A B C H O P Q all have headers and then in rows 3 to 8 put in sample data. I have the formulas you want for each column so that is fine. You dont have to enter the formulas into the sample workbook just enter how the data should look (assuming the formulas you quoted in your first post are the formulas that are going to be used in the final workbook).
    Thats all I should need to complete what you want.
    Yes what you are aiming for can be done - no doubt
    We will get to the end of this I promise

    Re: Automatically updating formulas for four different columns


    Hi Elat1
    I am still not sure what you are trying to achieve. What do you mean by update formula. I am unsure as well about the date issue - you enter a date into column P and it subtracts the date in column H - what is supposed to happen in column Q.
    I am sorry if I am missing something but can you explain in detail what you want to happen
    e.g. You enter a value into column C then what happens in column A, B, P, Q - if you can explain exactly what you want to happen and where it is to happen when you enter a value.
    I am sorry but I am confused
    Cheers

    Re: merge vlookup data


    I have some code below that should do what you want. It assumes that the lookups will never have information in the same row. If there is then the following code would need to be tweaked because it assumes that in a single row, there is only data from one of the lookups that you do.
    As well in the workbook you uploaded there is a stack of data - the last row is 6769 = If this lookup data is to be merged then again you would have to consider deleting blank rows and working out a numbering system.
    Have a look at the following and if it does not do what you want let me know and I will tweak it for you


    Hope this helps