Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I refresh user defined function data? | Excel Programming | |||
Nth_Occurence User Defined Formula | Excel Programming | |||
Access connection works in Macro, not in user-defined Function | Excel Programming | |||
Macro user defined function row information | Excel Programming | |||
User Defined Function used in Macro returns #VALUE | Excel Programming |