Find method and paste value problem

[qiong]

Dear friends,

Good evening,

I have some problems with finding keyword ":\" for each cell. My objective is to find that keyword in each cell in each worksheet which represent the link. Eg: =+'H:\QC\2007\Q uarter 1\EC\[ALM]Risk Measures'!F14 and paste value it.

I'm using Ms. Excel 2000.

Procedures:
1) Choose the Source_Director y and Source_Name
2) Search each cell with keyword ":\" for the formula in each cell for range A until IV (the max range in excel)
3) ":\" is keyword to search for the link in cell.
4) Copy all the selected found ":\" in every cell and paste value it.
5) Continue the above steps for each worksheets in the workbooks.
6) When finish, save the file as Output_Name without overwrite the Source_Name

'Paste value all the link in each cell in each worksheets in selected workbook
Option Explicit

Private Sub CommandButton1_Click()

'Dim No_FileName As Range

    'Define variables
    '------------------------
    Dim No_FileName, i As Integer
    Dim Source_Dir, Source_Name, Source, Output_Dir, Output_Name, Output As String
    
    No_FileName = ThisWorkbook.Worksheets("Input").Range("C2").Value

    For i = 1 To No_FileName
    Source_Dir = ThisWorkbook.Worksheets("Input").Range("C3").Value
    Source_Name = ThisWorkbook.Worksheets("Input").Range("C6").Offset(i - 1, 0).Value + ".xls"
    Source = Source_Dir + Source_Name
    Output_Dir = ThisWorkbook.Worksheets("Input").Range("H3").Value
    Output_Name = ThisWorkbook.Worksheets("Input").Range("H6").Offset(i - 1, 0).Value + ".xls"
    Output = Output_Dir + Output_Name
    
    'Open and Activate The Source File
    '---------------------------------
    Workbooks.Open Source, UpdateLinks:=0
    Windows(Source_Name).Activate
    
    Dim ws As Worksheet
    Dim cel As Variant
    Dim c As Variant
    Dim Worksheet As String
    Dim Cell As Variant
    
    For Each ws In Worksheets
    Worksheet = ws.Name
    ws.Select
    
         For Each cel In ActiveSheet.Range("A1:IV65536")
         Set c = ActiveCell.Find(What:=":\", After:=ActiveCell, LookIn:=xlFormula, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
          If c = True Then
                ActiveCell.Select
                Selection.Copy
                Application.CutCopyMode = False
                Windows(Output_Name).Activate
                ActiveCell.Select
                Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
                                
    
                
          End If
                
         Next cel
                
  Next ws
  
Workbooks(Source_Name).Close SaveChanges:=True


Workbooks(Output_Name).Save
Workbooks(Output_Name).Close SaveChanges:=True


Next i

End Sub

The errors occur with the coding:

Set c = ActiveCell.Find (What:=":\", After:=ActiveCe ll, LookIn:=xlFormu la, LookAt:=xlPart, SearchOrder:=xl ByRows, SearchDirection :=xlNext, MatchCase:=True )

whereas the subscript out of range.

Can someone help me with this problem? How should i write if i want to paste value if it found that keyword in each cell from A1 until IV65 536? Is the range is too big? Or any suggestions for better method?

Thank you very much in advance! I am really appreciated!

[MMcCarthy]

You have posted this in the Articles section.

I am moving it to the Visual Basic forum.

ADMIN