Originally Posted by
mdmackillop
Use Long instead of Integer. Excel does this when it compiles
If I etc are Long or Integer, there can be no spaces to trim, "A" & I will form a valid address without any other modification.
Make use of Variables for your worksheets. Keeps things cleaner; also With statements.
[VBA]
Private Sub CommandButton1_Click()
Dim I As Long
Dim J As Long
Dim K As Long
Dim L As Long
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
For I = 61 To 1 Step -1 '61 being the last row with data in it
With WS1
J = .Range("C" & I)
K = .Range("D" & I)
End With
For L = J To K 'now we're going to write the values out to Sheet 2
With WS2
.Range("A" & L).Value = WS1.Range("A" & I).Value
.Range("B" & L).Value = 1
.Range("C" & L).Value = L
.Range("A" & L).Value = "PCT:" & WS1.Range("A" & I).Value
.Range("B" & L).Value = "BT:" & WS1.Range("B" & I).Value
End With
Next L
Next I
End Sub
[/VBA]