mercredi 20 septembre 2017

Nested IF statement inside For loops in VBA

My other macro scrapes mutual fund sizes from web and returns the values in Worksheets("Updater").Cells(xx,4) (so column C). The macro scrapes also the date in which the fund has been updated on morningstar and returns it in column F on the same sheet. I've got 83 fund names in column A.

On a sheet called "Tracker" I've got the fund names transposed. They start from cell (1,4) and go to (1,86). In column B I've got DATEVALUE of dates in column C. There're some issues with the date formats so I'll just hide the column B later.

The below macro works as it is, but when you uncomment lines 11 and 22, it doesn't work as intended anymore. Idea is to look at the fund sizes on sheet "Updater" and return the fund size to the corresponding date on sheet "Tracker". As said, this works.

However, with the nested if statement, I'd want to modify the macro so it would keep all the cells that contain other than "-" untouched. This way I would start to cumulate the fund size data when the macro is executed on a daily basis and doesn't overwrite the old fund sizes with "-".

Now the macro flips the funds who knows where and I simply can't solve this nested if statement problem. Is this even the correct/best way to get where I want?

Sub fundSizeTracker()

    Dim f As Integer
    Dim numRows As Integer

    numRows = 86
    f = 2   'Sheet "Updater" fund names & sizes

        For m = 2 To 4 'Sheet "Tracker" row number, starting at rowNo 2
                For i = 4 To numRows 'Sheet "Tracker" column number (set to include all 83 funds)
'                    If Worksheets("Tracker").Cells(m, i) = "-" Then
    '               Are Datevalues same?                'Tracker datevalue                        'Updater datevalue
                        If StrComp(Worksheets("Tracker").Cells(m, 2).Value, Worksheets("Updater").Cells(f, 13).Value) = 0 Then
                            Worksheets("Tracker").Cells(m, i) = Worksheets("Updater").Cells(f, 4).Value
                        Else
                            Worksheets("Tracker").Cells(m, i) = "-"
                        End If
                        If f = numRows - 1 Then
                            f = 2
                        End If
                        f = f + 1
'                    End If
                Next i
        Next m

End Sub

Aucun commentaire:

Enregistrer un commentaire