Capturing Excel settings?

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
326
I have an add-in that is distributed in our offices in the US. One user is having a problem where the code below only writes out part of what it should. Let's say A1 is cDiv. A2 is A.
This code (which I use to write to a text file) usually writes out "A", in the output file. Instead, for this one user, it is writing out A", - leaving off the first ("). It does this for all the columns touched by this code.

I am thinking it is some setting that he has on his computer. Does anyone know of a way to capture a user's Excel settings as a way of debugging a problem like this? I cannot duplicate the problem on my PC.

This is only part of the code, but it's the part that creates the string to be written out.
Code:
Any ideas would be appreciated...
Sub Data_C()
Dim i As Long
Dim r As Range
Dim find_cell As Range
Dim what As String
Dim FirstAddress As String
Dim lastcell As Range

Set r = Cells.Range("A1:IV1")

what = "c?"
With r
Set find_cell = .Find(what, LookAt:=xlPart)
  If Not find_cell Is Nothing Then
  FirstAddress = find_cell.Address
      Do
     If InStr(1, find_cell.Value, "c", vbTextCompare) = 1 Then
       iPrg = iPrg + 1
       sngpercent = iPrg / col_ctr
       ProgressStyle1 sngpercent, True
      If row_ctr = 2 Then
       Set lastcell = Cells(row_ctr, find_cell.Column)
       Set x = Range(find_cell.Offset(0, 0), lastcell)
      Else
       Set lastcell = Cells(row_ctr, find_cell.Column)
       Set x = Range(find_cell.Offset(1, 0), lastcell)
      End If
      format_c_field_fast
     End If
      Set find_cell = .FindNext(find_cell)
      Loop While Not find_cell Is Nothing And FirstAddress <> find_cell.Address
  End If
End With
End Sub
Sub format_c_field_fast()
Dim mem As Variant
Dim i As Long

On Error Resume Next
x.SpecialCells(xlCellTypeBlanks) = " "
On Error GoTo 0

mem = x
    For i = 1 To UBound(mem)
    mem(i, 1) = """" & mem(i, 1) & """" & ","
    Next i
x = mem
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi, KenCriss,
just guessing ...

1.
change """" to Chr(34)
mem(i, 1) = Chr(34) & mem(i, 1) & Chr(34) & ","
or
2. insert a quote
mem(i, 1) = "'" & Chr(34) & mem(i, 1) & Chr(34) & ","
or
let the user format his range as text
if this helps you can insert this in the code

kind regards,
Erik
 
Upvote 0
Thanks, Erik. I was thinking the same thing about chr(34). I am having the user try that to see if it works.
 
Upvote 0
I don't see the difference, but just putted that in to be "complete" ...
adding a quote before (which you can delete afterwards) or formatting the cells could have more chance to succeed to my sense
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top