Find and change

hi

im trying to write a small app that opens a excel sheets

finds all the "." iin a particular colomn and replaces it with a "_"

eg in colomn 2 i have numbers like

26.5.3.2
26.5.3.3
26.5.3.4

i want :

26_5_3_2
26_5_3_3
26_5_3_4

now i know this is fairy simply and i thing i have done most of it but the problem is i have "." in other colomn and i THINK this is causing problems

here the code

Code:

---

Private Sub Find_and_Change()
Dim XlApp As Excel.Application
Set XlApp = New Excel.Application
Dim Temp
Dim Rownumber As Integer
Dim Selected_Row As Integer
Dim Selected_Col As Integer
Dim X As Integer
Dim File_Location As String
Dim Original_string As String
Dim original_array
Dim Converted_String As String
 
    'On Error GoTo ErrHndle
    On Error Resume Next
   
    Call Open_File(File_Location)      'let user chosses the xls file
   
    If File_Location = "" Then
        MsgBox "Please select a MS Excel file"
        Exit Sub
    End If

    With XlApp
        .Workbooks.Open (File_Location)      'open path
        .Visible = True
    End With
   
   
    Rownumber = 100
   
    For X = 1 To txtNumber_of_Rows              'number of rows in XL sheet

        'XlApp.Range("b5", "b409").Activate
       

        Temp = XlApp.Cells.Find(What:=".", lookin:=xlValues, LookAt:=xlPart, SearchOrder:=xlColumn, SearchDirection:=xlPrevious, MatchCase:=True) '.Activate

       
        'find the selected row
        Selected_Row = XlApp.ActiveCell.Row
       
        'find the colomn
        Selected_Col = XlApp.ActiveCell.Column
       
        'If Selected_Col <> 2 Then
        '    GoTo line1
        'End If
       
        'capture the string
        Original_string = XlApp.Cells(Selected_Row, Selected_Col).Value
       
        'remove the "."
        original_array = Split(Original_string, ".")
       
        'add the "_" between numbers
        Converted_String = Join(original_array, "_")
       
        'put it back into table
        XlApp.Cells(Selected_Row, Selected_Col).Value = Converted_String        'goes into coloum 17

'line1:
        Rownumber = Rownumber + 1


    Next X
   
    X = 0          'clear value of x
    Temp = ""
   
'error handle
ErrHndle:
    If Err.Number = 91 Then MsgBox " Could not find WORD"
    'GoTo line1
   

End Sub

---

i also think i need to select a range at the top but i dont know how to do this

:(

Your doing this in an Over Compicated way, try this instead

also NEVER USE GOTO EXPECT IN ON ERROR STATEMENT

Code:

---

Private Sub Find_and_Change()
Dim XlApp As Excel.Application
Set XlApp = New Excel.Application
Dim Temp
Dim Rownumber As Integer
Dim Selected_Row As Integer
Dim Selected_Col As Integer
Dim X As Integer
Dim File_Location As String
Dim Original_string As String
Dim original_array
Dim Converted_String As String
[red]Dim oWkb as Workbook[/red]
    'On Error GoTo ErrHndle
    On Error Resume Next
   
    Call Open_File(File_Location)      'let user chosses the xls file
   
    If File_Location = "" Then
        MsgBox "Please select a MS Excel file"
        Exit Sub
    End If

    With XlApp
        .Workbooks.Open (File_Location)      'open path
        .Visible = True
    End With
   
   
    Rownumber = 100
   
    For X = 1 To txtNumber_of_Rows
[red]        'if you wish to do everycolumn then:
        for Y = 1 to TxtNumber_of_column
        'otherwise
        'Y = txtColumnNumber[/red]
       
        'capture the string
        Original_string = XlApp.Cells(x, y).Value
       
        'remove the "."
        original_array = Split(Original_string, ".")
       
        'add the "_" between numbers
        Converted_String = Join(original_array, "_")
       
        'put it back into table
        XlApp.Cells(x,y).Value = Converted_String       

 
      [red]next y[/red]
  next x 
   
[red] for each owkb in xlApp.workbooks
    owkb.close false
next owkb
[/red]   
   
End Sub

---