$ amount does not calculate with a space

[FXA]

My issue is when I copy and paste from my source, that source has no comma in the dollar amount when the dollar amount hits the thousands and above. Is there a way around this?

Formula:

=C5+AK8+AK9+AK10

C5 = Starting Balance

AK8 =SUM(N10:N1809)

AK9 =SUM(L10:L1809)

AK10 =SUM(M10:M1809)


Thank you in advance.

 

[MARK858]

You're welcome

 

[FXA]

Just put the code in in the worksheet module (I've reduced it to just the bits you need [especially removed the FormulaVersion:=xlReplaceFormula2 as it won't run if your user has an older version of Excel])

Private Sub Worksheet_Activate()
Columns("N:N").Replace What:=" ", Replacement:="", LookAt:=xlPart
End Sub

Quick question:

What if I need to run that across multiple Columns?

 

[MARK858]

If it is just a few columns just repeat the code just changing the column letters or post the columns that you want

 

[FXA]

If it is just a few columns just repeat the code just changing the column letters or post the columns that you want

Columns L & M

 

[MARK858]

Range("L:M").Replace What:=" ", Replacement:="", LookAt:=xlPart

 

[FXA]

Range("L:M").Replace What:=" ", Replacement:="", LookAt:=xlPart

Thank you!

 

[MARK858]

Obviously if you still require it to work on column N as well just change the M in the last post to N so the one line covers all the columns

 

[FXA]

Thank you!

Is there a way to have this run as soon as data is copied in or does that consume resources and slow the worksheet down?