VBA If statement to continue

[dinunan]

Hi All,

I've a trading journal in one of the sheets. Entries are based on strategies like AT, SS, SELF etc in one of the column. Now I have separate sheets as well for AT, SS, SELF etc. End of the day I want to segregate my trades in separate sheets as per strategy. I've written the code for eg fetch the entries associated with AT in its dedicated sheet. If statement works as long as it finds AT but doesn't continue to next line if it finds anything else (other than AT). I want code to continue till last entry in the main sheet.

My code is attached below. Any help is appreciated.

Regards.
Dinesh.

Sub CopyTradesFromAT()

Dim rng As Range
Dim row As Range

Sheets("ATPA").Activate
Range("A408").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

Sheets("Trade Diary").Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlToRight)).Select
   
   Set rng = Selection

For Each row In rng.Rows

    If ActiveCell.Offset(0, 4).Value = " " Then Exit Sub
 
    If ActiveCell.Offset(0, 4).Value = "AT" Then
        ActiveCell.Range("A1:B1").Select
        Selection.Copy
       
        Worksheets("ATPA").Activate
        ActiveCell.Activate
       Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
       ActiveCell.Offset(0, 3).Activate
       
        Worksheets("Trade Diary").Activate
        Application.CutCopyMode = False
        ActiveCell.Range("H1:J1").Select
        Selection.Copy
   
        Worksheets("ATPA").Activate
        ActiveCell.Activate
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
       ActiveCell.Offset(0, 3).Activate
       
        Worksheets("Trade Diary").Activate
        Application.CutCopyMode = False
        ActiveCell.Offset(0, 13).Range("A1").Select
        Selection.Copy
   
        Worksheets("ATPA").Activate
        ActiveCell.Activate
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
       ActiveCell.Offset(1, -6).Activate
       
        Worksheets("Trade Diary").Activate
        Application.CutCopyMode = False
        ActiveCell.Offset(1, -20).Range("A1").Select

End If

Next row
   

    Worksheets("ATPA").Activate
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Alex Blakenburg]

Your macro does not define the starting point and just relies on ActiveCell. Is that really the intention ?

Assuming its not try the below:

Sub CopyTradesFromAT()

Dim wsTrades As Worksheet, wsATPA As Worksheet
Dim rngTrades As Range
Dim rowLastTrades As Long, rowNextATPA As Long, colLastTrades As Long
Dim row As Range

Set wsATPA = Sheets("ATPA")
With wsATPA
    rowNextATPA = .Range("A" & Rows.Count).End(xlUp).row + 1
End With

Sheets("Trade Diary").Select

Set wsTrades = Sheets("Trade Diary")
With wsTrades
    rowLastTrades = .Cells(Rows.Count, "A").End(xlUp).row
    colLastTrades = .Cells(1, Columns.Count).End(xlToLeft).Column
    Set rngTrades = .Range(.Cells(1, "A"), .Cells(rowLastTrades, colLastTrades))
End With

For Each row In rngTrades.Rows

    If row.Cells(5).Value = " " Then Exit Sub
 
    If row.Cells(5).Value = "AT" Then
        With wsATPA
            .Cells(rowNextATPA, 1).Resize(, 2).Value = row.Cells(1).Resize(, 2).Value           'A:B
            .Cells(rowNextATPA, 1).Resize(, 2).NumberFormat = row.Cells(1).Resize(, 2).NumberFormat
           
            .Cells(rowNextATPA, 4).Resize(, 3).Value = row.Cells(8).Resize(, 3).Value           'H:J
            .Cells(rowNextATPA, 4).Resize(, 3).NumberFormat = row.Cells(8).Resize(, 3).NumberFormat
           
            .Cells(rowNextATPA, 7).Value = row.Cells(21).Value                                  'U
            .Cells(rowNextATPA, 7).NumberFormat = row.Cells(21).NumberFormat
           
            rowNextATPA = rowNextATPA + 1
        End With

End If

Next row
  
Worksheets("ATPA").Activate
End Sub

 

[dinunan]

Master sheet "Trade Diary" has trade entries date wise in a column. Sometimes there are 5 trades and sometimes 15(unknown), so I am not able to fix starting point in the sheet. So I decided to keep my selection in Trade Diary sheet from where I would like to update the data (active cell) and then run the code.

 

[mumps]

It seems like your code takes care of the "AT" strategy but not any of the others Like "SS" and "SELF". It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[dinunan]

Exactly mumps. When code encounters anything other than AT (SS/SELF etc), it stops and nothing happens. I am trying additional if/elseif commands but not working.
The file is shared now on dropbox for reference.

Dropbox

www.dropbox.com

 

[mumps]

Thanks for the file. Currently you have sheets for "SS" and "AT" but not for "SELF". Is it possible that some of the sheets may be missing and need to be created? Will you only be using the values for "SS", "AT" and "SELF" or will there be any others? If there will be others, what are those values? Also, it looks like you want to work with only the data for the month of August. Do you want to be prompted for the month you want to process or do you want to do all months at the same time?

 

[dinunan]

Yes I would like to make at least two more sheets like SS and ZLSMA. New sheet creation depends if I attend any new course, I may have to create. All these months I was doing this manually so I thought of making it VBA so my method is very crude way. If I get prompted for months and all would be nice feature to have. I keep one file for one financial year having months one below previous.
Thanks in advance.

 

[mumps]

Could you please clarify which columns on the "Trade Diary" sheet you want to copy and where on the other sheets you want to paste that data. Since the column headers on the "Trade Diary" sheet don't match the headers on the other sheets, this is hard to determine. Please use a few examples from your data referring to specific cells, rows, columns and sheets.

 

[dinunan]

I've made the necessary changes in the original file to make it more clear. From Trade Diary I need to copy Rating/Instrument/Entry Reason/P. Qty/P. Date/P. Rate/S. Rate. Please check the link, file is upended with appropriate column headers. Refer to new sheet "SELF" where I want to populate the data similar to other sheets.