Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace: Object Required
Here' my code:
Dim varSht As Variant Dim FoundCell As Range ' Iterates through sheets, finds data, & replaces data For Each varSht In Workbooks(sFilename).Sheets ' Use Excel's Internal Find & Replace Method Set FoundCell = varSht.Cells.Replace(What:=sFindWhat, _ Replacement:=sReplaceWith, _ LookAt:=vLookAt, MatchCase:=bMatchCase) If Not FoundCell Is Nothing Then fncStdFind = True Exit Function End If Next When I run this, the 'Set FoundCell = varSht.Cells.Replace' line is highlighted, and the error 'Object required.' is displayed. If I change 'Dim varSht As Variant' to 'Dim varSht As WorkSheet', then it gives me a 'Type mismatch' error with the same line highlighted. What the heck? -- Trent Argante [DC.J(455)] |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace: Object Required
What are vLookAt and bMatchCase at runtime. As a guess that is where your
problem lies. vLookAt Should be text such as xlWhole or xlPart. bMatchCase will be boolean. -- HTH... Jim Thomlinson "Trent Argante" wrote: Here' my code: Dim varSht As Variant Dim FoundCell As Range ' Iterates through sheets, finds data, & replaces data For Each varSht In Workbooks(sFilename).Sheets ' Use Excel's Internal Find & Replace Method Set FoundCell = varSht.Cells.Replace(What:=sFindWhat, _ Replacement:=sReplaceWith, _ LookAt:=vLookAt, MatchCase:=bMatchCase) If Not FoundCell Is Nothing Then fncStdFind = True Exit Function End If Next When I run this, the 'Set FoundCell = varSht.Cells.Replace' line is highlighted, and the error 'Object required.' is displayed. If I change 'Dim varSht As Variant' to 'Dim varSht As WorkSheet', then it gives me a 'Type mismatch' error with the same line highlighted. What the heck? -- Trent Argante [DC.J(455)] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace: Object Required
Hi Jim,
vLookAt & bMatchCase are user defined variables passed into the procedure. I replaced them with xlPart & False and still got the same error. The interesting thing is that using Find method in the same manner does *not* return this 'Object required' error, but runs fine. Any othe suggestions? Thanks for your help. Trent Argante [DC.J(455)] "Jim Thomlinson" wrote: What are vLookAt and bMatchCase at runtime. As a guess that is where your problem lies. vLookAt Should be text such as xlWhole or xlPart. bMatchCase will be boolean. -- HTH... Jim Thomlinson "Trent Argante" wrote: Here' my code: Dim varSht As Variant Dim FoundCell As Range ' Iterates through sheets, finds data, & replaces data For Each varSht In Workbooks(sFilename).Sheets ' Use Excel's Internal Find & Replace Method Set FoundCell = varSht.Cells.Replace(What:=sFindWhat, _ Replacement:=sReplaceWith, _ LookAt:=vLookAt, MatchCase:=bMatchCase) If Not FoundCell Is Nothing Then fncStdFind = True Exit Function End If Next When I run this, the 'Set FoundCell = varSht.Cells.Replace' line is highlighted, and the error 'Object required.' is displayed. If I change 'Dim varSht As Variant' to 'Dim varSht As WorkSheet', then it gives me a 'Type mismatch' error with the same line highlighted. What the heck? -- Trent Argante [DC.J(455)] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace: Object Required
Good Morning Trent,
I believe it is excel is causing your problem. I ran several test a got the same results as you. When read in VBA help the Replace method replaces all occureance in the selected range. In your case you have selected the complete sheet. So for ever matching cell found, excel would have return range object. This would be very impractical. Because excel be passing multiple range object. Thus probably causing the error. I've attached some work around code that will probably help you get your task done.... Enjoy, Rick, Fairbanks Alaska... Dim varSht As Worksheet Dim FoundCell As Range Dim FirstAdd As String ' Iterates through sheets, finds data, & replaces data For Each varSht In Worksheets ' Use Excel's Internal Find & Replace Method Set FoundCell = varSht.Cells.Find(What:=sFindWhat, _ LookAt:=xlPart, MatchCase:=True) If Not FoundCell Is Nothing Then fncStdFind = True FirstAdd = FoundCell.Address Do FoundCell.Value = sReplaceWith Set FoundCell = Cells.FindNext(FoundCell) Loop While (FoundCell.Address < FirstAdd) Exit Function Else fncStdFind = False End If Next "Trent Argante" wrote in message ... Hi Jim, vLookAt & bMatchCase are user defined variables passed into the procedure. I replaced them with xlPart & False and still got the same error. The interesting thing is that using Find method in the same manner does *not* return this 'Object required' error, but runs fine. Any othe suggestions? Thanks for your help. Trent Argante [DC.J(455)] "Jim Thomlinson" wrote: What are vLookAt and bMatchCase at runtime. As a guess that is where your problem lies. vLookAt Should be text such as xlWhole or xlPart. bMatchCase will be boolean. -- HTH... Jim Thomlinson "Trent Argante" wrote: Here' my code: Dim varSht As Variant Dim FoundCell As Range ' Iterates through sheets, finds data, & replaces data For Each varSht In Workbooks(sFilename).Sheets ' Use Excel's Internal Find & Replace Method Set FoundCell = varSht.Cells.Replace(What:=sFindWhat, _ Replacement:=sReplaceWith, _ LookAt:=vLookAt, MatchCase:=bMatchCase) If Not FoundCell Is Nothing Then fncStdFind = True Exit Function End If Next When I run this, the 'Set FoundCell = varSht.Cells.Replace' line is highlighted, and the error 'Object required.' is displayed. If I change 'Dim varSht As Variant' to 'Dim varSht As WorkSheet', then it gives me a 'Type mismatch' error with the same line highlighted. What the heck? -- Trent Argante [DC.J(455)] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace: Object Required
Thanks rick, that did the trick!
Incidentally, my best high school buddy is from Fairbanks, but that was almost Ahhhh 30 years ago!!! =;O) -- Trent Argante [DC.J(455)] "Rick Hansen" wrote: Good Morning Trent, I believe it is excel is causing your problem. I ran several test a got the same results as you. When read in VBA help the Replace method replaces all occureance in the selected range. In your case you have selected the complete sheet. So for ever matching cell found, excel would have return range object. This would be very impractical. Because excel be passing multiple range object. Thus probably causing the error. I've attached some work around code that will probably help you get your task done.... Enjoy, Rick, Fairbanks Alaska... Dim varSht As Worksheet Dim FoundCell As Range Dim FirstAdd As String ' Iterates through sheets, finds data, & replaces data For Each varSht In Worksheets ' Use Excel's Internal Find & Replace Method Set FoundCell = varSht.Cells.Find(What:=sFindWhat, _ LookAt:=xlPart, MatchCase:=True) If Not FoundCell Is Nothing Then fncStdFind = True FirstAdd = FoundCell.Address Do FoundCell.Value = sReplaceWith Set FoundCell = Cells.FindNext(FoundCell) Loop While (FoundCell.Address < FirstAdd) Exit Function Else fncStdFind = False End If Next "Trent Argante" wrote in message ... Hi Jim, vLookAt & bMatchCase are user defined variables passed into the procedure. I replaced them with xlPart & False and still got the same error. The interesting thing is that using Find method in the same manner does *not* return this 'Object required' error, but runs fine. Any othe suggestions? Thanks for your help. Trent Argante [DC.J(455)] "Jim Thomlinson" wrote: What are vLookAt and bMatchCase at runtime. As a guess that is where your problem lies. vLookAt Should be text such as xlWhole or xlPart. bMatchCase will be boolean. -- HTH... Jim Thomlinson "Trent Argante" wrote: Here' my code: Dim varSht As Variant Dim FoundCell As Range ' Iterates through sheets, finds data, & replaces data For Each varSht In Workbooks(sFilename).Sheets ' Use Excel's Internal Find & Replace Method Set FoundCell = varSht.Cells.Replace(What:=sFindWhat, _ Replacement:=sReplaceWith, _ LookAt:=vLookAt, MatchCase:=bMatchCase) If Not FoundCell Is Nothing Then fncStdFind = True Exit Function End If Next When I run this, the 'Set FoundCell = varSht.Cells.Replace' line is highlighted, and the error 'Object required.' is displayed. If I change 'Dim varSht As Variant' to 'Dim varSht As WorkSheet', then it gives me a 'Type mismatch' error with the same line highlighted. What the heck? -- Trent Argante [DC.J(455)] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Required | Excel Programming | |||
Object Required | Excel Programming | |||
Object Required | Excel Programming | |||
Object Required | Excel Discussion (Misc queries) | |||
Object required? | Excel Programming |