Code ceased to function

**maruchi** · 02-09-2010, 10:50 AM

I have been using this code;

Code:

'DEFINE OWNER

    Columns("L:L").ColumnWidth = 13
    Rows("98:98").RowHeight = 12.75

    Dim intRow As Single
    Dim intLastRow As Single
    intLastRow = Range("L500").End(xlUp).Row

    For intRow = intLastRow To 2 Step -1
    
    Select Case Left(UCase$(Cells(intRow, "I").Value), 2)
            Case "PE"
                Cells(intRow, "L").Value = "MATT"
                Cells(intRow, "L").Select
                Selection.Font.ColorIndex = 3
            Case "RB"
                Cells(intRow, "L").Value = "FAZ"
                Cells(intRow, "L").Select
                Selection.Font.ColorIndex = 10
            Case "FR"
                Cells(intRow, "L").Value = "ANDY/TOM"
                Cells(intRow, "L").Select
                Selection.Font.ColorIndex = 5
            Case "ST"
                Cells(intRow, "L").Value = "CARL"
                Cells(intRow, "L").Select
                Selection.Font.ColorIndex = 13
                Selection.Font.Italic = True
            Case "RC", "RG"
                Cells(intRow, "L").Value = "MURRAY"
                Cells(intRow, "L").Select
                Selection.Font.ColorIndex = 53
                Selection.Font.Bold = True
            Case "SC", "SA", "SF"
                Cells(intRow, "L").Value = "KEV"
            Case Else
                Cells(intRow, "L").Value = "UNASSIGNED"

        End Select

    Next intRow

To populate a column based on the first two letters of a reference which appears in another column. For some reason, without any alteration since the last time I used it last week, the code has now stopped working. It does not return any error messages, nor does it hang. The macro runs through the code and simply does nothing to the spreadsheet.

Anything you can suggest is much appreciated,
Mar

**Domski** · 02-09-2010, 10:54 AM

Hi,

Can't see anything immediately obvious.

Can you attach a sample workbook with the code in it.

Dom

**maruchi** · 02-09-2010, 11:03 AM

Here you go.

I have had to remove some of the information from the preceding columns and have cut it down to just the code that is not working/being skipped.

Like I said, it worked brilliantly before (thanks to another individual on this forum).

The macro just skips owner assignment and goes straight into the little bit of formatting I get it to do to prepare for printing.

Bothersome...
Mar

**Domski** · 02-09-2010, 11:19 AM

Try changing this:

Code:

    intLastRow = Range("L500").End(xlUp).Row

to this:

Code:

    intLastRow = Cells(Rows.Count, 10).End(xlUp).Row

All intLastRow was doing in your original code was returning 1 so the code was only looking at row 1 and then stopping.

Dom

**maruchi** · 02-09-2010, 11:28 AM

Cheers Dom, that works in mine now too.

Any idea why it would have worked before?

Interestingly, while messing about with it myself, I found that placing some value (1, a, cheese) in the last cell in the required column range made the code work!?

Sometimes Excel is a fickle mistress...

Thanks again
Mar

**Domski** · 02-09-2010, 01:04 PM

When checking for the last used row in a sheet it is best to use a column that contains data. Whilst what you had may have produced results before (due to the usedrange of the worksheet being set in that column I think) it will not work consistently hence the problem you faced.

The code I posted basically tells it to look up row J from the bottom of the column and return the first populated row that it finds. There are other methods but this seems to work well for me.

Dom