Posts by 05125668

    Re: Repeat vba code for specific array of worksheets


    I still seem to get the "Run-time error '9': Subscript out of range" error when modifying the code as you mentioned. Do you have any other ideas on this?




    Thanks again for looking at this.

    Re: Repeat vba code for specific array of worksheets


    Hi,


    Thanks very much for the reply.


    I have tried as you have suggested below, however I receive the following error "Complie Error: For Each control variable must be Variant or Object" and the "wsName" text is highlighted.


    Code
    Sub test()
        Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet, wsName As String
        With Worksheets("Dashboard")
            cmonth = .Range("J2").Value
            pmonth = .Range("K2").Value
            nmonth = .Range("I2").Value
         
            For Each wsName In Array("Sheet1", "Sheet2")
            Set ws = Worksheets(wsName)


    I have tried changing the wsName to Variant and Object however I get the following message when this is done "Run-time error "9": Subscript out of range"


    Code
    Sub test()
        Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet, wsName As Variant
        With Worksheets("Dashboard")
            cmonth = .Range("J2").Value
            pmonth = .Range("K2").Value
            nmonth = .Range("I2").Value
         
            For Each wsName In Array("Sheet1", "Sheet2")
            Set ws = Worksheets(wsName)


    Do you know why I will be receiving these error messages?


    Kind regards,

    Hi,


    I'm trying to figure out how have the below code re-run through a certain array of worksheets in the same workbook.

    From the following code I need to loop through an array of worksheet names performing the same vba code each time:


    Code
    Sheets(Array("Sheet1", "Sheet2")).Select 
    
    
    For Each worksheet In Array
    
    
    Next worksheet In an array


    Can anybody help me out with this one please?

    Here is the full code:


    Kind regards,

    05125668

    Hi,


    I'm trying to figure out how have the below code re-run through a certain array of worksheets in the same workbook.


    I have highlighted the area in the code where there need the code to perform a set of tasks for each worksheet listed in an array.


    Can anybody help me out with this one please?


    Here is the code:



    Kind regards,


    05125668

    Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri


    Hi venkat1926,


    Thanks sooo much for your help, I have managed to make the modifications you suggested and it now works a treat. Thanks for having the patience with me as I know my initial requirements weren't clear.


    Please see below for the final code:



    Thanks very much again


    Kind regards,


    05125668

    Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri


    Sorry here is the modified workbook.


    I also forgot to mention that I will need to run the same code for multiple business units.


    Is there anyway to call the code but change the worksheet name of the business unit worksheet? I have added this to the excel document to show what I mean.


    Please let me know if you need any more information.


    Kind regards,

    Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri


    Hi venkat1926,


    Thanks very much for taking the time to do this. The code did works as I requested and I made some additions to create a fail safe to prevent the formulas from being deleted.


    I just need one last thing to be performed in the code and for the next months header to be found in "Business Unit XX" and this columns and all columns to the right of this to be hidden.


    I have put code below that will find the next month header but I just need the vba code that will hide that column and all others to the right


    Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri


    Hi,<BR><BR>hopefully this should be of more help.&nbsp; I have put in a bit more information and have also put in an instructions tab to details what I need the vba code to do.&nbsp; Please let me know if you need any further information.<BR><BR>Many thanks in advance.

    Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri


    Hi Venkat1926,


    Thanks for your reply. Yes you're correct the date is in B1, apologises for this.


    In terms of the formula in column M, I just created dummy formulas as I just need to this is work in principal and then I can modify this to work in document I'm currently working in. You can put whatever formula you like in column M, I just need the vba code to copy the formulas and paste them in the next blank column to the right (i.e, column N).


    Unfortunately I can't send through the master as the document is rather large in size and also has confidential information in it.


    Please let me know if you need any further information and thanks again.


    Kind regards,

    Hi,


    I'm currently relatively new to the VBA arena and am trying to solve a time consuming task via the help of VBA in Excel.


    I have attached an very basic example of the excel data and layout I'm working with. This is what I need the code to do:


    1. Search for the string in Sheet 1 (cell A2) and find it in Sheet 2.
    2. Copy the entire column of the found column.
    3. Paste the formulas/data in the column next to the found column.


    Do you think there is anyone how could potentially help me with this problem?


    Thanks in advance for the help.


    Kind regards,


    05125668

    Hi All,

    I was wondering if someone could help me write a marco that creates a new row in the below table with the relevant fields in each cell. This is in Word 2007.


    Start Date
    End Date
    Start Time
    End Time
    No of Staff
    Job Role
    <Start Date>
    <End Date>
    <Start Time>
    <End Time>
    <No of Staff>
    <Job Role>



    Please let me know if anyone can help me with this problem.

    If you need further information then just ask.

    Thanks in advance.

    05125668

    Re: Extract three sets of numbers from one cell


    CF,

    Thanks for your work on this one.

    I will have a look at the example and see if I can fully utilise your code.

    With regards to my VB exposure - I did computing at college but only touched on Real Basic programing, so I'm O.K with simple programming. Nevertheless I'm willing to learn and will give it a go.

    Thanks again for your help. If I have any other further questions then I will let you know.

    05125668

    Re: Extract three sets of numbers from one cell


    Hi Guys,

    CurtFunk: I've had a little play with the spreadsheet you attached and it works perfectly for cells when the format is e.g. 'Black = 123456' and 'Colour =123456'. So thanks for that.

    Do you know how I could adapt this for the following formats ':', '-', ' '? In my data set it seems there are lots of instances where to value follows a ':' but the issue here would be that nearly every line in the cell has a ':'. For example 'Address: ', 'Tele: ' etc.

    How would I find the ':' that corresponds to 'Black', 'B/W', 'Colour', 'Total', 'Readings' etc?

    Thanks

    Dan

    Re: Extract three sets of numbers from one cell


    Hi Guys,

    Thanks for your replies and sorry I haven't got back to you over the weekend.

    I have attached an example Excel doc just to give you an idea of the data. I have also put an example table to show the way in which I would like the data to be extracted.

    CurtFunk: I will have a look at your example Excel sheet and see if it is possible to adapt the formula.

    The main issue is that the value could follow 'B/W:', 'B/W:', 'Black =', 'Black:', 'Black ' and this is the same for 'Colour'.

    Also the length of the string will change in every cell as there are e-mails, addresses etc in there.

    Thanks

    Dan

    Hi All,

    My problem is that I have around 300 cells (same column) which contain endless amounts of data and I need to extract two sets of numbers from them. Here's an example:

    "Orginal Message as follows:

    ------------------------

    CALL REASSIGNMENT NOTIFICATION

    Reads - B = 6742 F= 3027"

    (This is a shorter version compared to what is in the cells but it gives you an idea)

    The challenge is that all the cells have different lengths of data in them. I need the numbers in bold putting in seperate columns.

    I've tried the 'text to columns' function however because the cells are varied in length it proves to be useless.

    Is there anyone out there that could help me?

    Thanks