Automatically letter debits/credits

[tonyjyoo]

Hello,

Random question. Is there any way to automatically put in letters for each pair of debit and credits I have in journal entries within excel?

For example, I would need A, B, C, D, etc. in a red circle.

Thanks,
Tony

 

[tonyjyoo]

I am not sure what you mean by that, especially given I tested the code where every cell in Column E had different text in it. Can you show and example of the section of your data (both Column E and F values) plus a few rows before and after the grouping so I can see what you are dealing with?

Basically, the macro lettered everything correctly (A,B,C,D,etc...) up until U, which has differing descriptions.

This is what group "U" reads in my data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Comm Ck Deposit[/TD]
[TD]109,016.85

[/TD]
[/TR]
[TR]
[TD]ES Comm Deposit[/TD]
[TD]-108,093.43
[/TD]
[/TR]
[TR]
[TD]Clearing Firm Ck 6249[/TD]
[TD]-923.42[/TD]
[/TR]
</tbody>[/TABLE]

so the macro did in fact letter this group with a "U", but the rest of my data after does not show any lettering. It seems the code stopped here at this point.

Does that make sense?

 

[Rick Rothstein]

Basically, the macro lettered everything correctly (A,B,C,D,etc...) up until U, which has differing descriptions.

This is what group "U" reads in my data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Comm Ck Deposit[/TD]
[TD]109,016.85

[/TD]
[/TR]
[TR]
[TD]ES Comm Deposit[/TD]
[TD]-108,093.43
[/TD]
[/TR]
[TR]
[TD]Clearing Firm Ck 6249[/TD]
[TD]-923.42[/TD]
[/TR]
</tbody>[/TABLE]

so the macro did in fact letter this group with a "U", but the rest of my data after does not show any lettering. It seems the code stopped here at this point.

Does that make sense?

Are the cells in Column F formatted to two decimal places? If so, the problem more than likely is your values are calculated values and you are looking at the rounded values whereas the real underlying values after what you showed us never actually total to zero again because the real values in the cells have more than the two decimal places that are showing. I think this may fix the problem, but I am not 100% sure of that (let me know)...

[table="width: 500"]
[tr]
	[td]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, Total As Long, EText As Variant, FNumbers As Variant
  On Error GoTo SomethingBadMustHaveHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow, "E"), Cells(LastRow, "E"))
  FNumbers = Range(Cells(StartRow, "F"), Cells(LastRow, "F"))
  X = -1
  Application.ScreenUpdating = False
  For R = 1 To UBound(FNumbers) - 1
    If Total = 0 Then
      X = X + 1
      Total = 0
      With Cells(R + StartRow - 1, "E")
        .Value = .Value & Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Name = "Arial Unicode MS"
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Color = vbRed
      End With
    End If
    Total = Total + Round(FNumbers(R, 1), 2)
  Next
SomethingBadMustHaveHappened:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[tonyjyoo]

Are the cells in Column F formatted to two decimal places? If so, the problem more than likely is your values are calculated values and you are looking at the rounded values whereas the real underlying values after what you showed us never actually total to zero again because the real values in the cells have more than the two decimal places that are showing. I think this may fix the problem, but I am not 100% sure of that (let me know)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, Total As Long, EText As Variant, FNumbers As Variant
  On Error GoTo SomethingBadMustHaveHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow, "E"), Cells(LastRow, "E"))
  FNumbers = Range(Cells(StartRow, "F"), Cells(LastRow, "F"))
  X = -1
  Application.ScreenUpdating = False
  For R = 1 To UBound(FNumbers) - 1
    If Total = 0 Then
      X = X + 1
      Total = 0
      With Cells(R + StartRow - 1, "E")
        .Value = .Value & Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Name = "Arial Unicode MS"
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Color = vbRed
      End With
    End If
    Total = Total + Round(FNumbers(R, 1), 2)
  Next
SomethingBadMustHaveHappened:
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hm... same problem. I tested it on several examples and it does not complete the lettering for all the groups..

EDIT: The cells in column F will always show 2 decimal points. For example, If I write 100, it will show as 100.00

 

[tonyjyoo]

Hm... same problem. I tested it on several examples and it does not complete the lettering for all the groups..

EDIT: The cells in column F will always show 2 decimal points. For example, If I write 100, it will show as 100.00

OK - This is strange. It worked fine for one that had more groups. So I don't understand why sometimes it doesn't complete the lettering for others.

 

[Rick Rothstein]

OK - This is strange. It worked fine for one that had more groups. So I don't understand why sometimes it doesn't complete the lettering for others.

Can you send me a copy of your workbook (one where my code does not work correctly for)... make sure it is a workbook with data BEFORE my code has been run on it so I can actualy watch what my code does with it live? My email address is...

rick DOT news AT verizon DOT net

 

[tonyjyoo]

Can you send me a copy of your workbook (one where my code does not work correctly for)... make sure it is a workbook with data BEFORE my code has been run on it so I can actualy watch what my code does with it live? My email address is...

rick DOT news AT verizon DOT net

Hi Rick,

Just sent you an email.

 

[Rick Rothstein]

Hi Rick,

Just sent you an email.

I got it and I saw the problem you were talking about... and I cannot figure out why it is happening, so I changed my code's method to avoid it. This code will work but if there is something ultimately wrong in your data, this does not solve it... it just a works around it.

[table="width: 500"]
[tr]
	[td]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, Total As Double
  Dim Status As String, EText As Variant, FNumbers As Variant
  On Error GoTo SomethingBadMustHaveHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow, "E"), Cells(LastRow, "E"))
  FNumbers = Range(Cells(StartRow, "F"), Cells(LastRow, "F")).Value
  X = -1
  Status = -1
  Application.ScreenUpdating = False
  For R = 1 To UBound(FNumbers) - 1
    If FNumbers(R, 1) >= 0 And Status < 0 Then
      X = X + 1
      Total = 0
      With Cells(R + StartRow - 1, "E")
        .Value = .Value & Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Name = "Arial Unicode MS"
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Color = vbRed
        [B][COLOR="#FF0000"].Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Bold = True[/COLOR][/B]
      End With
    End If
    Status = Sgn(FNumbers(R, 1))
    Total = Total + Round(FNumbers(R, 1), 2)
  Next
SomethingBadMustHaveHappened:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

Note: The line of code I highlighted in red is the one that makes the red circled letters Bold. If you end up not liking them bold (for the reason I mentioned in one of my previous messages), simply delete it.

 

[tonyjyoo]

I got it and I saw the problem you were talking about... and I cannot figure out why it is happening, so I changed my code's method to avoid it. This code will work but if there is something ultimately wrong in your data, this does not solve it... it just a works around it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, Total As Double
  Dim Status As String, EText As Variant, FNumbers As Variant
  On Error GoTo SomethingBadMustHaveHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow, "E"), Cells(LastRow, "E"))
  FNumbers = Range(Cells(StartRow, "F"), Cells(LastRow, "F")).Value
  X = -1
  Status = -1
  Application.ScreenUpdating = False
  For R = 1 To UBound(FNumbers) - 1
    If FNumbers(R, 1) >= 0 And Status < 0 Then
      X = X + 1
      Total = 0
      With Cells(R + StartRow - 1, "E")
        .Value = .Value & Application.Rept(ChrW(9398 + (X Mod 26)), 1 + Int(X / 26))
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Name = "Arial Unicode MS"
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Color = vbRed
        [B][COLOR=#FF0000].Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Bold = True[/COLOR][/B]
      End With
    End If
    Status = Sgn(FNumbers(R, 1))
    Total = Total + Round(FNumbers(R, 1), 2)
  Next
SomethingBadMustHaveHappened:
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Note: The line of code I highlighted in red is the one that makes the red circled letters Bold. If you end up not liking them bold (for the reason I mentioned in one of my previous messages), simply delete it.

My God.

This is amazing.

Thank you so much for all your help on this matter! Seriously, I cannot stress how much help this is.

 

[Rick Rothstein]

My God.

This is amazing.

Thank you so much for all your help on this matter! Seriously, I cannot stress how much help this is.

You are quite welcome... I was happy to be able to help out and I am glad we finally got you a working solution.

 

[tonyjyoo]

You are quite welcome... I was happy to be able to help out and I am glad we finally got you a working solution.

Hey Rick,

I just noticed one small detail, I don't know if this will require a lot of work, but after 26 groups, it letters as AA, then BB, then CC etc...

I wanted it as AA, AB, AC etc.

Is this a quick fix? Otherwise, it's okay.