VBA Question - Formatting External System Data Resulting In Circular References

[DR RENO]

New to VBA and trying to embed code in control button, but instead of getting data in correct format so that I can apply lookup formulas, it appears it's just generating circular references with no changes to my source data. HELP

Here is the code I'm using:

Private Sub CommandButton1_Click()
Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
For Each Cell In Selection
S = Replace(Cell.Value, Chr(160), " ")
For X = LBound(CodesToClean) To UBound(CodesToClean)
If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
Next
Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
Next
End Sub

 

[6StringJazzer]

This code does not insert any formulas so I see no reason it would create circular references. This code works on Selection. What are you selecting when you click the button? What circular references are being created? That is, what formula in what cell refers to what other formula in what other cell, and so on, until it comes back to the first cell?

What does the data in the selected cells look like?

(Adding CODE tags would make your code more readable)

 

[DR RENO]

Thank you for responding me and pointing me in the right direction. I didn't realize that I would need to select the data prior to running the macro. I thought it had to would automatically apply to all cell data found inside the sheet. The circular reference was found where I had copied the =TRIM(CLEAN(SUBSTITUTE(D2371,CHAR(160)," "))) down the entire column, although no data resided in the rows. So thanks to you, I now have it working as it was intended. Is there a way to have the macro automatically select the data?

A couple of additional questions if you would kindly answer.

1. We have a column dedicated to account code which has leading zeros (eg. 000038, 00007, etc...). When the macro runs, it removes the leading zeros resulting in account codes such as 38, 7. This causes me a lookup issue. Is there some modification to the code that could resolve this problem? The system data comes across as text with asterisk before the 0s ('00038).

2. Your last line has me wanting to better understand usage of CODE tags so that people such as yourself and others won't have frustration reading my code examples. The CODE tabs you recommend are found on the site when creating a post correct? I can research information on here or Google to find out how to apply them, but if you could give a quick blurb that would be of help. By the way, it would not be upsetting if you choose to just direct me to look it up.

Again, thank you for the help. It's much appreciated.

 

[Michael M]

This line suggest you ARE using a Selection

For Each Cell In Selection

So, you need to decide what range / area/ cells you want the code to apply to ?

 

[DR RENO]

Yes, that is what I'm trying to do but struggling. I have a named range 'Format' which represents "A2:M6000" that I would like the macro to format data. My other issue is formatting a column that contains text cells with '0004 so that it doesn't give me a value of 4. New to VBA

 

[DR RENO]

Trying to select range for macro but getting Object Error

Private Sub CommandButton1_Click()  
  Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)


Range("A2:M6000").Cells.Value.Select
For Each Cell In Selection
    S = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
  Next
  
 
End Sub

 

[6StringJazzer]

Don't use Selection at all.

Private Sub CommandButton1_Click()  
  Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)


For Each Cell In [COLOR=#ff0000]Range("A2:M6000")[/COLOR]
    S = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
  Next
  
 
End Sub

 

[DR RENO]

That works. Is there a way to modify or enhance code that allows it to retain the columns with text string value containing leading zeroes prefixed by an asterisk? As I mentioned earlier it results in 27 instead of 027. I have several system extracts, most of which are in unicode character 48 where the text string is not impacted, but for the reports with unicode value 9, I get what's described above.

I know that I've veered a little from my original post, so perhaps this needs to be its own issue. Again, I'm newbie so let me know if anyone feels that is the case.

 

[DR RENO]

This by the way is what my code currently looks like, if that helps anyone. Also, if anyone sees ways to make the run-time reduced, I would appreciate the tip(s)

Private Sub CommandButton1_Click()

  Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)


For Each Cell In Range("A2:U6000")
    S = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
  Next
  
 
End Sub

 

[Michael M]

I note your range seems to be changing "A2:M6000" and now "A2:U6000"
How do you arrive at this specific range ??
You are actually searching through 126,000 cells with an array of 36 different criteria...Have a think of how you might be able to reduce the range AND the array if possible.
It may also help to post some sample data OR the worksheet to dropbox, so we might be able to look at other possible options.