PDA

View Full Version : "Unable to get the Find property of the range class" Error



matrix20xx
09-28-2007, 12:45 AM
Hi, current I am writing a VBA to import a file (which may exceed 65536 rows of data) into excel. Then I want it to search through all the worksheets in the workbook for a word, and get the neccessary data from all the worksheets. Paste it in another worksheets.

Currently, I am able to import the file and split it into 2 or more sheets. But I cannot search all the worksheets for the specific word :banghead: . It return an error msg as mention in the title (the error is the highlighted in RED :help ).

THANKS in advance.



Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double

Dim ws As Worksheet

'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")

'Check for no entry
If FileName = "" Then End

'Get Next Available File Handle Number
FileNum = FreeFile()

'Open Text File For Input
Open FileName For Input As #FileNum

'Turn Screen Updating Off
Application.ScreenUpdating = False

'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet

'Set The Counter to 1
Counter = 1

'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)

'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName

'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr

'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then

ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then

'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else

'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If

'Increment the Counter By 1
Counter = Counter + 1

'Start Again At Top Of 'Do While' Statement
Loop

'Remove Message From Status Bar
Application.StatusBar = False

I = 0

For Each ws In Worksheets

m = m + WorksheetFunction.CountIf(ws.Cells, "*PRES CHAMBER*")

Next

For Each ws In Worksheets

For n = 1 To m

ws.Cells.Find(What:="PRES CHAMBER", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWithinWorkbook, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate

'row number
r = ActiveCell.Row

'column number
c = ActiveCell.Column

'time of the when the process started
tm = Left(Split(Cells(r - 1, c), " ")(1), 8)

I = I + 1

'copy the required raw data from the excel
PCA = Split(Cells(r + 0, 1), "'")(2)
TWX = Split(Cells(r + 1, 1), "'")(2)
TWY = Split(Cells(r + 2, 1), "'")(2)
TR = Split(Cells(r + 3, 1), "'")(2)
ALCPX = Split(Cells(r + 4, 1), "'")(2)
ALCPY = Split(Cells(r + 5, 1), "'")(2)
ALCPR = Split(Cells(r + 6, 1), "'")(2)
ACurrent = Split(Cells(r + 7, 1), "'")(2)
ATarget = Split(Cells(r + 8, 1), "'")(2)
BCurrent = Split(Cells(r + 9, 1), "'")(2)
BTarget = Split(Cells(r + 10, 1), "'")(2)
Offset = Split(Cells(r + 11, 1), "'")(2)
Humidity = Split(Cells(r + 12, 1), "'")(2)

'Front
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.FontStyle = "Bold"
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").HorizontalAlignment = xlCenter
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").VerticalAlignment = xlBottom
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.Name = "Arial"
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.Size = 12

'Lengend
Workbooks("Book1.xls").ActiveSheet.Cells(5, 1) = "DATE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 2) = "TIME"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 3) = "PRES CHAMBER AIR"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 4) = "TEMP WAFER X"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 5) = "TEMP WAFER Y"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 6) = "TEMP RETICLE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 7) = "ALCP WAFER X"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 8) = "ALCP WAFER Y"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 9) = "ALCP RETICLE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 10) = "PRES A CURRENT"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 11) = "PRES A TARGET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 12) = "PRES B CURRENT"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 13) = "PRES B TARGET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 14) = "LC FCS OFFSET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 15) = "HUMIDITY"

'Format row
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 1) = Format(Date, "long date")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 2) = Format(Time, "long time")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 3) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 4) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 5) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 6) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 7) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 8) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 9) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 10) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 11) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 12) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 13) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 14) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 15) = Format(dblNumber, "6")

'paste it into the workbooks
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 1) = dt
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 2) = tm
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 3) = PCA
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 4) = TWX
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 5) = TWY
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 6) = TR
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 7) = ALCPX
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 8) = ALCPY
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 9) = ALCPR
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 10) = ACurrent
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 11) = ATarget
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 12) = BCurrent
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 13) = BTarget
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 14) = Offset
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 15) = Humidity

Next

'create new sheet
Workbooks("Book1.xls").Sheets.Add Before:=ActiveSheet

Next

End Sub

rory
09-28-2007, 02:06 AM
Activecell belongs to the active sheet. You cannot therefore loop through all the sheets and tell each one to search all its cells starting after a cell that isn't on that sheet. Try leaving the After:= argument blank, or specify a range address on that sheet.

YellowLabPro
09-30-2007, 04:27 AM
Rory,
Would also have any bearing to change the Lookin paramter to xlValues?

rory
09-30-2007, 05:18 AM
It depends on what you want to search. If it's just text values, then no. As a general rule, I would also avoid trying to use the found range in the same line as the Find (i.e. avoid the Find(...).Activate syntax). If there is nothing found you will get a runtime error when the activate is tried. Always assign the results of Find to a range variable, then check if it is Nothing to see if the Find succeeded.

matrix20xx
10-01-2007, 01:14 AM
HI, i manage to search the word but it doesn't continue to search for the rest of the dulipcates through all the worksheets.

The program stop after finding the 1st duplicate.

how can I rectify this problem?