PDA

View Full Version : Solved: Eliminating a Line or cell from code


Scooter172
06-12-2011, 07:04 PM
I have to change / remove some of the areas that are transfered using this code ( I inherited). I need to eliminate the transfer of [lrvLine4Start] and [lrvLine4Stop] Time information. Those range names have been eliminated from the page referenced. Any Ideas?



Sub sendWarrantDetail()
Application.ScreenUpdating = False
Workbooks.Open Filename:="H:\LRT\Ops Table Swap Folder\OpsTableSwapFile.xlsx", UpdateLinks:=3, writeresPassword:="6328"

'clear the swap file warrant
'Line 2
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2speed].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2track].ClearContents
'Line3
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3track].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3eic].ClearContents
'Line 4
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].ClearContents
For c = 1 To 6
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(c, 1) = "No"
Next c
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].ClearContents
'Line 5
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5track].ClearContents
'Line 6
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].ClearContents
'move the data
'Line 1
'last priority: will require investigation into how best to have Excel know it is voided

Dim intI As Integer 'line number on active warrant
Dim intJ As Integer 'line number to use in swap file warrant
Dim nextLetter As Integer 'code for next letter to use on warrant line

'Line 2
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line2Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2speed].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2speed].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2track].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(2, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop

'Line 3
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line3Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3eic].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3eic].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(3, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop

'Line 4
intI = 1 'active warrant line index
intJ = 1 'swap warrant line index
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line4Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4letter].Cells(intI, 1)
'IF the Line 4 has stop &/or start times after midnight, THEN change the times accordingly, ELSE copy them as is
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "x" Then
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "x" Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = 1
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1)
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4hand].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4mc].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 6 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(4, 1)
intJ = intJ + 1
End If
Do While intJ <= 6
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop

'Line 5
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line5Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5track].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(5, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop

'Line 6
intI = 1
intJ = 1
Dim intK As Integer 'text index for swap warrant
Dim intL As Integer 'text index for active warrant
intK = 1
intL = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line6Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6letter].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 1, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 1, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 2, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 2, 1)
intJ = intJ + 1
intK = intK + 3
End If
intI = intI + 1
intL = intL + 3
Next c
If intK <= 7 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(6, 1)
intK = intK + 3
End If
Do While intK <= 7 'as is this Do Loop will run at most once. Programmed this way for consistency with other lines and to allow easier expansion if more line 6's are added
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK - 3, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intL, 1) = 97
End If
intK = intK + 3
Loop

Workbooks("OpsTableSwapFile.xlsx").Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
Simon Lloyd
06-12-2011, 11:06 PM
It "looks" like a case of replacing thisFor Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line4Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4letter].Cells(intI, 1)
'IF the Line 4 has stop &/or start times after midnight, THEN change the times accordingly, ELSE copy them as is
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "x" Then
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "x" Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = 1
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1)
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4hand].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4mc].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
with thisFor Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line4Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4hand].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4mc].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
Scooter172
06-13-2011, 02:10 PM
Thanks, Now this section of code was used to Fill Three Lettered Lines, (Line 6 - A-? ) and each Lettered line had two lines of text. between each Lettered line, they skipped a Line for seperation purposes. But now they want more Lettered Lines, and I have increased this to 6 of which each has two lines of Text. I believe it is as simple as adjusting the code to count down correctly?? I have already changed the Named ranges to encompass the correct areas. Is it the (int + ?,?) that does this? Total amount of lines of text is now 12, up from 6.

'Line 6
intI = 1
intJ = 1
Dim intK As Integer 'text index for swap warrant
Dim intL As Integer 'text index for active warrant
intK = 1
intL = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line6Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6letter].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 1, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 1, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 2, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 2, 1)
intJ = intJ + 1
intK = intK + 3
End If
intI = intI + 1
intL = intL + 3
Next c
If intK <= 7 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(6, 1)
intK = intK + 3
End If
Do While intK <= 7 'as is this Do Loop will run at most once. Programmed this way for consistency with other lines and to allow easier expansion if more line 6's are added
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK - 3, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intL, 1) = 97
End If
intK = intK + 3
Loop

Workbooks("OpsTableSwapFile.xlsx").Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub



Scott :beerchug:
Simon Lloyd
06-14-2011, 01:28 AM
Im not entirely sure what you're asking, however, where you see something like intK = intK + 3 this means that it is increased by 3 every time the code runs to this line, when you see intK <= 7 it shows that the events (where you see IF or DO While) will only run if intK is equal to or below 7, so taking that intK is started at 1 and you add 3 each time means that the loop...etc would only run twice, try experimenting with this number and see how you go on, try 17 instead of 7 for each instance of intK <= 7 as a start.
Scooter172
06-16-2011, 08:14 AM
Would it help to send screen shots of the before and after.