Posts by Dave80

    Re: Referencing a Range of Cells with Named Columns and Target.Row


    Thanks everyone. M40wen, unfortunately that was giving me the following error: Run-time error '438': Object doesn't support this property or method. It did however point me in the right direction as I hadn't come across the .address property before. I've now managed to get the following code working.


    Code
    Range((Range("Names").Cells(Target.Row, 1).Address) & ":" & (Range("Comments").Cells(Target.Row, 1).Address)).Interior.ColorIndex = 2

    Re: Referencing a Range of Cells with Named Columns and Target.Row


    Name and Comments are what I've called the columns. To give you an idea of the scope of the spreadsheet I've got over 200 named columns over 11 sheets of which Name and Comments are just two of the Column Names. For example Name refers to Sheet4!$A:$A


    If I remove the "" I'm getting an error message saying Run-time error '1004': Application-defined or object-defined error

    Re: Referencing a Range of Cells with Named Columns and Target.Row


    And thanks for the advice on a better way to deal with headers in general but this spreadsheet is rather large and has taken a couple of months to develop so I'm not very keen on completely changing the way I'm dealing with the headers on this one if I can avoid it but will bare it in mind for future!

    Re: Referencing a Range of Cells with Named Columns and Target.Row


    M40wen, the second last row of your code is along the lines of what I'm looking for but ideally I want to use "Name" and "Comments" instead of HeaderNameA and HeaderNameB.


    The problem is that when I use


    Code
    Range(Sheet4.Cells(Target.Row, "Name"), Sheet4.Cells(Target.Row, "Comments")).Interior.ColorIndex = 2


    I get a Run-time error '13' Type mismatch error

    Re: Referencing a Range of Cells with Named Columns and Target.Row


    "Name" and "Comments" are just two of multiple headings on the sheet. The sheet is a project register which is taking a list of equipment being installed as part of a project including document references, ranges, set points, etc. of which the tag number of the equipment ("Name") is the first column and the final column is a comments one. The majority of the columns have names assigned and then a macro is being run at the end of the project to put the data into a format ready for a bulk upload to SAP. During the life cycle of the project there may be a requirement for engineers to add an additional column in for any extra data they want to keep handy that is not required in SAP and can't be predicted to enable the additional columns to be added into the template that I am creating.


    What the small section of the code referenced above is doing is looking at the value in one cell which is provided by a drop down box and changing the background colour of the entire row dependant on that value. Since this makes the default grey grid lines vanish I'm also adding in grid lines as part of the process.

    Re: Referencing a Range of Cells with Named Columns and Target.Row


    Thanks M40wen, that makes a bit more sense. My problem isn't so much giving the column names (as I've already done that manually) but how to then use those names in the code to colour and add grid lines. As you say, I want A to S coloured and gridded and if someone adds a column at C then it would become A to T that are coloured and gridded. I can now see how your code would generate the column names (I think) but can't see how it would help with the colouring and grid lining of the row between those columns.


    For example I have the code


    Code
    If Not Intersect(Target, Range("Mech_Work_Parent")) Is Nothing Then
            Sheet11.Range("Mech_Tag_Parent").Cells(Target.Row, 1) = Sheet12.Range("Mech_Work_Parent").Cells(Target.Row, 1) 'Copy Parent Tag
        End If


    to copy the content of a cell on one sheet to another sheet if it is updated (I suspect the Sheet references are probably not required but it's working so I don't want to tinker too much and risk breaking it) with the column names Mech_Tag_Parent and Mech_Work_Parent declared with a scope of Workbook. This way if any columns are inserted into the separate sheets then it won't make any difference to the above code. What I'm trying to do (without much success!) is use these same predefined names to limit the range of cells which are coloured and having grid lines added.


    What in effect I want to do is change the code


    Code
    Range("A6:T6").Interior.ColourIndex = 2


    to something like


    Code
    Range("Name" & Target.Row & ":Comments" & Target.Row).Interior.ColorIndex = 2


    but I can't figure out how to get this to work

    Re: Referencing a Range of Cells with Named Columns and Target.Row


    Thanks M40wen but unfortunately I don't think that will help me unless I'm misunderstanding it. On my sheet all the columns are already named specific to their purpose but end users may require to add additional columns in for other information not normally required / available and I don't want this addition of columns to cause problems with any of the macros on my sheet. As a result all of the macros are written using the predetermined column names meaning they will all run on the appropriate columns no matter where or how many columns a user adds.


    The only bit of the macro I can't get working like this is the section shown above where I'm having to reference column letters instead.

    I'm currently working on a spreadsheet with various different macros in it. The current macro I'm having problems with is a Private Sub on a Worksheet which is called on any Change on the worksheet.


    What I am trying to achieve is depending on the value in the column named "Status" the entire row will have its background changed to either white or blue. There are a couple of columns I want to leave black as they are code generated cells so I've split this into 3 blocks of code. One hiccup I had with this was that the grid lines on the line effected vanished so I've had to add in code to actually put in proper grid lines rather than the default grey ones that you get on a blank sheet.


    All is good at this point with the code using lettered references for the columns A, B, C, etc. but I'm trying to use defined column names throughout the code so that if a user adds a column into the spreadsheet at any point the macros will still run correctly and I can't seem to get this working for this section of code.


    Below is the section of code I've managed to get working:



    What I'm trying to do is change the first Range lines to reference my defined columns names along the lines of:


    Code
    Range("Name" & Target.Row & ":Comments" & Target.Row).Interior.ColorIndex = 2


    The other headache I've got with this code is that if the cell "Status", Target.Row is changed by another sheet within the workbook (There are two sheets, one with basic information and another with more in depth information and common columns on both sheets that are copied onto the other sheet so that both sheets have the correct information) then I start getting "Run-Time error 1004: Select method of Range class failed" messages.


    Any help with this would be much appreciated!

    Re: Splitting Text in a Cell to a set lenght


    I've been having a play around with the code above and have hit a slight snag........ I'm sure it's something obvious but I'm struggling to see it. If the cell does not contain a carriage return then the code is ignoring it.


    I need to end up with all Descriptions in a single column, potentially with the description split over several rows depending on it's length. Ideally I also need to include a reference to the original row number in the preceding column leaving me with something along the lines of the following:


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Original row number

    [/td]


    [td]

    Text

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Text5 < 72 Chars

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Text6 First 72 Chars

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Text6 Second 72 Chars

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Text6 Third 72 Chars

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    Text7 <72 Chars

    [/td]


    [/tr]


    [/TABLE]

    Hi,


    I'm trying to create a macro that will take the text in a free formatted unlimited cell on one worksheet and convert it into a number of cells on another worksheet with each cell containing no more than 60 characters. This is so that it can then be automatically uploaded into another piece of software that cannot handle wrapping text and has a text box maximum length of 60 characters but can have any number of rows.


    I've found various methods to achieve this if all text is in a single line within the original cell but as the original cell is in effect a notes section it may contain carriage returns and I haven't found anything that will cope with that. Also, every option I've found so far splits the cell across several columns where as I need to split them down several rows. I'm aware the destination cell will have to be on a different column or worksheet as otherwise it would overwrite the entry below it but that is fine as the end location for the text is a separate sheet anyway and I'm already utilising a few hidden worksheets for data manipulation before it ends up on the required sheet.


    Examples of the sort of text that might be in the cells are:


    [TABLE="class: outer_border, width: 500"]

    [tr]


    [td]

    ESD Evaporator Level Transmitter for High High trip



    0 - 2150mm of R410A or 0 - 2709mm of H2O
    0% correspond at the bottom vessel

    [/td]


    [/tr]


    [/TABLE]


    or


    [TABLE="class: outer_border, width: 500"]

    [tr]


    [td]

    R410A flow from Economizer Outlet to Evaporator Inlet HMI Isolation Valve Open Indication

    [/td]


    [/tr]


    [/TABLE]


    What I need to end up with is


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Evaporator Level Transmitter - DP

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td]

    0 - 2150mm of R410A or 0 - 2709mm of H2O

    [/td]


    [/tr]


    [tr]


    [td]

    0% correspond at the bottom vessel

    [/td]


    [/tr]


    [/TABLE]
    (keeping the line space between the first and second row of text is optional)


    or


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    R410A flow from Economizer Outlet to Evaporator Inlet HMI Isolation

    [/td]


    [/tr]


    [tr]


    [td]

    Valve Open Indication

    [/td]


    [/tr]


    [/TABLE]


    Any suggestions anyone can come up with will be greatly received.