Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Loop in my macro doesn't seem to be running; however, it compiles fine...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Loop in my macro doesn't seem to be running; however, it compiles fine...

I have a loop in my code which compiles fine; however, it doesn't see
to be running (or doing anything?) . The loop is supposed t
check the values of Column C in each row, If the value is different tha
the row below it, then it draws a line along the bottom of all thos
cells. The code is as follows with comments in blue:

For i = 2 To 1000 'run loop a maximum of 1000 iterations
j = i + 1 'j is 1 more than i so that j can be used t
check the next row
If ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value < "
Then 'check to see If Column C in the row after row(i) isn't empty, i
False then exit the loop
If ThisWorkbook.Worksheets("Sheet1").Cells(i, "C").Value <
ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value Then 'check t
see if row(i) column C and row(j) column C have different values, i
true then row(i) needs to get formatted with a line along the bottom o
all row(i)'s cells
With Rows("i:i").Borders(xlEdgeBottom) 'Create a lin
along the bottom of all of row(i)'s cells
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End If
End If
Next

--
Goobie
-----------------------------------------------------------------------
Goobies's Profile: http://www.excelforum.com/member.php...fo&userid=3076
View this thread: http://www.excelforum.com/showthread.php?threadid=50668

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Loop in my macro doesn't seem to be running; however, it compiles fine...
I think, With Rows("i:i").Borders(xlEdgeBottom) may be problem.

'i' of "i:i" is not a variable but is a part of string "i:i"

Best regards,

sjoo

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Loop in my macro doesn't seem to be running; however, it compiles fine...

avveerkar Wrote:
That is right. Instead of Rows("i:i") just say rows(i) and it shoul
wor

A V Veerka

I tried this but still no luck. Nothing happens. I've attached th
macro in it's entirety in case something else is keeping this loop fro
doing anything. Most of the code was generated using the record macr
button in excell so please excuse the .select this and .select that..
I haven't had a chance to clean up that part of the code. Also
Everthing before and after the For loop executes properly, it jus
seems the loop itself is doing nothing...? I've highlighted the loo
in blue

Dim i As Intege
Dim j As Intege

Cells.Selec
Selection.Columns.AutoFi
Range("A1").Selec
Sheets("output").Selec
Sheets.Ad
Sheets("output").Selec
Columns("B:B").Selec
Selection.Cop
Sheets("Sheet1").Selec
Columns("A:A").Selec
ActiveSheet.Past
Sheets("output").Selec
ActiveWindow.SmallScroll ToRight:=1
Columns("T:T").Selec
Application.CutCopyMode = Fals
Selection.Cop
Sheets("Sheet1").Selec
Columns("B:B").Selec
ActiveSheet.Past
Sheets("output").Selec
ActiveWindow.SmallScroll ToRight:=1
Columns("AG:AG").Selec
Application.CutCopyMode = Fals
Selection.Cop
Sheets("Sheet1").Selec
Columns("C:C").Selec
ActiveSheet.Past
Range("A1").Selec
Sheets("output").Selec
ActiveWindow.SmallScroll ToRight:=3
Columns("BL:BL").Selec
Application.CutCopyMode = Fals
Selection.Cop
Sheets("Sheet1").Selec
Columns("D:D").Selec
ActiveSheet.Past
Columns("B:B").Selec
Application.CutCopyMode = Fals
Selection.Insert Shift:=xlToRigh
Range("B1").Selec
ActiveCell.FormulaR1C1 = "
Columns("B:B").Selec
Selection.Delete Shift:=xlToLef
Columns("C:C").Selec
Selection.Insert Shift:=xlToRigh
Range("C1").Selec
ActiveCell.FormulaR1C1 = "PC
Range("C2").Selec
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)
Range("C2").Selec
Selection.AutoFill Destination:=Range("C2:C1000")
Type:=xlFillDefaul
Range("C2:C1000").Selec
ActiveWindow.SmallScroll Down:=-109
Columns("A:A").Selec
Selection.NumberFormat = "mm/dd/yy
Cells.Selec
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending
Key2:=Range("A2")
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1
MatchCase:=
False, Orientation:=xlTopToBottom
DataOption1:=xlSortTextAsNumbers,
DataOption2:=xlSortNorma

For i = 2 To 100
j = i +
If ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value < "
The
If ThisWorkbook.Worksheets("Sheet1").Cells(i, "C").Value <
ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value The
With Rows(i).Borders(xlEdgeBottom
.LineStyle = xlContinuou
.Weight = xlMediu
.ColorIndex = xlAutomati
End Wit
End I
End I
Next

Range("A1").Selec
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:

"Sheet1!R1C1:R1000C5").CreatePivotTable TableDestination:=""
TableName:=
"PivotTable1
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3
1
ActiveSheet.Cells(3, 1).Selec
ActiveSheet.PivotTables("PivotTable1").SmallGrid = Fals
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="PC

ActiveSheet.PivotTables("PivotTable1").PivotFields ("PC").Orientation

xlDataFiel
Sheets("Sheet1").Selec
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:

"[output.csv]Sheet2!PivotTable1", TableDestination:=""
TableName:=
"PivotTable2
ActiveSheet.PivotTables("PivotTable2").SmallGrid = Fals
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Owner

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Owner").Orientatio
=
xlDataFiel
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Owner"
.PivotItems("(blank)").Visible = Fals
End Wit
Range("A5").Selec
ActiveSheet.PivotTables("PivotTable2").PivotSelect ""
xlDataAndLabe
Selection.Cop
Sheets("Sheet2").Selec
Range("D3").Selec
ActiveSheet.Past
Cells.Selec
Selection.Columns.AutoFi
ActiveWindow.SmallScroll Down:=-
Range("A1").Selec
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("PC"
.PivotItems("").Visible = Fals
End Wit
ActiveWindow.SmallScroll Down:=-
Sheets("Sheet2").Selec
Sheets("Sheet2").Name = "Summary
Range("A1").Selec
Sheets("Sheet3").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
End Sub


--
Goobies
------------------------------------------------------------------------
Goobies's Profile: http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=506683

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Loop in my macro doesn't seem to be running; however, it compiles fine...

Bump! Any help is appreciated!


--
Goobies
------------------------------------------------------------------------
Goobies's Profile: http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=506683

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Loop in my macro doesn't seem to be running; however, it compiles fine...
With Rows(i).Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlMedium
ColorIndex = xlAutomatic
End With

should be

With Rows(i).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

just to demonstrate, this works fine:

Sub AABB()
For i = 10 To 15
With Rows(i).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
End Sub

--
Regards,
Tom Ogilvy



"Goobies" wrote in
message ...

avveerkar Wrote:
That is right. Instead of Rows("i:i") just say rows(i) and it should
work

A V Veerkar

I tried this but still no luck. Nothing happens. I've attached the
macro in it's entirety in case something else is keeping this loop from
doing anything. Most of the code was generated using the record macro
button in excell so please excuse the .select this and .select that...
I haven't had a chance to clean up that part of the code. Also,
Everthing before and after the For loop executes properly, it just
seems the loop itself is doing nothing...? I've highlighted the loop
in blue:

Dim i As Integer
Dim j As Integer

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Sheets("output").Select
Sheets.Add
Sheets("output").Select
Columns("B:B").Select
Selection.Copy
Sheets("Sheet1").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=14
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("B:B").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=16
Columns("AG:AG").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("C:C").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=31
Columns("BL:BL").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("D:D").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "PC"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1000"),
Type:=xlFillDefault
Range("C2:C1000").Select
ActiveWindow.SmallScroll Down:=-1098
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy"
Cells.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal

For i = 2 To 1000
j = i + 1
If ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value < ""
Then
If ThisWorkbook.Worksheets("Sheet1").Cells(i, "C").Value <
ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value Then
With Rows(i).Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlMedium
ColorIndex = xlAutomatic
End With
End If
End If
Next i

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!R1C1:R1000C5").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="PC"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("PC").Orientation =
_
xlDataField
Sheets("Sheet1").Select
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
_
"[output.csv]Sheet2!PivotTable1", TableDestination:="",
TableName:= _
"PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Owner"

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Owner").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Owner")
PivotItems("(blank)").Visible = False
End With
Range("A5").Select
ActiveSheet.PivotTables("PivotTable2").PivotSelect "",
xlDataAndLabel
Selection.Copy
Sheets("Sheet2").Select
Range("D3").Select
ActiveSheet.Paste
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.SmallScroll Down:=-6
Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("PC")
PivotItems("").Visible = False
End With
ActiveWindow.SmallScroll Down:=-2
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Summary"
Range("A1").Select
Sheets("Sheet3").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
End Sub


--
Goobies
------------------------------------------------------------------------
Goobies's Profile:
http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=506683



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Loop in my macro doesn't seem to be running; however, it compiles fine...

I figured out what the problem was!. BTW thanks all for the advice. It
turns out since my macro is saved in my Personal macro workbook I had to
use Activeworkbook instead of Thisworkbook.


Tom Ogilvy Wrote:
With Rows(i).Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlMedium
ColorIndex = xlAutomatic
End With

should be

With Rows(i).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

just to demonstrate, this works fine:

Sub AABB()
For i = 10 To 15
With Rows(i).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
End Sub

--
Regards,
Tom Ogilvy



"Goobies" wrote
in
message ...

avveerkar Wrote:
That is right. Instead of Rows("i:i") just say rows(i) and it
should
work

A V Veerkar

I tried this but still no luck. Nothing happens. I've attached the
macro in it's entirety in case something else is keeping this loop
from
doing anything. Most of the code was generated using the record
macro
button in excell so please excuse the .select this and .select
that...
I haven't had a chance to clean up that part of the code. Also,
Everthing before and after the For loop executes properly, it just
seems the loop itself is doing nothing...? I've highlighted the
loop
in blue:

Dim i As Integer
Dim j As Integer

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Sheets("output").Select
Sheets.Add
Sheets("output").Select
Columns("B:B").Select
Selection.Copy
Sheets("Sheet1").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=14
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("B:B").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=16
Columns("AG:AG").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("C:C").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=31
Columns("BL:BL").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("D:D").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "PC"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1000"),
Type:=xlFillDefault
Range("C2:C1000").Select
ActiveWindow.SmallScroll Down:=-1098
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy"
Cells.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal

For i = 2 To 1000
j = i + 1
If ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value < ""
Then
If ThisWorkbook.Worksheets("Sheet1").Cells(i, "C").Value <
ThisWorkbook.Worksheets("Sheet1").Cells(j, "C").Value Then
With Rows(i).Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlMedium
ColorIndex = xlAutomatic
End With
End If
End If
Next i

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!R1C1:R1000C5").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="PC"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("PC").Orientation
=
_
xlDataField
Sheets("Sheet1").Select
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
_
"[output.csv]Sheet2!PivotTable1", TableDestination:="",
TableName:= _
"PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Owner"


ActiveSheet.PivotTables("PivotTable2").PivotFields ("Owner").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Owner")
PivotItems("(blank)").Visible = False
End With
Range("A5").Select
ActiveSheet.PivotTables("PivotTable2").PivotSelect "",
xlDataAndLabel
Selection.Copy
Sheets("Sheet2").Select
Range("D3").Select
ActiveSheet.Paste
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.SmallScroll Down:=-6
Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("PC")
PivotItems("").Visible = False
End With
ActiveWindow.SmallScroll Down:=-2
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Summary"
Range("A1").Select
Sheets("Sheet3").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
End Sub


--
Goobies

------------------------------------------------------------------------
Goobies's Profile:
http://www.excelforum.com/member.php...o&userid=30768
View this thread:
http://www.excelforum.com/showthread...hreadid=506683



--
Goobies
------------------------------------------------------------------------
Goobies's Profile: http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=506683

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear 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
Excel Macro runs fine, but freezes if I try to do ANYTHING else whileit's running Rruffpaw Setting up and Configuration of Excel 0 August 3rd 11 08:31 PM
Macro fine Run fine from Select but not from KB Shortcut? [email protected] Excel Discussion (Misc queries) 8 August 31st 06 02:06 AM
Macro hangs up often but sometimes works fine Jeff Excel Worksheet Functions 3 June 13th 06 01:01 PM
Macro Compiles Sheets to One Book...Small Modification Needed TEAM[_7_] Excel Programming 1 September 15th 05 06:12 PM
Macro works fine in xl2002 but does not in xl 2000 Nolin[_2_] Excel Programming 1 February 25th 04 05:58 PM


All times are GMT +1. The time now is 09:16 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement