lundi 25 mars 2019

VBA for loop only returning one value when there are more that meet the criteria

I am trying to transfer stock transactions from a transaction workbook to another book that has the formatting i want. I want to be able to change the client name and stock at the top of the code so it makes it easier to run for multiple people. the problem is that when i run this it only returns one date in my formatted book when i can see that there are 3 stock trades for the given ticker in the transaction book. it seems like the FOR function isn't looping through all the rows in the transaction book but im not sure why

Sub SortTransactionData()


Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Allen Smith Transactions.xlsx")
Set ws = wb.Sheets("Sheet1")

Dim wb1 As Workbook 
Dim ws1 As Worksheet

Set wb1 = Workbooks("Allen Smith HI.xlsm")
Set ws1 = wb1.Sheets("MO")

Dim ticker As String
ticker = ws1.Range("A2")

Dim a As Integer
a = ws.Cells(Rows.Count, 6).End(xlUp).Row

Dim b As Integer
b = Application.WorksheetFunction.CountIf(ws1.Range("B1:B7"), "*")

For i = 2 To a
    'copy date for stock transaction'
    If ws.Cells(i, 6).Value = ticker Then
    ws1.Cells(b + 1, 2).Value = ws.Cells(i, 1)
    End If

Next
End Sub

Aucun commentaire:

Enregistrer un commentaire