By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Find method and paste value problem

P: 2
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\Quarter 1\EC\[ALM]Risk Measures'!F14 and paste value it.

I'm using Ms. Excel 2000.

Procedures:
1) Choose the Source_Directory 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


Expand|Select|Wrap|Line Numbers
  1.  
  2. 'Paste value all the link in each cell in each worksheets in selected workbook
  3. Option Explicit
  4.  
  5. Private Sub CommandButton1_Click()
  6.  
  7. 'Dim No_FileName As Range
  8.  
  9.     'Define variables
  10.     '------------------------
  11.     Dim No_FileName, i As Integer
  12.     Dim Source_Dir, Source_Name, Source, Output_Dir, Output_Name, Output As String
  13.  
  14.     No_FileName = ThisWorkbook.Worksheets("Input").Range("C2").Value
  15.  
  16.     For i = 1 To No_FileName
  17.     Source_Dir = ThisWorkbook.Worksheets("Input").Range("C3").Value
  18.     Source_Name = ThisWorkbook.Worksheets("Input").Range("C6").Offset(i - 1, 0).Value + ".xls"
  19.     Source = Source_Dir + Source_Name
  20.     Output_Dir = ThisWorkbook.Worksheets("Input").Range("H3").Value
  21.     Output_Name = ThisWorkbook.Worksheets("Input").Range("H6").Offset(i - 1, 0).Value + ".xls"
  22.     Output = Output_Dir + Output_Name
  23.  
  24.     'Open and Activate The Source File
  25.     '---------------------------------
  26.     Workbooks.Open Source, UpdateLinks:=0
  27.     Windows(Source_Name).Activate
  28.  
  29.     Dim ws As Worksheet
  30.     Dim cel As Variant
  31.     Dim c As Variant
  32.     Dim Worksheet As String
  33.     Dim Cell As Variant
  34.  
  35.     For Each ws In Worksheets
  36.     Worksheet = ws.Name
  37.     ws.Select
  38.  
  39.          For Each cel In ActiveSheet.Range("A1:IV65536")
  40.          Set c = ActiveCell.Find(What:=":\", After:=ActiveCell, LookIn:=xlFormula, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
  41.           If c = True Then
  42.                 ActiveCell.Select
  43.                 Selection.Copy
  44.                 Application.CutCopyMode = False
  45.                 Windows(Output_Name).Activate
  46.                 ActiveCell.Select
  47.                 Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
  48.                 False, Transpose:=False
  49.  
  50.  
  51.  
  52.           End If
  53.  
  54.          Next cel
  55.  
  56.   Next ws
  57.  
  58. Workbooks(Source_Name).Close SaveChanges:=True
  59.  
  60.  
  61. Workbooks(Output_Name).Save
  62. Workbooks(Output_Name).Close SaveChanges:=True
  63.  
  64.  
  65. Next i
  66.  
  67. End Sub
  68.  
  69.  
The errors occur with the coding:

Set c = ActiveCell.Find(What:=":\", After:=ActiveCell, LookIn:=xlFormula, LookAt:=xlPart, SearchOrder:=xlByRows, 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!
May 12 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
You have posted this in the Articles section.

I am moving it to the Visual Basic forum.

ADMIN
May 12 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.