VBA Beginner - Can see data stored in Watch Window but not written

starkc69

New Member
Joined
Dec 20, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Set Dash_r = Sheets("Dashboard").Range("A1:AP100") ' Sets Range for results to be written
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top