Loop in my macro doesn't seem to be running; however, it compiles fine...

G

Goobies

I have a loop in my code which compiles fine; however, it doesn't see
to be running (or doing anything?):confused: . 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
 
S

sjoo.kwak

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
 
A

avveerkar

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
Click to expand...

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

A V Veerka
 
G

Goobies

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

A V Veerka
Click to expand...

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:=xlDatabase, 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
 
T

Tom Ogilvy

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 said:
That is right. Instead of Rows("i:i") just say rows(i) and it should
work

A V Veerkar
Click to expand...

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:=xlDatabase, 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
Click to expand...
 
G

Goobies

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 said:
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



in
message news:[email protected]...
That is right. Instead of Rows("i:i") just say rows(i) and it should
work

A V Veerkar
Click to expand...

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:=xlDatabase, 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?action=getinfo&userid=30768
View this thread: http://www.excelforum.com/showthread.php?threadid=506683
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
converting a for next loop into a for each loop 2
Cut bottom 300 rows to next sheet & repeat 8
Code rounds to whole numbers, need it to stay with decimals 4
Code to name numerous ranges 11
Code to Concat a row in loop 2
InputBox for column letter problem 2
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy 11

Top