Could someone assist me in cracking this VBA and getting this on the Multi Row Formula

[Arjunkp]

Input file and VBA code attached in the workflow 

Formula for % Movement
If mWS.Range("B" & RowNum) = mWS.Range("B" & RowNum - 1) And mWS.Range("C" & RowNum) = mWS.Range("C" & RowNum - 1) Then
If mWS.Range("G" & RowNum - 1).Value = 0 Or mWS.Range("G" & RowNum).Value = 0 Then
If mWS.Range("G" & RowNum - 1).Value = 0 And mWS.Range("G" & RowNum).Value <> 0 Then
mWS.Range("I" & RowNum).Value = 1
Else
If mWS.Range("G" & RowNum - 1).Value <> 0 And mWS.Range("G" & RowNum).Value = 0 Then
mWS.Range("I" & RowNum).Value = -1
Else
mWS.Range("I" & RowNum).Value = 0
End If
End If
Else
mWS.Range("I" & RowNum).Formula = "=(G" & RowNum & "-G" & RowNum - 1 & ")/G" & RowNum - 1
End If
Else
mWS.Range("I" & RowNum).Value = 0
End If

Sample Input

Account number	Asset description - long	number	category	Shares	Valuation date	Market price	Unrealized G/L - total
122333	GENERAL 12-31-2049	ABCTNT	Corporate Bonds	1,333.00	2023-08-17	99.855	2.00
122333	GENERAL 12-31-2049	ABCTNT	Corporate Bonds	21.00	2023-08-18	99.825	2.00
122333	PLAINS 12-31-2049	AQLS1N	Corporate Bonds	3.00	2023-08-17	91.79	33.00
122333	PLAINS 12-31-2049	AQLS1N	Corporate Bonds	5.00	2023-08-18	92.25	34.00
122333	PVTPL 04-20-2030	JDJKDKJ	Asset Backed Securities	5,566.00	2023-08-17	81.00126	45.00
122333	PVTPL 04-20-2030	JDJKDKJ	Asset Backed Securities	66.00	2023-08-18	81.00645	465.00
122333	EDGEWATER 12-13-2025	12JDND	Bank Loans	7.00	2023-08-17	94.875	7.00
122333	EDGEWATER 12-13-2025	12JDND	Bank Loans	77.00	2023-08-18	94.977	768.00

[SPetrie · Accepted solution]

I cleaned up the formatting to make the VBA a bit more readable first.

After that, we replace the range reference with Alteryx columns

Since the multi-row will be creating the new column, we can remove explicit references to column I and just have the results instead.

I find it easier to put some of the last conditions first, so my ultimate formula changes some = to be != so I can set the end condition first and then use elseifs to get the rest.

The formula I ended up with is

I multiply by 100 to get it in a percent format but you can alter that to suit your tastes. Its also not rounded yet and you can set it as fixed width decimal instead of double if you want or round it later, up to you.

[Arjunkp]

Thank you so much. Much Appriciated

[Arjunkp]

Hello, I'm new to VBA scripting. There is a minor piece of work that has to be completed. Could you also help assist me in cracking this code and having it in multi row formula?

'Formula for New Security Indicator
If mWS.Range("C" & RowNum) = mWS.Range("C" & RowNum - 1) Or mWS.Range("C" & RowNum) = mWS.Range("C" & RowNum + 1) Then
If mWS.Range("E" & RowNum) <> 0 And mWS.Range("E" & RowNum + 1) = 0 Then
If mWS.Range("F" & RowNum) = vMinimum And IsEmpty(mWS.Range("C" & RowNum).Value) = False Then
mWS.Range("M" & RowNum).Value = vDropOff
Else
mWS.Range("M" & RowNum).Value = vUnderscore
End If
Else
mWS.Range("M" & RowNum).Value = vUnderscore
End If
Else
If mWS.Range("F" & RowNum) = vMaximum Then
mWS.Range("M" & RowNum).Value = vNewSecurity
Else
If mWS.Range("F" & RowNum) = vMinimum Then
mWS.Range("M" & RowNum).Value = vDropOff
Else
mWS.Range("M" & RowNum).Value = vUnderscore
End If
End If
End If


'Formula for Unrealised G/L Variation, Accrued Income Variation, Bkt/Mkt, Accr/Mkt & Zero Price
If mWS.Range("C" & RowNum) = "" Then

If mWS.Range("F" & RowNum) = vMinimum Then
mWS.Range("L" & RowNum).Formula = "=0-H" & RowNum
mWS.Range("V" & RowNum).Formula = "=0-U" & RowNum
'Bkt/Mkt Ratio
mWS.Range("W" & RowNum).Formula = "=O" & RowNum & "/N" & RowNum
'Accr/Mkt Ratio
mWS.Range("Y" & RowNum).Formula = "=U" & RowNum & "/" & "O" & RowNum
Else
mWS.Range("L" & RowNum).Formula = "=H" & RowNum & "-0"
mWS.Range("V" & RowNum).Formula = "=U" & RowNum & "-0"
'Bkt/Mkt Ratio
mWS.Range("W" & RowNum).Formula = "=O" & RowNum & "/N" & RowNum
'Accr/Mkt Ratio
mWS.Range("Y" & RowNum).Formula = "=U" & RowNum & "/" & "O" & RowNum
End If

Else

If mWS.Range("B" & RowNum) = mWS.Range("B" & RowNum - 1) And mWS.Range("C" & RowNum) = mWS.Range("C" & RowNum - 1) Then
mWS.Range("L" & RowNum).Formula = "=H" & RowNum & "-H" & RowNum - 1
mWS.Range("V" & RowNum).Formula = "=U" & RowNum & "-U" & RowNum - 1
'Bkt/Mkt Ratio
mWS.Range("W" & RowNum).Formula = "=O" & RowNum & "/N" & RowNum
'Bkt/Mkt Ratio Change
mWS.Range("X" & RowNum - 1).Formula = "=(W" & RowNum & "-W" & RowNum - 1 & ")/" & "W" & RowNum - 1
mWS.Range("X" & RowNum).Formula = "=(W" & RowNum & "-W" & RowNum - 1 & ")/" & "W" & RowNum - 1
'Accr/Mkt Ratio
mWS.Range("Y" & RowNum).Formula = "=U" & RowNum & "/" & "O" & RowNum
'Accr/Mkt Ratio Change
mWS.Range("Z" & RowNum - 1).Formula = "=(Y" & RowNum & "-Y" & RowNum - 1 & ")/" & "Y" & RowNum - 1
'mWS.Range("Z" & RowNum - 1).Formula = "=Y" & RowNum & "-Y" & RowNum - 1
mWS.Range("Z" & RowNum).Formula = "=(Y" & RowNum & "-Y" & RowNum - 1 & ")/" & "Y" & RowNum - 1
'mWS.Range("Z" & RowNum).Formula = "=Y" & RowNum & "-Y" & RowNum - 1
'Zero Price Exception
If mWS.Range("O" & RowNum) = 0 And mWS.Range("O" & RowNum - 1) <> 0 Then
If mWS.Range("E" & RowNum) <> 0 Then
mWS.Range("AA" & RowNum - 1).Formula = "Zero Price"
mWS.Range("AA" & RowNum).Formula = "Zero Price"
End If
End If

Else
If mWS.Range("M" & RowNum).Value = "New Security" Then
mWS.Range("L" & RowNum).Formula = "=H" & RowNum & "-0"
mWS.Range("V" & RowNum).Formula = "=U" & RowNum & "-0"
'Bkt/Mkt Ratio
mWS.Range("W" & RowNum).Formula = "=O" & RowNum & "/N" & RowNum
'Bkt/Mkt Ratio Change
mWS.Range("X" & RowNum).Formula = "=1-(N" & RowNum & "/O" & RowNum & ")"
'Accr/Mkt Ratio
mWS.Range("Y" & RowNum).Formula = "=U" & RowNum & "/" & "O" & RowNum
'Accr/Mkt Ratio Change
mWS.Range("Z" & RowNum).Formula = "=1-(Y" & RowNum & "/Y" & RowNum & ")"
'Zero Price Exception
If mWS.Range("O" & RowNum) = 0 Then
If mWS.Range("E" & RowNum) <> 0 Then
mWS.Range("AA" & RowNum).Formula = "Zero Price"
End If
End If

Else
If mWS.Range("M" & RowNum).Value = "Drop Off" And mWS.Range("C" & RowNum + 1) <> mWS.Range("C" & RowNum) Then
mWS.Range("L" & RowNum).Formula = "=0-H" & RowNum
mWS.Range("V" & RowNum).Formula = "=0-U" & RowNum
'Bkt/Mkt Ratio
mWS.Range("W" & RowNum).Formula = "=O" & RowNum & "/N" & RowNum
'Accr/Mkt Ratio
mWS.Range("Y" & RowNum).Formula = "=U" & RowNum & "/" & "O" & RowNum
Else
mWS.Range("L" & RowNum).Value = 0
mWS.Range("V" & RowNum).Value = 0
'Bkt/Mkt Ratio
mWS.Range("W" & RowNum).Formula = "=O" & RowNum & "/N" & RowNum
'Accr/Mkt Ratio
mWS.Range("Y" & RowNum).Formula = "=U" & RowNum & "/" & "O" & RowNum
End If
End If
End If
'Bkt/Mkt Ratio
mWS.Range("W" & RowNum).Formula = "=O" & RowNum & "/N" & RowNum
'Accr/Mkt Ratio
mWS.Range("Y" & RowNum).Formula = "=U" & RowNum & "/" & "O" & RowNum
End If

[SPetrie]

Some of these wont really convert to a single multi-row and may require multiples. For example, anything that is setting the formula or value of multiple columns at a time. A multi-row will only act upon one column at a time.

Unfortunately I dont have time at the moment to really dive in and assist with converting these, but I can give you some breakdown of the VBA meanings. If you use these in conjunction with how I converted the previous formulas, you should be able to do it.

• If  it starts with a single quote, its a comment/note and not part of the code
• mWS.Range("A"&RowNum) is referring to Column A and the current row. In Alteryx this would be like [F1],mWS.Range("D"&RowNum) would be [F4] etc.
• anything with .Value after it is setting the value of that field. mWS.Range("V" & RowNum).Value = 0 in Alteryx would be like your multi-row or formula setting [F22] to be 0
• anything with .Formula is setting the excel formula for the indicated column but Alteryx would treat it the same was as .Value. This would usually be the column your Alteryx formula or multi-row field is acting upon.
• anything with RowNum-1 or +1 etc would require a multi-row since it is referencing the row above or below the current row it is working on. [Row-1:F1] or [Row+1:F1] for example.

[Arjunkp]

Thank you. This was tried, however some parts of the code were unable to fully crack.