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