I am running a code that will go through a list of PortNames and find the one corresponding to the PortName in another list in a different workbook. The corresponding PortName will then use the find function to find the row number that corresponds to that particular PortName. I am running into trouble on this piece of code:
Code
AssetAllocateRow = assetAllocate.Sheets("Asset_Allocation").Range(assetAllocate.Sheets("Asset_Allocation").Cells(1, 1), assetAllocate.Sheets("Asset_Allocation").Cells(numAssetAllocateRows, 1)).Find(PortName, LookIn:=xlValues, lookat:=xlWhole).Row
This is the full code leading up to that statement:
Code
Sub AssetsbyBusinessGroup(mmPortName As String, NewSheetName As String, CondenseSheet As String)
' Presents the condensed asset data in a new sheet, breaking up cash flows by business group
' The distribution is given by the input
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Aggregate As Workbook, assetAllocate As Workbook
Dim WS As Worksheet, errCatch As Worksheet
Dim startAll As Integer, endAll As Integer
Dim filePathAssetAllocate As String
Dim numRowsCondense As Long
Dim PortName As String, PortType As String, AssetAllocateRow As Integer
Dim PercentageAsset As String, EditPercentageAsset As Long, PercetageLength As Integer, numAssetAllocateRows As Integer
Set Aggregate = ThisWorkbook
numRowsCondense = Aggregate.Sheets(CondenseSheet).Range("A1").End(xlDown).Row
filePathAssetAllocate = Aggregate.Sheets("Inputs").Range("E3").Value
Set assetAllocate = Workbooks.Open(filePathAssetAllocate)
numAssetAllocateRows = assetAllocate.Sheets("Asset_Allocation").Range("A1").End(xlDown).Row
startAll = assetAllocate.Sheets("Asset_Allocation").Rows(2).Find("%*", LookIn:=xlValues, lookat:=xlWhole).Column
endAll = assetAllocate.Sheets("Asset_Allocation").Rows(2).End(xlToRight).Column
Sheets(CondenseSheet).Cells(1, 10).Value = "Business Group"
For j = 2 To numRowsCondense
PortName = Aggregate.Sheets(CondenseSheet).Cells(j, 7).Value
PortType = Aggregate.Sheets(CondenseSheet).Cells(j, 8).Value
If PortName = "Money Market" Then
PortName = mmPortName
End If
AssetAllocateRow = assetAllocate.Sheets("Asset_Allocation").Range(assetAllocate.Sheets("Asset_Allocation").Cells(1, 1), assetAllocate.Sheets("Asset_Allocation").Cells(numAssetAllocateRows, 1)).Find(PortName, LookIn:=xlValues, lookat:=xlWhole).Row
For i = startAll To endAll
PercentageAsset = assetAllocate.Sheets("Asset_Allocation").Cells(AssetAllocateRow, i)
PercentageLength = Len(PercetageAsset)
Select Case PercentageLength
Case 4
EditPercentageAsset = Left(PercentageAsset, 3)
Case 3
EditPercentageAsset = Left(PercentageAsset, 2)
Case Else
EditPercentageAsset = Left(PercentageAsset, 1)
End Select
If EditPercentageAsset = "100" Then
Aggregate.Sheets(CondenseSheet).Cells(j, 10).Value = assetAllocate.Sheets("Asset_Allocation").Cells(1, i).Value
ElseIf EditPercentageAsset = "0" Then
GoTo NextPercentage
Else
Storage = 100 - EditPercentageAsset
If Storage = 0 Then
Aggregate.Sheets(CondenseSheet).Cells(j, 2).Value = Aggregate.Sheets(CondenseSheet).Cells(j, 2).Value * EditPercentageAsset * 0.01
Aggregate.Sheets(CondenseSheet).Cells(j, 10).Value = assetAllocate.Sheets("Asset_Allocation").Cells(1, i).Value
Else
'Aggregate.Sheets(CondenseSheet).Row(j).EntireRow.Insert
Aggregate.Sheets(CondenseSheet).Range(Sheets(CondenseSheet).Cells(j, 1), Sheets(CondenseSheet).Cells(j, 10)).Value = Aggregate.Sheets(CondenseSheet).Range(Sheets(CondenseSheet).Cells(j + 1, 1), Sheets(CondenseSheet).Cells(j + 1, 10)).Value
Aggregate.Sheets(CondenseSheet).Cells(j, 2).Value = Aggregate.Sheets(CondenseSheet).Cells(j, 2).Value * EditPercentageAsset * 0.01
Aggregate.Sheets(CondenseSheet).Cells(j, 10).Value = assetAllocate.Sheets("Asset_Allocation").Cells(1, i).Value
Aggregate.Sheets(CondenseSheet).Cells(j + 1, 2).Value = Aggregate.Sheets(CondenseSheet).Cells(j + 1, 2).Value - Aggregate.Sheets(CondenseSheet).Cells(j, 2).Value
End If
End If
NextPercentage:
Next i
Next j