adding comma to string value in Range

[ilcaa]

i have 6000 rows in my range, i want to add a comma to the 7 character location from end.

i have this formula that i tested in a single cell and it works BUT when I try to apply it to the entire range using a With Range, the syntax is off (get mismatch error) can anyone assist with proper syntax

the formula is. Take the left most characters - last 6, then add a comma, then add back the last 6 characters.

set Rng = range("a1:a6000"

With Rng
        .Value = Left(.Value, Len(Rng) - 6) & "," & Right(Rng, 6)
End With

 

[Fluff]

You cannot change multiple values like that, try

Sub ilcaa()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(len(@)<6, @,replace(@,len(@)-5,0,"",""))", "@", .Address))
   End With
End Sub

 

[ilcaa]

You cannot change multiple values like that, try

Sub ilcaa()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(len(@)<6, @,replace(@,len(@)-5,0,"",""))", "@", .Address))
   End With
End Sub

hi fluff

i need to change the hard coded value of the 6th space from end to a variable.

i am struggling to write the proper syntax with all those quotes, etc. info on EVALUATE is very limited with only basic examples on www.

how can i replace the hard-coded len(@)-5 with a variable name?

thanks alot. hope i made sense

 

[Fluff]

How about

Sub ilcaa()
   Dim Lngth As Long
   Lngth = 6
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(len(@)< " & Lngth & ", @,replace(@,len(@)-" & Lngth - 1 & ",0,"",""))", "@", .Address))
   End With
End Sub