Posts by NGM

    Hi all, here is the line of code I'm trying to input a formula in a cell:

    Code
    range("G"&i).Formula="=(F"&i+1)


    The column for the find routine will always be column A, and the amount wanted will always be in column F, and the formula will always be in column G.


    Thank you,


    Neil

    Excellent, thank you so much, KjBox!


    Cells were formatted with WrapText set to True, which was the same as the source cells, so this made reading the results difficult. I added a line of code to fix this, and here is the final code with all the unnecessary stuff removed:



    Thank you! :congrats:

    Thanks Carim and KjBox,


    So, the code finally works without errors; however, the array does not seem to be populating as expected. Only one sheet has the last row of data copied.


    The good news is the Summary sheet correctly displays all the current worksheets in order.


    Here is the code:


    Here is the result:


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tDebug4.PNG Views:\t1 Size:\t40.3 KB ID:\t1217697","data-align":"none","data-attachmentid":"1217697","data-size":"full"}[/ATTACH]


    Any ideas on why the array is only populating for one worksheet?


    Not sure if this is related, but the array seems to enter data all the way down 7 rows further than the last sheet name (found by pressing Ctrl+End), but all 7 rows are empty.


    Thank you,

    Thank you so much, KjBox,


    This code was still failing, so I thought it must be something to do with my sheets. After inserting error handling, which trapped the name of the worksheet at the time of the failure and displayed it in a message box, I found the offending road sheet; however, while there were some issues (such as the template structure was missing, so I fixed that), it is still failing on this one sheet.


    I'll keep checking to see if I can find it, but interruptions are impossible at the moment.


    Thank you again,


    Neil

    Hi KjBox,


    Attached is a cut-down version of our road network. Our local government area (eg. similiar in understanding to the US County area) is over 47,000km2, so our road network is very extensive. We are only a very, very small part of Australia, very far from capital cities. The area of roads we look after is equal to about half the size of the US state of Michigan, or half the size of the country Hungary - but our population is only around 5,000.


    Thank you,

    I just read your recent replies, KjBox, so I'll work on that now, but it will be a cut-down version so as not to include our sensitive information. Yes, there a worksheet called Summary, as you will see.

    Hi KjBox, the following could be the problem.


    While the workbook uploaded indicated each sheet had the column headings begin on row 1, they actually begin on rows 6-7, as in the picture below - and every sheet has the exact same format.


    [ATTACH=JSON]{"data-align":"none","data-size":"full","data-attachmentid":1217641}[/ATTACH]


    No cells are merged, but there could be data all the way to column V, and will probably not be on the same row as the last used row in Column A.


    So, I'm sorry for misleading you, as I can probably understand if your code assumes there are column headings across the first row of data.


    All I'm wanting at the moment is for code to return the final chainage number in Column A for each sheet, and I think I can manipulate the code to deal with things like Road Width, etc.


    Thank you,


    Neil

    Hi KjBox, and thank you for your reply.


    While your code worked well in your attached workbook, it failed in mine on the following line:


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tDebug1.PNG Views:\t1 Size:\t74.6 KB ID:\t1217633","data-align":"none","data-attachmentid":"1217633","data-size":"full"}[/ATTACH]
    I tried to understand your code before replying, but arrays are not something I fully understand. Am I right in assuming you are passing the results into an array, then copying these values from the array to populate the cells?


    Would your code fall down if the Summary sheet does not have all the column names? Putting column names into the Summary sheet didn't seem to help.


    I'm also confused with x="Road No." value? If x is to be the current Worksheet, there is no worksheet called "Road No."


    Thank you,


    Neil

    Thanks, HTH, I really appreciate your help.


    These ideas, unfortunately, don't seem to be working for me.


    So, your first formula was just a test?
    =INDIRECT("'"&A2&"'!"&"A25")


    Your next formula worked, but it was only good for 100 rows, but I can see this is hard-coded
    =LOOKUP(2,1/(INDIRECT("'"&A3&"'!"&"A1"):INDIRECT("'"&A3&"'!"&"A100")<>0),INDIRECT("'"&A3&"'!"&"A1"):INDIRECT("'"&A3&"'!"&"A100"))


    This could work, though, if I had a cell somewhere on the Summary Sheet which recorded the last used row number (populated by a macro), then reference this cell instead of "A100".


    I should be able to find this code OK.


    Now to test your code.

    Thanks, Carim,


    The Summary Sheet contains all the Road Numbers down Column A.


    Column B needs to return the last Chainage for each Road Number.


    Let's say A2 = Road 1


    Worksheet Road 1 has the Chainage in Column A.


    The last value in Column A will be the value I want to extact and copy to cell B2 in the Summary Sheet.


    I have attached a very simple idea of the worksheets, hoping this helps.


    Our work day is over here in eastern Australia, and I'll be back in the office in about 14 hours.


    Thank you,

    Hi, I'll try to set the scene.


    OS: Windows 7 Pro
    Application: Excel 2013


    The Workbook:

    • This workbook contains nearly 100 worksheets, each with data relating to a road network
    • Except for the Summary Sheet, each worksheet has a unique name which is the Road Number
    • Each worksheet for each Road Number has the exact same structure as one template was used to construct each one
    • The used colums are A thru V (22)
    • The last value in the Chainage column will also equal the road length

    The Summary Sheet

    • This Sheet is also formatted into the same columns as each of the other sheets, except that Column A is the Road Number (so there are 23 columns)
    • In the Road Number worksheets, the first Column contains Chainage data (eg. road segment length, where the last segment in this column equals the length of the road)

    The Code

    • For this code, I'm only concerned with the first column as I can adapt the code across the various fields
    • The first worksheet is a Summary sheet which at present contains the Road Numbers down Column A
    • For each Road Number, the code needs to:

      • Read the Worksheet Name from Column A (Road Number)
      • Read the Column Heading (eg Chainage)
      • Return the last value in the Chainage column and paste the value into the Chainage Column on the Summary Sheet

        • In this case, Cell A2 = Road 1
        • Cell B2 = Chainage for Road 1


      • Continue on until the last Road Name has the Chainage recorded in Column B


    I'm familiar to coding, but cannot put this all together, particularly using cell.value to extract the Worksheet name.


    Here is code I have found to find the last used cell in a column, and I expect the cells() to be preceded by worksheets(""). or something in a For Each ws, Next loop:


    [VBA]
    Dim lrow As Long
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    [/VBA]


    Code to insert the relevant chainage will use the cell.value = [last chainage]


    If anyone could point me to some code to use, I think I can extract and manipulate what I need.


    Thank you,


    Neil