Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code debug
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 137
Default Code debug
Please help to debug following code which was collected and restructured
several codes from the discussion groups. I intended to add a thick black
bottom border to upper row if two rows contain different data, and then add a
blank row in between, now correct number of rows has formatted with a thick
black bottom border but all placed start from the last row up.

Expected result
AAAA
AAAA
AAAA 'thick bottom border
'Empty row in between
BBBB
BBBB 'thick bottom border
'Empty row in between
CCCC 'thick bottom border

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then

Selection.Borders(xlEdgeTop).LineStyle = xlDot
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeTop).ColorIndex = 15

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeBottom).ColorIndex = 1

Selection.Borders(xlEdgeRight).LineStyle = xlDot
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeRight).ColorIndex = 15

Selection.Borders(xlInsideVertical).LineStyle = xlDot
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideVertical).ColorIndex = 15

Selection.Borders(xlInsideHorizontal).LineStyle = xlDot
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
Selection.Borders.LineStyle = xlDot
Selection.Borders.Weight = xlThin
Selection.Borders.ColorIndex = 15
End If
Rgds
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default Code debug
Try this...

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then
with range(cells(lngrow, "A"), Cells(lngrow, "X"))
.Borders(xlEdgeTop).LineStyle = xlDot
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = 15

.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeBottom).ColorIndex = 1

.Borders(xlEdgeRight).LineStyle = xlDot
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = 15

.Borders(xlInsideVertical).LineStyle = xlDot
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = 15

.Borders(xlInsideHorizontal).LineStyle = xlDot
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
.Borders.LineStyle = xlDot
.Borders.Weight = xlThin
.Borders.ColorIndex = 15
end with
End If

--
HTH...

Jim Thomlinson


"Seeker" wrote:

Please help to debug following code which was collected and restructured
several codes from the discussion groups. I intended to add a thick black
bottom border to upper row if two rows contain different data, and then add a
blank row in between, now correct number of rows has formatted with a thick
black bottom border but all placed start from the last row up.

Expected result
AAAA
AAAA
AAAA 'thick bottom border
'Empty row in between
BBBB
BBBB 'thick bottom border
'Empty row in between
CCCC 'thick bottom border

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then

Selection.Borders(xlEdgeTop).LineStyle = xlDot
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeTop).ColorIndex = 15

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeBottom).ColorIndex = 1

Selection.Borders(xlEdgeRight).LineStyle = xlDot
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeRight).ColorIndex = 15

Selection.Borders(xlInsideVertical).LineStyle = xlDot
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideVertical).ColorIndex = 15

Selection.Borders(xlInsideHorizontal).LineStyle = xlDot
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
Selection.Borders.LineStyle = xlDot
Selection.Borders.Weight = xlThin
Selection.Borders.ColorIndex = 15
End If
Rgds
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 137
Default Code debug
Hi Jim,
Thanks for your reply, when I test your code, the compiler said "Else
without If"?
Rgds

"Jim Thomlinson" wrote:

Try this...

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then
with range(cells(lngrow, "A"), Cells(lngrow, "X"))
.Borders(xlEdgeTop).LineStyle = xlDot
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = 15

.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeBottom).ColorIndex = 1

.Borders(xlEdgeRight).LineStyle = xlDot
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = 15

.Borders(xlInsideVertical).LineStyle = xlDot
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = 15

.Borders(xlInsideHorizontal).LineStyle = xlDot
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
.Borders.LineStyle = xlDot
.Borders.Weight = xlThin
.Borders.ColorIndex = 15
end with
End If

--
HTH...

Jim Thomlinson


"Seeker" wrote:

Please help to debug following code which was collected and restructured
several codes from the discussion groups. I intended to add a thick black
bottom border to upper row if two rows contain different data, and then add a
blank row in between, now correct number of rows has formatted with a thick
black bottom border but all placed start from the last row up.

Expected result
AAAA
AAAA
AAAA 'thick bottom border
'Empty row in between
BBBB
BBBB 'thick bottom border
'Empty row in between
CCCC 'thick bottom border

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then

Selection.Borders(xlEdgeTop).LineStyle = xlDot
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeTop).ColorIndex = 15

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeBottom).ColorIndex = 1

Selection.Borders(xlEdgeRight).LineStyle = xlDot
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeRight).ColorIndex = 15

Selection.Borders(xlInsideVertical).LineStyle = xlDot
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideVertical).ColorIndex = 15

Selection.Borders(xlInsideHorizontal).LineStyle = xlDot
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
Selection.Borders.LineStyle = xlDot
Selection.Borders.Weight = xlThin
Selection.Borders.ColorIndex = 15
End If
Rgds
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 199
Default Code debug
this is a another approach.

Sub sometest()
Dim Lastcell As Range
Dim startcell As Range
Dim endcell As Range

Set Lastcell = Cells(Rows.Count, "A").End(xlUp)

Set startcell = Range("B2")
Set endcell = startcell.Cells(2, 1)

Do While (startcell.Row <= Lastcell.Row)

If startcell = endcell Then
Set endcell = endcell.Cells(2, 1)
Else
With Range(startcell.Cells(1, 0), endcell.Cells(0, 23))
If endcell.Row - startcell.Row 1 Then
.Borders(xlInsideHorizontal).LineStyle = xlDot
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = 15
End If
.Borders(xlEdgeTop).LineStyle = xlDot
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = 15

.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeBottom).ColorIndex = 1

.Borders(xlEdgeRight).LineStyle = xlDot
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = 15

.Borders(xlInsideVertical).LineStyle = xlDot
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = 15

End With
endcell(1, 1).EntireRow.Insert

Set startcell = endcell
Set endcell = startcell.Cells(2, 1)
End If
Loop
End Sub

Keiji

Seeker wrote:
Please help to debug following code which was collected and restructured
several codes from the discussion groups. I intended to add a thick black
bottom border to upper row if two rows contain different data, and then add a
blank row in between, now correct number of rows has formatted with a thick
black bottom border but all placed start from the last row up.

Expected result
AAAA
AAAA
AAAA 'thick bottom border
'Empty row in between
BBBB
BBBB 'thick bottom border
'Empty row in between
CCCC 'thick bottom border

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then

Selection.Borders(xlEdgeTop).LineStyle = xlDot
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeTop).ColorIndex = 15

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeBottom).ColorIndex = 1

Selection.Borders(xlEdgeRight).LineStyle = xlDot
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeRight).ColorIndex = 15

Selection.Borders(xlInsideVertical).LineStyle = xlDot
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideVertical).ColorIndex = 15

Selection.Borders(xlInsideHorizontal).LineStyle = xlDot
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
Selection.Borders.LineStyle = xlDot
Selection.Borders.Weight = xlThin
Selection.Borders.ColorIndex = 15
End If
Rgds
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 137
Default Code debug
keiji kounoike,
Your code works perfectly, million thanks.
Rgds

"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

this is a another approach.

Sub sometest()
Dim Lastcell As Range
Dim startcell As Range
Dim endcell As Range

Set Lastcell = Cells(Rows.Count, "A").End(xlUp)

Set startcell = Range("B2")
Set endcell = startcell.Cells(2, 1)

Do While (startcell.Row <= Lastcell.Row)

If startcell = endcell Then
Set endcell = endcell.Cells(2, 1)
Else
With Range(startcell.Cells(1, 0), endcell.Cells(0, 23))
If endcell.Row - startcell.Row 1 Then
.Borders(xlInsideHorizontal).LineStyle = xlDot
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = 15
End If
.Borders(xlEdgeTop).LineStyle = xlDot
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = 15

.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeBottom).ColorIndex = 1

.Borders(xlEdgeRight).LineStyle = xlDot
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = 15

.Borders(xlInsideVertical).LineStyle = xlDot
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = 15

End With
endcell(1, 1).EntireRow.Insert

Set startcell = endcell
Set endcell = startcell.Cells(2, 1)
End If
Loop
End Sub

Keiji

Seeker wrote:
Please help to debug following code which was collected and restructured
several codes from the discussion groups. I intended to add a thick black
bottom border to upper row if two rows contain different data, and then add a
blank row in between, now correct number of rows has formatted with a thick
black bottom border but all placed start from the last row up.

Expected result
AAAA
AAAA
AAAA 'thick bottom border
'Empty row in between
BBBB
BBBB 'thick bottom border
'Empty row in between
CCCC 'thick bottom border

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then

Selection.Borders(xlEdgeTop).LineStyle = xlDot
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeTop).ColorIndex = 15

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeBottom).ColorIndex = 1

Selection.Borders(xlEdgeRight).LineStyle = xlDot
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeRight).ColorIndex = 15

Selection.Borders(xlInsideVertical).LineStyle = xlDot
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideVertical).ColorIndex = 15

Selection.Borders(xlInsideHorizontal).LineStyle = xlDot
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
Selection.Borders.LineStyle = xlDot
Selection.Borders.Weight = xlThin
Selection.Borders.ColorIndex = 15
End If
Rgds

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 199
Default Code debug
Thanks for your feedback.

Keiji

Seeker wrote:
keiji kounoike,
Your code works perfectly, million thanks.
Rgds

"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

this is a another approach.

Sub sometest()
Dim Lastcell As Range
Dim startcell As Range
Dim endcell As Range

Set Lastcell = Cells(Rows.Count, "A").End(xlUp)

Set startcell = Range("B2")
Set endcell = startcell.Cells(2, 1)

Do While (startcell.Row <= Lastcell.Row)

If startcell = endcell Then
Set endcell = endcell.Cells(2, 1)
Else
With Range(startcell.Cells(1, 0), endcell.Cells(0, 23))
If endcell.Row - startcell.Row 1 Then
.Borders(xlInsideHorizontal).LineStyle = xlDot
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = 15
End If
.Borders(xlEdgeTop).LineStyle = xlDot
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = 15

.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeBottom).ColorIndex = 1

.Borders(xlEdgeRight).LineStyle = xlDot
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = 15

.Borders(xlInsideVertical).LineStyle = xlDot
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = 15

End With
endcell(1, 1).EntireRow.Insert

Set startcell = endcell
Set endcell = startcell.Cells(2, 1)
End If
Loop
End Sub

Keiji

Seeker wrote:
Please help to debug following code which was collected and restructured
several codes from the discussion groups. I intended to add a thick black
bottom border to upper row if two rows contain different data, and then add a
blank row in between, now correct number of rows has formatted with a thick
black bottom border but all placed start from the last row up.

Expected result
AAAA
AAAA
AAAA 'thick bottom border
'Empty row in between
BBBB
BBBB 'thick bottom border
'Empty row in between
CCCC 'thick bottom border

Dim LastRow As Long
Dim lngRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:X" & LastRow).Select

For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) < Range("B" & lngRow - 1) Then

Selection.Borders(xlEdgeTop).LineStyle = xlDot
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeTop).ColorIndex = 15

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeBottom).ColorIndex = 1

Selection.Borders(xlEdgeRight).LineStyle = xlDot
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeRight).ColorIndex = 15

Selection.Borders(xlInsideVertical).LineStyle = xlDot
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideVertical).ColorIndex = 15

Selection.Borders(xlInsideHorizontal).LineStyle = xlDot
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Borders(xlInsideHorizontal).ColorIndex = 15

Rows(lngRow).EntireRow.Insert
Else
Selection.Borders.LineStyle = xlDot
Selection.Borders.Weight = xlThin
Selection.Borders.ColorIndex = 15
End If
Rgds
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Easiest way to Debug Code Corey Excel Programming 4 September 9th 08 03:41 AM
debug code mohavv Excel Discussion (Misc queries) 1 November 27th 07 09:56 PM
Please help debug this code! Heliocracy Excel Programming 1 October 23rd 07 10:04 PM
Debug Error in Code Karen McKenzie Excel Discussion (Misc queries) 2 May 18th 07 04:25 PM
Need help with code debug please justme Excel Programming 2 January 4th 07 06:09 AM


All times are GMT +1. The time now is 10:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement