none
Replacing characters and changing format of excel column to text format using vba RRS feed

  • Question

  • Hi All,

    In the script  below I am replacing characters and empty field with "0" in the colums ("F2:I5000"), and after that I am updating the format of column"I" to text format. The first part is working fine althought its still complaining that not all characters can be found, but with the second part changing format of column"I" to text format, it's asking for obeject required?

    Any suggestion what's missing in my script for the second part changing format of column"I" to text format?

    Private Sub Command2_Click()
    Dim wkb As Workbook
    Dim fndList As Variant
    Dim rplcList As Variant
    Dim x As Long

    fndList = Array("n/a", "na", "NA", "N/A", "NONE", "NAV", "999", "ALL", "T*", "t*", "test", "")
    rplcList = Array("0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0")
    Set wkb = Workbooks.Open("C:\Users\Peacock\Documents\All system report v5 today.xlsx")
    'Loop through each item in Array lists
      For x = LBound(fndList) To UBound(fndList)
        'Loop through each worksheet in ActiveWorkbook
        wkb.Sheets("Sheet1").Range("F2:I5000").Replace What:=fndList(x), Replacement:=rplcList(x), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
        Next x
        wb.Sheets("Sheet1").Range("I2:I5000").NumberFormat = "@"

      wkb.Close SaveChanges:=True
    End Sub


    Anri

    Friday, June 17, 2016 7:53 AM

All replies

  • Just a typo:

    wb.Sheets(...

    should be

    wkb.Sheets(...

    If you require variables to be declared, i.e. if you have a line

    Option Explicit

    at the top of the module, the Visual Basic Editor would warn you that wb isn't defined.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 17, 2016 8:35 AM
  • Hi Hans,

    Thanks I don't get the warnings anymore!! 

    I have checked the excel, and it didn't change the format first, but after change the range to the whole column it worked fine!  Below the working script.

    Last question : IS there a command for command "fndList" that will search for all characters that isn't a number?  To avoid that I need to add all kind of characters.

    Private Sub Command2_Click()
    Dim wkb As Workbook
    Dim fndList As Variant
    Dim rplcList As Variant
    Dim x As Long

    fndList = Array("n/a", "na", "NA", "N/A", "NONE", "NAV", "999", "ALL", "T*", "t*", "test", "")
    rplcList = Array("0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0")
    Set wkb = Workbooks.Open("C:\Users\Peacock\Documents\All system report v5 today.xlsx")
    'Loop through each item in Array lists
      For x = LBound(fndList) To UBound(fndList)
        'Loop through each worksheet in ActiveWorkbook
        wkb.Sheets("Sheet1").Range("F2:I5000").Replace What:=fndList(x), Replacement:=rplcList(x), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
        Next x
        wb.Sheets("Sheet1").Range("I2:I5000").NumberFormat = "@"

      wkb.Close SaveChanges:=True
    End Sub


    Anri

    Friday, June 17, 2016 9:35 AM
  • So you want to replace everything that is not a number with 0?

    In that case, why do you set the number format of the cells to text?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 17, 2016 10:19 AM
  • Hi Hans,

    Yes, otherwise I will be in a endless loop of ending new exceptions.

    Regards,

    Anri


    Anri

    Friday, June 17, 2016 11:07 AM
  • Try this version:

    Private Sub Command2_Click()
        Dim wkb As Workbook
        Dim rng As Range
        Dim rngT As Range
        Set wkb = Workbooks.Open("C:\Users\Peacock\Documents\All system report v5 today.xlsx")
        Set rng = wkb.Sheets("Sheet1").Range("F2:I5000")
        On Error Resume Next
        Set rngT = rng.SpecialCells(xlCellTypeConstants, xlTextValues)
        On Error GoTo 0
        If Not rngT Is Nothing Then
            rngT.Value = 0
        End If
        ' Do you really want to set the format to Text?
        rng.NumberFormat = "@"
        wkb.Close SaveChanges:=True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 17, 2016 11:57 AM
  • Hi Hans,

    I have tested it and it's removing deleting everything that is not a number, but not replacing it with "0"?  Iwill stick with my solution as soem number values has also "-" included in the values i.e.  "1-6"; "7-13"  etc

    Regards,

    Anri


    Anri

    Friday, June 17, 2016 12:44 PM
  • Do you show zero values (File > Options > Advanced > Display options for this worksheet > Show a zero in cells that have zero value)? If not, the zeros will be there but not displayed...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 17, 2016 1:20 PM
  • The setting is flagged on.


    Anri

    Friday, June 17, 2016 1:31 PM
  • I have checked the excel file on (File > Options > Advanced > Display options for this worksheet > Show a zero in cells that have zero value)? it hass the flag but still don't see the values

    Anri

    Friday, June 17, 2016 1:34 PM
  • Strange - when I tested the code, it replaced text values with zeroes.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 17, 2016 3:08 PM
  • Could it be related to the fact that I high lighted the fields that should be updated with a back ground colour?

    Anri

    Friday, June 17, 2016 5:36 PM
  • If the background color is the same as the text color, you wouldn't see the zeros...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 17, 2016 7:13 PM
  • I checked that already I thought exactly the same first :-)

    Anri

    Friday, June 17, 2016 7:51 PM
  • I'm stumped - sorry. I'd have to see (a copy of) the workbook to know what's going on.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 17, 2016 9:28 PM
  • Hi Anri2018,

    I run the code suggested by Hans Vogelaar.

    it gives me following output.

    is it your desired output?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 20, 2016 7:44 AM
    Moderator
  • Hi Hans,

    I hope this works? So numbers and values like 1-6, 7-9 etc are not replaced with a zerro

    https://www.dropbox.com/s/u99jqw7fmmvgvbw/Example%20column%20F-I.xls?dl=0

    The file has in real more columns but can't share the values.

    Regards,  Anri



    Anri

    Wednesday, June 22, 2016 8:55 AM
  • https://www.dropbox.com/l/scl/bkGrNssBCihAgIxirgYARo or try this link

    Anri

    Wednesday, June 22, 2016 8:56 AM
  • Hi Anri2018,

    is that solves your issue?

    did you apply solution from user interface or with VBA code?

    because I did not find code in your workbook?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 22, 2016 9:46 AM
    Moderator
  • Hi Deepak,

    I provided example data that needs to be checked and updated with the script of Hans, but forr some reason it's not working with my data.


    Anri

    Wednesday, June 22, 2016 11:23 AM
  • I downloaded your workbook, and added the macro. I had to modify it a bit, since you moved the columns, but when I ran it, the text values were all changed to zeros:

    So I'm still stumped...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 22, 2016 1:52 PM
  • I have tested it again by using the same test file No change -no updates?

    Below the code

    Private Sub Command3_Click()

    Dim wkb As Workbook

        Dim rng As Range

        Dim rngT As Range

        Set wkb = Workbooks.Open("C:\Users\John.peacock\Documents\Example column F-I.xls")

        Set rng = wkb.Sheets("Sheet1").Range("B2:E5000")

        On Error Resume Next

        Set rngT = rng.SpecialCells(xlCellTypeConstants, xlTextValues)

        On Error GoTo 0

        If Not rngT Is Nothing Then

            rngT.Value = 0

        End If

        ' Do you really want to set the format to Text?

        rng.NumberFormat = "@"

        wkb.Close SaveChanges:=True

    End Sub


    Anri

    Wednesday, June 22, 2016 2:46 PM
  • I really don't know why it doesn't work for you...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 22, 2016 2:51 PM
  • Could it be that the problem is in a missing reference setting in access?  I am talking about 

    1. Open the database.
    2. Press ALT+F11 to start Visual Basic Editor.
    3. On the Tools menu, click References.

    Anri

    Wednesday, June 22, 2016 3:30 PM
  • My apologies, I completely overlooked the fact that you posted this in the Access forum. I treated it as a pure Excel question.

    Here is a version that should run from Access.

    Private Sub Command2_Click()
        Dim app As Object
        Dim wkb As Object
        Dim rng As Object
        Dim rngT As Object
        Dim f As Boolean
        On Error Resume Next
        Set app = GetObject(Class:="Excel.Application")
        If app Is Nothing Then
            Set app = CreateObject("Excel.Application")
            If app Is Nothing Then
                MsgBox "Can't start Excel!", vbExclamation
                Exit Sub
            End If
            f = True
        End If
        On Error GoTo ErrHandler
        Set wkb = app.Workbooks.Open("C:\Users\Peacock\Documents\All system report v5 today.xlsx")
        Set rng = wkb.Sheets("Sheet1").Range("F2:I5000")
        On Error Resume Next
        Set rngT = rng.SpecialCells(2, 2)
        On Error GoTo ErrHandler
        If Not rngT Is Nothing Then
            rngT.Value = 0
        End If
        ' Do you really want to set the format to Text?
        rng.NumberFormat = "@"
    ExitHandler:
        On Error Resume Next
        wkb.Close SaveChanges:=True
        If f And Not app Is Nothing Then
            app.Quit
        End If
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 22, 2016 8:43 PM
  • Hi Hans,

    It's working but replacing everything including values that should stay in like number values with "-" included in the values i.e.  "1-6"; "7-13"  etc.

    Regards,

    Anri


    Anri

    Thursday, June 23, 2016 4:27 PM
  • I'm sorry, it has become too complicated.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 23, 2016 5:17 PM