Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2010
    Location
    Sydney, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a spreadsheet that contains suppliers in column G in the format A97.[txt}. There may be more or less characters before the period, but what I need to do is to create a box around the cells in column G where the text prior to the period is the same.
    How can I do this? I've started code as follows:

    dim boxer as long
    dim conta
    lngMaxRow = Range("a65536").End(xlUp).Row
    For lngrow = lngMaxRow To 2 Step -1 'Only go up to row 2
    boxer = InStr(cell, ".")
    conta = Left(Cells(lngrow, 7), boxer)

    but at this point I'm a bit lost - how can I compare it to the cell below? And if there are three, four or five cells from the same supplier, how do I do this?
    Much thanks in advance!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you elaborate on what you want? Perhaps put in an example file with a before and after worksheet...

    But to compare and if match you could do something like:
    [codebox]Dim i As Integer
    if conta = Left(Cells(lngrow+1, 7), boxer) then
    For i = xlEdgeLeft To xlEdgeRight
    With Cells(lngRow, 7).Borders(i)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Next
    end if[/codebox]

    Steve
    PS I would suggest changing the line:
    lngMaxRow = Range("a65536").End(xlUp).Row

    to
    lngMaxRow = cells(activesheet.rows.count,1).End(xlUp).Row

    it will make it compatible with XL2007 and later versions with more rows than 65536.

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    I agree with Steve's query .... "Could you elaborate on what you want? Perhaps put in an example file with a before and after worksheet..."

    Here is a sample of how I interpret what is needed ....

    [attachment=90606:Box around cells.jpg]

    Are Supplier names going to be in alpha sorted order? If so, my results would be different (ie. for A97.txt)
    Attached Images Attached Images

  4. #4
    New Lounger
    Join Date
    Nov 2010
    Location
    Sydney, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    Thank you for your quick reply. I have enclosed two spreadsheets - the Initial state and the After state. Tim, you are close - however the box goes around the range of cells where the text before the period is the same, rather than round each individual cell.

    Steve, thanks also for your tip re xlendup - very helpful.

    Many thanks again in advance.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Nov 2010
    Location
    Sydney, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh, also, yes, the list will be sorted in suppler alpha order

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you check your files, They both seem corrupt: I get errors when I try to open them which causes excel to crash...

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This code almost works. It marks the G2:G3, G9:G13, and G15:G16 as in your file. It also marks G4:G5.

    Could you explain the logic of why G4 and G5 are not marked so that logic can be built into the code? Or was not marking them an oversight on your part?

    [codebox]Option Explicit
    Sub MarkCells()
    Dim lRow As Long
    Dim lMaxRow As Long
    Dim iCol As Integer
    Dim sConta As String
    Dim iPeriod As String
    Dim sNext As String
    Dim rCell As Range

    iCol = 7 'Column to compare
    lRow = 2 'starting row

    lMaxRow = Cells(ActiveSheet.Rows.Count, iCol).End(xlUp).Row
    Do While lRow < lMaxRow
    Set rCell = Cells(lRow, iCol)
    iPeriod = InStr(rCell, ".")
    sConta = Left(rCell, iPeriod)
    sNext = Left(rCell.Offset(1, 0), iPeriod)
    If sNext = sConta Then
    With rCell.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With rCell.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With rCell.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    End If
    lRow = lRow + 1
    Do While sConta = sNext
    Set rCell = Cells(lRow, iCol)
    With rCell.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With rCell.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    sNext = Left(rCell.Offset(1, 0), iPeriod)
    If sConta <> sNext Then
    With rCell.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    End If
    lRow = lRow + 1
    Loop
    Loop
    End Sub[/codebox]

    Steve

  8. #8
    New Lounger
    Join Date
    Nov 2010
    Location
    Sydney, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Uranium, brilliant, thank you very much. And you are quite correct, G4 and G5 should have been boxed in my example, sorry for the oversight.
    Much appreciated, thanks again.
    SteveA - thank you for your attempt to help - apologies that your system crashed, but it worked with other users - perhaps the files were corrupted during the download process.
    Thanks again all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •