| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help me with this error...user defined not defined.
I have a macro that does find actual used range from a worksheet, and
copy/paste into newly created word document. But I am having a error saying "user defined not defined" please.. Thank you for taking time to read this. Sub MyUsedRange() Dim ar As Range, r As Double, c As Integer, tr As Double, tc As Integer Dim ur As Range, fr As Double, fc As Integer, tfr As Double, tfc As Integer On Error Resume Next fc = ActiveSheet.Columns.Count fr = ActiveSheet.Rows.Count Set ur = Union(ActiveSheet.UsedRange.SpecialCells(xlCellTyp eConstants), _ ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)) If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants) End If If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las) End If If Err.Number = 0 Then For Each ar In ur.Areas 'tr = ar.Range("A1").Row + ar.Rows.Count - 1 tr = (ar.Range("A1").Row + 17) + ar.Rows.Count - 1 'tc = ar.Range("A1").Column + ar.Columns.Count - 1 tc = ar.Range("A1").Column - 1 + ar.Columns.Count - 1 If tc c Then c = tc If tr r Then r = tr tfr = ar.Range("A1").Row 'tfc = ar.Range("A1").Column tfc = ar.Range("A1").Column - 1 If tfc < fc Then fc = tfc If tfr < fr Then fr = tfr Next Range(Cells(fr, fc), Cells(r, c)).Select ElseIf Err.Number = 1004 Then 'Range("A1").Select End If End Sub Sub PasteTableToWord() Dim obj As Word.Application 'Activate the worksheet Worksheets("sheet9").Activate 'Select the range of cells to copy 'Worksheets("sheet1").Range("a1:c10").Copy Call MyUsedRange 'Create a word object Set obj = CreateObject("Word.Application.11") 'Make Word visible obj.Visible = True 'Create a new file. Set newDoc = obj.Documents.Add 'Determine if Microsoft Excel is running on the Macintosh or Windows. ''If (Application.OperatingSystem Like "*Mac*") Then 'AppActivate "Microsoft word" 'obj.Selection.PasteSpecial 'Paste data into Word ''Else 'If Windows NT/95/3.x - paste data into Word obj.Selection.PasteSpecial ''End If 'Format table ''obj.Selection.Tables(1).AutoFormat Format:=wdTableFormatGrid1 'Save the file newDoc.SaveAs Filename:="C:\TestDoc.doc" 'Quit Word obj.Quit 'Release object Set obj = Nothing End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
please help me with this error...user defined not defined.
Which line of code receives the error (Debug - Compile)
-- HTH... Jim Thomlinson "Steven" wrote: I have a macro that does find actual used range from a worksheet, and copy/paste into newly created word document. But I am having a error saying "user defined not defined" please.. Thank you for taking time to read this. Sub MyUsedRange() Dim ar As Range, r As Double, c As Integer, tr As Double, tc As Integer Dim ur As Range, fr As Double, fc As Integer, tfr As Double, tfc As Integer On Error Resume Next fc = ActiveSheet.Columns.Count fr = ActiveSheet.Rows.Count Set ur = Union(ActiveSheet.UsedRange.SpecialCells(xlCellTyp eConstants), _ ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)) If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants) End If If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las) End If If Err.Number = 0 Then For Each ar In ur.Areas 'tr = ar.Range("A1").Row + ar.Rows.Count - 1 tr = (ar.Range("A1").Row + 17) + ar.Rows.Count - 1 'tc = ar.Range("A1").Column + ar.Columns.Count - 1 tc = ar.Range("A1").Column - 1 + ar.Columns.Count - 1 If tc c Then c = tc If tr r Then r = tr tfr = ar.Range("A1").Row 'tfc = ar.Range("A1").Column tfc = ar.Range("A1").Column - 1 If tfc < fc Then fc = tfc If tfr < fr Then fr = tfr Next Range(Cells(fr, fc), Cells(r, c)).Select ElseIf Err.Number = 1004 Then 'Range("A1").Select End If End Sub Sub PasteTableToWord() Dim obj As Word.Application 'Activate the worksheet Worksheets("sheet9").Activate 'Select the range of cells to copy 'Worksheets("sheet1").Range("a1:c10").Copy Call MyUsedRange 'Create a word object Set obj = CreateObject("Word.Application.11") 'Make Word visible obj.Visible = True 'Create a new file. Set newDoc = obj.Documents.Add 'Determine if Microsoft Excel is running on the Macintosh or Windows. ''If (Application.OperatingSystem Like "*Mac*") Then 'AppActivate "Microsoft word" 'obj.Selection.PasteSpecial 'Paste data into Word ''Else 'If Windows NT/95/3.x - paste data into Word obj.Selection.PasteSpecial ''End If 'Format table ''obj.Selection.Tables(1).AutoFormat Format:=wdTableFormatGrid1 'Save the file newDoc.SaveAs Filename:="C:\TestDoc.doc" 'Quit Word obj.Quit 'Release object Set obj = Nothing End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
please help me with this error...user defined not defined.
Sub MyUsedRange()
Dim ar As Range, r As Double, c As Integer, tr As Double, tc As Integer Dim ur As Range, fr As Double, fc As Integer, tfr As Double, tfc As Integer On Error Resume Next fc = ActiveSheet.Columns.Count fr = ActiveSheet.Rows.Count Set ur = Union(ActiveSheet.UsedRange.SpecialCells(xlCellTyp eConstants), _ ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)) If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants) End If If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las) End If If Err.Number = 0 Then For Each ar In ur.Areas 'tr = ar.Range("A1").Row + ar.Rows.Count - 1 tr = (ar.Range("A1").Row + 1) + ar.Rows.Count - 1 'tc = ar.Range("A1").Column + ar.Columns.Count - 1 tc = ar.Range("A1").Column - 1 + ar.Columns.Count - 1 If tc c Then c = tc If tr r Then r = tr tfr = ar.Range("A1").Row 'tfc = ar.Range("A1").Column tfc = ar.Range("A1").Column - 1 If tfc < fc Then fc = tfc If tfr < fr Then fr = tfr Next Range(Cells(fr, fc), Cells(r, c)).Select ElseIf Err.Number = 1004 Then 'Range("A1").Select End If End Sub Sub PasteTableToWord() <-----------yellow highlighted Dim obj As Word.Application <----------------blue highlighted. 'Activate the worksheet Worksheets("sheet9").Activate 'Select the range of cells to copy 'Worksheets("sheet1").Range("a1:c10").Copy Call MyUsedRange 'Create a word object Set obj = CreateObject("Word.Application.11") 'Make Word visible obj.Visible = True 'Create a new file. Set newDoc = obj.Documents.Add 'Determine if Microsoft Excel is running on the Macintosh or Windows. ''If (Application.OperatingSystem Like "*Mac*") Then 'AppActivate "Microsoft word" 'obj.Selection.PasteSpecial 'Paste data into Word ''Else 'If Windows NT/95/3.x - paste data into Word obj.Selection.PasteSpecial ''End If __________________________________________________ __ compile error: User-defined not defined. <---------this is what I get Thank you, Jim |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
please help me with this error...user defined not defined.
My best guess is that you have not referenced the Microsoft Word Object
Library. In the VB Editor Tools - References - Microsoft Word #.# Object Library... -- HTH... Jim Thomlinson "Steven" wrote: Sub MyUsedRange() Dim ar As Range, r As Double, c As Integer, tr As Double, tc As Integer Dim ur As Range, fr As Double, fc As Integer, tfr As Double, tfc As Integer On Error Resume Next fc = ActiveSheet.Columns.Count fr = ActiveSheet.Rows.Count Set ur = Union(ActiveSheet.UsedRange.SpecialCells(xlCellTyp eConstants), _ ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)) If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants) End If If Err.Number = 1004 Then Err.Clear Set ur = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las) End If If Err.Number = 0 Then For Each ar In ur.Areas 'tr = ar.Range("A1").Row + ar.Rows.Count - 1 tr = (ar.Range("A1").Row + 1) + ar.Rows.Count - 1 'tc = ar.Range("A1").Column + ar.Columns.Count - 1 tc = ar.Range("A1").Column - 1 + ar.Columns.Count - 1 If tc c Then c = tc If tr r Then r = tr tfr = ar.Range("A1").Row 'tfc = ar.Range("A1").Column tfc = ar.Range("A1").Column - 1 If tfc < fc Then fc = tfc If tfr < fr Then fr = tfr Next Range(Cells(fr, fc), Cells(r, c)).Select ElseIf Err.Number = 1004 Then 'Range("A1").Select End If End Sub Sub PasteTableToWord() <-----------yellow highlighted Dim obj As Word.Application <----------------blue highlighted. 'Activate the worksheet Worksheets("sheet9").Activate 'Select the range of cells to copy 'Worksheets("sheet1").Range("a1:c10").Copy Call MyUsedRange 'Create a word object Set obj = CreateObject("Word.Application.11") 'Make Word visible obj.Visible = True 'Create a new file. Set newDoc = obj.Documents.Add 'Determine if Microsoft Excel is running on the Macintosh or Windows. ''If (Application.OperatingSystem Like "*Mac*") Then 'AppActivate "Microsoft word" 'obj.Selection.PasteSpecial 'Paste data into Word ''Else 'If Windows NT/95/3.x - paste data into Word obj.Selection.PasteSpecial ''End If __________________________________________________ __ compile error: User-defined not defined. <---------this is what I get Thank you, Jim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
| Workspace faux user-defined type not defined | Excel Programming | |||
| User-defined data type; Error: Only User-defined types... | Excel Programming | |||
| Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming | |||
| Word.Document - user defined type not defined | Excel Programming | |||
Linear Mode
