Posts by nilem

Function ConcNum(rng As Range) As String
Dim s$, x, i&, bu As Boolean
x = Split(rng.Value & " ")
If UBound(x) = 0 Then ConcNum = x(0): Exit Function
For i = 0 To UBound(x) - 1
    s = s & ", " & Trim(x(i))
    Do While Val(x(i)) = Val(x(i + 1)) - 1
        bu = True: i = i + 1
    Loop
    If bu Then s = s & "-" & Trim(x(i)): bu = False
Next i
ConcNum = Mid(s, 3)
End Function

Sub ttt()
Dim x, i&, sp
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
    x = .Value
    For i = 1 To UBound(x)
        sp = Split(x(i, 1))
        If UBound(sp) > 0 Then x(i, 1) = sp(0) & "-" & sp(UBound(sp))
    Next i
    .Offset(, 1).Value = x
End With
End Sub

Function GenPopRSeq(rInd As Range, rDrq As Range)
Dim ind, frq, i&, j&, t$, sp
ind = rInd.Value: frq = rDrq.Value


For i = 1 To UBound(ind)
    For j = 1 To frq(i, 1)
        t = t & "~" & ind(i, 1)
    Next j
Next i


sp = Split(Mid(t, 2), "~")
Randomize
For i = LBound(sp) To UBound(sp)
    j = Int((UBound(sp) + 1) * Rnd)
    t = sp(i)
    
    sp(i) = sp(j)
    sp(j) = t
Next i
GenPopRSeq = Application.Transpose(sp)
End Function

...
Set BBHNAV = Worksheets("BBH NAV")


With BBHNAV
    LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
    'For each row in the 'BBH NAV' sheet...
    '    For n = 2 To LastRow
    '        'Unique Identifier (get a complete list of identifiers as they are used in calculation for column b which is a fill down instead of looping 1 cell at a time)
    '        .Range("A" & n).Value = .Range("J" & n).Value & .Range("M" & n).Value
    '    Next n
    .Range("A2:A" & LastRow).FormulaR1C1 = "=RC[9]&RC[12]"


    'Base Market Value
    .Range("B2").FormulaArray = "=IFERROR(INDEX('T-1 DATA'!G$1:G$1000,MATCH(1,('T-1 DATA'!A$1:A$1000='BBH NAV'!A2)*('T-1 DATA'!D$1:D$1000='BBH NAV'!L2),0),1)," & """NO DATA""" & ")"
    .Range("B2:B" & LastRow).FillDown


    For n = 2 To LastRow
'        'Base Market Value
'        .Range("B2").FormulaArray = "=IFERROR(INDEX('T-1 DATA'!G:G,MATCH(1,('T-1 DATA'!A:A='BBH NAV'!A2)*('T-1 DATA'!D:D='BBH NAV'!L2),0),1)," & """NO DATA""" & ")"
'        .Range("B2:B" & LastRow).FillDown
...

Function Kramerica2(rA As Range, rB As Range, rC As Range)
Dim a, b, c, i&, j&, s$, t$
a = rA.Value
b = rB.Value
c = rC.Value
For j = 1 To UBound(a, 2)
    For i = 1 To UBound(b)
        If b(i, j) <> 0 Then
            t = "~" & a(1, j) & b(i, j) & c(1, j)
            If InStr(s, t) = 0 Then s = s & t
        End If
    Next i
Next j
Kramerica2 = Application.Transpose(Split(Mid(s, 2), "~"))
End Function

Function ConcatenarIntervalos(items As Range, items2 As Range, limite As Integer) As Variant
'Dim i As Integer
Dim j As Integer, result As String
'Dim k As Integer


'i = items.Row
'k = items2.Row
limite = WorksheetFunction.Min(items2.Columns.Count, limite)


For j = 4 To limite + 3
'    result = result & Cells(i, j) & Cells(k, j)
    result = result & items(1, j) & items2(1, j)
Next j


ConcatenarIntervalos = result
End Function