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]

However, the location of my cells are different than what Gerald set up. My amounts are in column F, and the formula rows are in columns M,N,O, and P - versus A,B,C,D,E like Gerald has. Also, my data starts at row 15, not 1.

This is the first time you have told us this. Without this information, the logical thing for us to do is assume Columns A, B, etc. and either Row 1 or, if you assume headers, Row 2 for the staring row. The problem is when you tell us this information late, we always have to do double work to change everything from what we assumed to what you finally are telling us. For future questions you may ask, please tell us where things are located in your original message.

Can you help adjust the macro for these criteria??

What column are the labels that you named "Example 1", "Example 2", etc. in?

What column do you want the red circled letters in?

 

[tonyjyoo]

This is the first time you have told us this. Without this information, the logical thing for us to do is assume Columns A, B, etc. and either Row 1 or, if you assume headers, Row 2 for the staring row. The problem is when you tell us this information late, we always have to do double work to change everything from what we assumed to what you finally are telling us. For future questions you may ask, please tell us where things are located in your original message.



What column are the labels that you named "Example 1", "Example 2", etc. in?

What column do you want the red circled letters in?

I sincerely apologize. Please forgive me.

- The columns the "examples" starts F15. It's label, on cell F14, is labeled "Amount".
- The formulas I copied from Gerald I have placed in cells M15, N15, O15, and P15. Column "P" has my letters A,B,C, etc based on the formulas.
- I would like the red circled letters in Column E, right before my example's in column F.

Again please forgive me for not being clear from the beginning.

 

[Rick Rothstein]

The columns the "examples" starts F15. It's label, on cell F14, is labeled "Amount".
- The formulas I copied from Gerald I have placed in cells M15, N15, O15, and P15. Column "P" has my letters A,B,C, etc based on the formulas.
- I would like the red circled letters in Column E, right before my example's in column F.

My code does not rely on Gerald's formula and if you eventually choose to use my code, you can delete his formulas.

Column F contains numbers, correct? That is not what I want to know. When you wrote "Example 1", I assumed that was text... a client name, an account number or something else textual (I am not sure what your first five columns hold) that repeat on each row for the numbers in Column F that apply to it... I need to know the Column that that repeating text is on. Why? Because I use the row when one of those texts stop equaling the text in the preceding row to determine when and where to place the circled red letters.

 

[tonyjyoo]

My code does not rely on Gerald's formula and if you eventually choose to use my code, you can delete his formulas.

Column F contains numbers, correct? That is not what I want to know. When you wrote "Example 1", I assumed that was text... a client name, an account number or something else textual (I am not sure what your first five columns hold) that repeat on each row for the numbers in Column F that apply to it... I need to know the Column that that repeating text is on. Why? Because I use the row when one of those texts stop equaling the text in the preceding row to determine when and where to place the circled red letters.

Column F contains the positive and negatives numbers, CORRECT.

The "Example 1,2,etc" are in Column E, which are descriptions. This is text. Descriptions vary down this column and are associated with the amounts that apply to it.

 

[Rick Rothstein]

The "Example 1,2,etc" are in Column E, which are descriptions. This is text. Descriptions vary down this column and are associated with the amounts that apply to it.

Okay, first, let's make sure we are talking about the same thing. The descriptions in Column E are identical for value in Column F belonging to it, correct? In other words, assuming F15 has 100 in it and F16 has -100 in it, I am expecting that both cell E15 and cell E16 contain exactly the same text description (no variations at all between them) and for the next set of related numbers in Column F that the corresponding cells in Column E all have the identical text in them (again, no variations at all between them) and that that text is different than what was in cells E15 and E16 and that this structure repeats throughout your data... is that correct?

If so, then here is my confusion.... these text descriptions are in Column E, but in Message #42, you said "I would like the red circled letters in Column E". These cannot both be in the same column, so can you clarify this for me please?

 

[Gerald Higgins]

Just to clarify, the thing about columns A to E was an assumption I made about how the source data and the helper columns I proposed, could be laid out, as per post #31.

 

[tonyjyoo]

Okay, first, let's make sure we are talking about the same thing. The descriptions in Column E are identical for value in Column F belonging to it, correct? In other words, assuming F15 has 100 in it and F16 has -100 in it, I am expecting that both cell E15 and cell E16 contain exactly the same text description (no variations at all between them) and for the next set of related numbers in Column F that the corresponding cells in Column E all have the identical text in them (again, no variations at all between them) and that that text is different than what was in cells E15 and E16 and that this structure repeats throughout your data... is that correct?

If so, then here is my confusion.... these text descriptions are in Column E, but in Message #42, you said "I would like the red circled letters in Column E". These cannot both be in the same column, so can you clarify this for me please?

Yes, you are correct that the corresponding cells in F have identical text in them in Column E.

When I stated I wanted the red circled letters in Column E, I was wondering if I could put the circled letters in there, as in like next to the text descriptions. Do they need to be in an entirely separate column?

 

[Rick Rothstein]

When I stated I wanted the red circled letters in Column E, I was wondering if I could put the circled letters in there, as in like next to the text descriptions. Do they need to be in an entirely separate column?

I think they would look better in their own column as they would stand out more that way, but I think (not positive yet because of the possible font mix) it can be done. Where in the cell did you want them... at the beginning or the end of the text? If at the end, how many lines do your descriptions take up? Note that if the description is on, say, two lines and the second line is short, if you put the red letters at the end, they may end up in the middle of the cell.

 

[tonyjyoo]

I think they would look better in their own column as they would stand out more that way, but I think (not positive yet because of the possible font mix) it can be done. Where in the cell did you want them... at the beginning or the end of the text? If at the end, how many lines do your descriptions take up? Note that if the description is on, say, two lines and the second line is short, if you put the red letters at the end, they may end up in the middle of the cell.

Unfortunately for this specific purpose, I think I would need them in the same column, preferably at the end of the text. If spacing is an issue, I can add the letters to the column with the amounts also, if that makes sense.

 

[Rick Rothstein]

Rick - I was testing your macro and this is EXACTLY what I need. However, the location of my cells are different than what Gerald set up. My amounts are in column F, and the formula rows are in columns M,N,O, and P - versus A,B,C,D,E like Gerald has. Also, my data starts at row 15, not 1.

Can you help adjust the macro for these criteria??

See if this does what you want...

[table="width: 500"]
[tr]
	[td]Sub CircledLetters()
  Dim R As Long, X As Long, StartRow As Long, LastRow As Long, EText As Variant
  On Error GoTo SomethingBadHappened
  StartRow = 15
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  EText = Range(Cells(StartRow - 1, "E"), Cells(LastRow, "E"))
  X = -1
  Application.ScreenUpdating = False
  For R = 2 To UBound(EText)
    If EText(R, 1) <> EText(R - 1, 1) Then
      X = X + 1
      With Cells(R + StartRow - 2, "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
  Next
SomethingBadHappened:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]