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

 

[Rick Rothstein]

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.

Do you think you will ever need more than ZZ with that system (approximately 700 individual groupings)?

 

[tonyjyoo]

Do you think you will ever need more than ZZ with that system (approximately 700 individual groupings)?

No, at most it's been up to around 60 groups... such as "BD"..

 

[tonyjyoo]

No, at most it's been up to around 60 groups... such as "BD"..

Any luck on this?

 

[Rick Rothstein]

Any luck on this?

Thanks for reactivating this question... I had lost track of it in the volume of message in this forum. See if this macro works for you...

[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")
        If X < 26 Then
          .Value = .Value & ChrW(9398 + X)
        Else
          .Value = .Value & ChrW(9398 + Int((X - 26) / 26)) & ChrW(9398 + (X Mod 26))
        End If
        .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
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Bold = True
      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]

 

[phoenixdown]

I didn't read through the last few pages but your Accounts Receivable / Accounts Payable system should detail out the Invoice #, Invoice Date and Posting Dates. Those would be keys to helping you group the remaining items together.

 

[Rick Rothstein]

I didn't read through the last few pages

Why not (there were only 74 messages when you posted to this thread)?

...but your Accounts Receivable / Accounts Payable system should detail out the Invoice #, Invoice Date and Posting Dates. Those would be keys to helping you group the remaining items together.

All kidding aside, his question did not detail with creating the groups themselves... his data is already grouped with the plus values listed first followed by the negative values for each group... ultimately what he wanted to do was simply place circled red letters within the description next to the first plus value per group. The location for this red circled letters is easily identified given the way his data is grouped (it is the row where a plus value immediately follows a negative value). The OP acknowledged that my attempt in Message #68 placed the red circled letters at the correct location, but that I had interpreted the sequencing of those letter after the letter Z was incorrect... the code I posted in Message #74 gives him the correct sequencing that he wanted. This thread should become resolved once the OP sees Message #74.

 

[tonyjyoo]

Thanks for reactivating this question... I had lost track of it in the volume of message in this forum. See if this macro works for you...

[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")
        If X < 26 Then
          .Value = .Value & ChrW(9398 + X)
        Else
          .Value = .Value & ChrW(9398 + Int((X - 26) / 26)) & ChrW(9398 + (X Mod 26))
        End If
        .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
        .Characters(Len(EText(R, 1)) + 1, 1 + Int(X / 26)).Font.Bold = True
      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]

This works beautifully. Thank you again for all your help!!!!

 

[tonyjyoo]

This works beautifully. Thank you again for all your help!!!!

Hello again...

So I manipulated the code and decided to place the letters on their own in a new column (again, thank you, as the code works perfectly).

However, is it possible to take this one step further and merge the cells each grouped letter has? For example, if group A has 1 positive and 1 negative, it would have 2 cells, so merge those 2 to center the letter; if group B has 3 cells, to merge the 3 cells and center, etc.

Thank you,
Tony