Find and change

[Sanj]

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

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

[Nirces]

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

also NEVER USE GOTO EXPECT IN ON ERROR STATEMENT

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