Greetings,
I am beginning to run the script below line by line with F8. I am watching the different variables with Add Watch. When I get to line:
The value of A goes to "0" and creates errors for the subsequent variable values that use A as an independent. Any ideas why A goes from a non-zero value (in the case of my sheet 2534) to 0?
I am beginning to run the script below line by line with F8. I am watching the different variables with Add Watch. When I get to line:
Code:
A = E
The value of A goes to "0" and creates errors for the subsequent variable values that use A as an independent. Any ideas why A goes from a non-zero value (in the case of my sheet 2534) to 0?
Code:
Sub TRENDSHEET()
Dim wsPA As Worksheet
Dim wsTA As Worksheet
Dim PA As Workbook
Dim TA As Workbook
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim RowLoop1 As Long
Dim AA As Range
Dim DD As Range
Set TA = Workbooks("COMM_TREND.xls")
Set PA = Workbooks("COMM_PA.xls")
For Each wsPA In PA.Worksheets
Set wsTA = TA.Worksheets(wsPA.Name)
wsTA.Activate
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
wsPA.Activate
A = wsPA.Cells(Rows.Count, "A").End(xlUp).Row
Do Until A = 2
If wsPA.Cells(A, "E").Value > Application.Large(wsPA.Cells((A - 10), "E").Resize(21), 2) Then
Set AA = wsPA.Cells(A, "E")
Set DD = wsPA.Cells(A, "A")
G = AA.Row
H = wsPA.Cells(A, "A").Row
wsTA.Activate
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
Selection.Value = "ACTIVE"
Range("A1").Select
Selection.End(xlDown).Offset(0, 1).Select
Selection.Value = DD.Value
Range("A1").End(xlDown).Offset(0, 2).Select
Selection.Value = AA.Value
A = E
K = E - 10
Do Until K = 2
I = 2
For RowLoop1 = G To I
H = wsPA.Cells(E, "A").Row
B = (wsPA.Cells(K, "E").Value - wsPA.Cells(G, "E").Value) / (wsPA.Cells(K, "A").Value - wsPA.Cells(G, "A").Value)
C = wsPA.Range(wsPA.Cells(K, "A"), wsPA.Cells(G, "A")).Rows.Count
I = (wsPA.Cells(K, "E").Value - wsPA.Cells(G, "E").Value)
F = A - (A - H)
J = wsPA.Range(wsPA.Cells(H, "A").Value - wsPA.Cells(G, "A").Value).Rows.Count
If wsPA.Cells(H, "E").Value > AA.Value + (B * F) And F < C + 10 Then
ElseIf wsPA.Cells(H, "E").Value > AA.Value + (B * F) And F > C + 10 Then
wsTA.Range("A1").End(xlDown).Offset(1, 0).Select
Selection.Value = "ACTIVE"
wsPA.Cells(H, "A").Value.Copy
wsTA.Range("A1").End(xlDown).Offset(0, 1).Select
Selection.Paste PasteSpecial:=xlValues
AA.Value.Copy
wsTA.Range("A1").End(xlDown).Offset(0, 2).Select
Selection.Paste PasteSpecial:=xlValues
wsPA.Cells(K, "A").Value.Copy
wsTA.Range("A1").End(xlDown).Offset(0, 3).Select
Selection.Paste PasteSpecial:=xlValues
wsPA.Cells(K, "E").Value.Copy
wsTA.Range("A1").End(xlDown).Offset(0, 4).Select
Selection.Paste PasteSpecial:=xlValues
wsTA.Range("A1").End(xlDown).Offset(0, 5).Value = C
wsTA.Range("A1").End(xlDown).Offset(0, 6).Value = I
wsTA.Range("A1").End(xlDown).Offset(0, 7).Value = B
wsTA.Range("A1").End(xlDown).Offset(0, 8).Select
Selection.Paste PasteSpecial:=xlValues
wsPA.Cells(H, "A").Value.Copy
wsTA.Range("A1").End(xlDown).Offset(0, 9).Select
Selection.Paste PasteSpecial:=xlValues
ElseIf wsPA.Cells(H, "E").Value < AA.Value + (B * F) Then
End If
Next RowLoop1
K = K - 1
Loop
Else
A = A - 1
End If
Loop
Next wsPA
End Sub