Hello - New to VBA and I've been trying to parse through a time based price data dump, capture it, run a calculation then write the results on another tab within the same spreadsheet. So far, I've been able to locate and capture the data but unable to write it to the destination tab. Here's what I have so far. In the STEP 2 section, while using a Watch Window I can see all the correct information, appropriate cell in destination worksheet selected but nothing is written, nor color changed. Thanks in advance for any help.
VBA Code:
Sub T1()
'Step 1
'========
'Scan the Buy Cells for a Value then
'move down until a corresponding sell value is found
'900 rows is the built-in failsafe because while statements can be dangerous
Dim Col As Integer
Dim Row As Integer
Dim Buy As String
Dim Sell As String
Dim Dash_Row As Integer
Dim Dash_Col As Integer
Dim Profit_Loss As Single
Dim r As Range
Dim Dash_r As Range
Set r = Range("R5:S900") ' Sets window of data to be analyzed (offset)
Row = 1
Col = 1
Set Dash_r = Range("A1:AP100") ' Sets Range for results to be written
Dash_Row = 3
Dash_Col = 2
While Row < 901
Sheets("TEST").Select
While r.Cells(Row, Col).Value = 0 ' Iterate Buy Column Until First Buy price is found
Row = Row + 1
If Row > 900 Then Exit Sub
Buy = r.Cells(Row, Col).Value
Wend
Col = Col + 6 ' Shifts to Sell Column
While r.Cells(Row, Col).Value = 0 ' Iterate Sell Column Until First Sell price is found
Row = Row + 1
If Row > 900 Then Exit Sub
Sell = r.Cells(Row, Col).Value
Wend
'Step 2
'========
'Calculate profit and then put value into corresponding row in dashboard
'Also, highlight the cell with an arbitrary color, green or red in this example.
Profit_Loss = Buy - Sell
Sheets("Dashboard").Select
If Profit_Loss > 0 Then
Dash_r.Cells(Dash_Row, Dash_Col).Interior.Color = vbGreen
Dash_r.Cells(Dash_Row, Dash_Col).Value = Profit_Loss
Else
Dash_r.Cells(Dash_Row, Dash_Col).Interior.Color = vbRed
Dash_r.Cells(Dash_Row, Dash_Col).Value = Profit_Loss
End If
Sheets("TEST").Select
