User defined Nth_Occurence function fails when new data is loadingvia macro
 

This doesn't happen if I load the data manually. Please help.
Tried many googles and searches.....

Here's macro that loads the data;

Private Sub GetCallout_Click()
Application.ScreenUpdating = False
Dim wdApp As Object
Dim wdDoc As Variant
Application.EnableEvents = False
Worksheets("CallOut").Range("A1:Z999").ClearConten ts
Application.EnableEvents = True
Worksheets("CallOut").Activate
ActiveSheet.Range("A1").Select
CalloutDate = InputBox("Enter the Callout Date (mmddyyyy)",
"Callout Date")
Set wdApp = CreateObject("Word.application")
On Error GoTo fnf
wdApp.documents.Open Filename:="S:\Callout\" & CalloutDate &
"MSU.doc"
wdApp.documents(1).Select
wdApp.Selection.Copy
ActiveSheet.Paste
wdApp.Quit
Set wdApp = Nothing
Set wdDoc = Nothing
Worksheets("WorkHrs").Activate
Sheets("WorkHrs").Range("b4").Copy
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
Exit Sub
fnf:
Call FNFX

End Sub

Here's Nth_Occurence function;

Function Nth_Occurrence(range_look As Range, find_it As String,
occurrence As Long, offset_row As Long, offset_col As Long)

Dim lCount As Long

Dim rFound As Range



Set rFound = range_look.Cells(1, 1)

For lCount = 1 To occurrence

Set rFound = range_look.Find(find_it, rFound, xlValues,
xlWhole)

Next lCount

Nth_Occurrence = rFound.Offset(offset_row, offset_col)

End Function

User defined Nth_Occurence function fails when new data is loadingvia macro
 

hi

There is nowhere in the first macro where you show a call to the
Nth_Occurrence function, when you call it, do you provide all the
arguments?

At a glance, I suspect it may not be working because of the use of
"xlwhole" in the below line of code:

Code:
--------------------
Set rFound = range_look.Find(find_it, rFound, xlValues,
xlWhole)
--------------------

When the macro "GetCallout_Click" is run, does the result get pasted
into the excel sheet differently (eg into a single column) or at least
appear differently than when it is done manually?

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=99007