Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > What does Excel97 not like about this Code Q
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default What does Excel97 not like about this Code Q
I am getting an error "compile error in hidden module EMail". It relates to
the code below not exceuting an I can't work out why. It works fine on Excel
2000 and above. It doesn't go into debugjust the dialog box with the message
above

Thanks



Sub Email()


Dim IngPosY As Long ' Input Cell Number
Dim IngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean


Sheets("Coda").Visible = True
Sheets("Coda Template").Visible = True
Sheets("Coda").Select

ActiveSheet.Unprotect Password:="1234"
Cells.Select
Selection.ClearContents
Range("A1").Select


IngPosY = 2 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "Document_Type"
Range("D1") = "Document_date"
Range("E1") = "Nominal"
Range("F1") = "Subaccount"
Range("G1") = "Level3"
Range("H1") = "Document_Value"
Range("I1") = "Document_Year"
Range("J1") = "Document_Period"
Range("K1") = "External_Text"
Range("L1") = "Quantity_1"
Range("M1") = "Description"


IngOutY = 2

Do While Len(Worksheets("Coda Template").Range("A" & IngPosY)) 0
Range("a" & IngOutY) = Worksheets("Coda Template").Range("A" &
IngPosY)
Range("B" & IngOutY) = Worksheets("Coda Template").Range("B" &
IngPosY)
Range("C" & IngOutY) = Worksheets("Coda Template").Range("C" &
IngPosY)
Range("D" & IngOutY) = Worksheets("Coda Template").Range("D" &
IngPosY)
Range("E" & IngOutY) = Worksheets("Coda Template").Range("E" &
IngPosY)
Range("F" & IngOutY) = Worksheets("Coda Template").Range("F" &
IngPosY)
Range("G" & IngOutY) = Worksheets("Coda Template").Range("G" &
IngPosY)
Range("H" & IngOutY) = Round(Worksheets("Coda Template").Range("H" &
IngPosY), 2)
Range("I" & IngOutY) = Worksheets("Coda Template").Range("I" &
IngPosY)
Range("J" & IngOutY) = Worksheets("Coda Template").Range("J" &
IngPosY)
Range("K" & IngOutY) = Worksheets("Coda Template").Range("K" &
IngPosY)
Range("L" & IngOutY) = Worksheets("Coda Template").Range("L" &
IngPosY)
Range("M" & IngOutY) = Worksheets("Coda Template").Range("M" &
IngPosY)


IngPosY = IngPosY + 1
IngOutY = IngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D:D").Select
Selection.NumberFormat = "dd/mm/yyyy"
Columns("E:G").Select
Selection.NumberFormat = "0"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("I:J").Select
Selection.NumberFormat = "0"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "0.00"
Cells.Select
Columns("M:M").Select
Selection.NumberFormat = "@"
Cells.EntireColumn.AutoFit

For i = Range("E500").End(xlUp).Row To 2 Step -1
If Left(Range("E" & i).Value, 3) < "VAT" Or _
Range("L" & i).Value = 0 Then
If Range("H" & i).Value = 0 Then
Range("H" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=IF(LEFT(E3,5)=""16000"",1,B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b500").End(xlUp).Row)
End With

Range("A2").Select


Sheets("Coda").Visible = False
Sheets("Coda Template").Visible = False

Sheets("Claim Form").Select

Range("B10").Select

End Sub






Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default What does Excel97 not like about this Code Q
John,

The problem is with the line

Range("H" & IngOutY) = Round(Worksheets("Coda
Template").Range("H" & IngPosY), 2)

VBA5 (which is what Excel97 runs) doesn't have a Round function.
Round was added in VBA6 (which is in Excel 2000 and later).

Change the line to
Range("H" & IngOutY) = Application.Round(Worksheets("Coda
Template").Range("H" & IngPosY), 2)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"John" wrote in message
...
I am getting an error "compile error in hidden module EMail". It
relates to the code below not exceuting an I can't work out why.
It works fine on Excel 2000 and above. It doesn't go into
debugjust the dialog box with the message above

Thanks



Sub Email()


Dim IngPosY As Long ' Input Cell Number
Dim IngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean


Sheets("Coda").Visible = True
Sheets("Coda Template").Visible = True
Sheets("Coda").Select

ActiveSheet.Unprotect Password:="1234"
Cells.Select
Selection.ClearContents
Range("A1").Select


IngPosY = 2 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "Document_Type"
Range("D1") = "Document_date"
Range("E1") = "Nominal"
Range("F1") = "Subaccount"
Range("G1") = "Level3"
Range("H1") = "Document_Value"
Range("I1") = "Document_Year"
Range("J1") = "Document_Period"
Range("K1") = "External_Text"
Range("L1") = "Quantity_1"
Range("M1") = "Description"


IngOutY = 2

Do While Len(Worksheets("Coda Template").Range("A" &
IngPosY)) 0
Range("a" & IngOutY) = Worksheets("Coda
Template").Range("A" & IngPosY)
Range("B" & IngOutY) = Worksheets("Coda
Template").Range("B" & IngPosY)
Range("C" & IngOutY) = Worksheets("Coda
Template").Range("C" & IngPosY)
Range("D" & IngOutY) = Worksheets("Coda
Template").Range("D" & IngPosY)
Range("E" & IngOutY) = Worksheets("Coda
Template").Range("E" & IngPosY)
Range("F" & IngOutY) = Worksheets("Coda
Template").Range("F" & IngPosY)
Range("G" & IngOutY) = Worksheets("Coda
Template").Range("G" & IngPosY)
Range("H" & IngOutY) = Round(Worksheets("Coda
Template").Range("H" & IngPosY), 2)
Range("I" & IngOutY) = Worksheets("Coda
Template").Range("I" & IngPosY)
Range("J" & IngOutY) = Worksheets("Coda
Template").Range("J" & IngPosY)
Range("K" & IngOutY) = Worksheets("Coda
Template").Range("K" & IngPosY)
Range("L" & IngOutY) = Worksheets("Coda
Template").Range("L" & IngPosY)
Range("M" & IngOutY) = Worksheets("Coda
Template").Range("M" & IngPosY)


IngPosY = IngPosY + 1
IngOutY = IngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D:D").Select
Selection.NumberFormat = "dd/mm/yyyy"
Columns("E:G").Select
Selection.NumberFormat = "0"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("I:J").Select
Selection.NumberFormat = "0"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "0.00"
Cells.Select
Columns("M:M").Select
Selection.NumberFormat = "@"
Cells.EntireColumn.AutoFit

For i = Range("E500").End(xlUp).Row To 2 Step -1
If Left(Range("E" & i).Value, 3) < "VAT" Or _
Range("L" & i).Value = 0 Then
If Range("H" & i).Value = 0 Then
Range("H" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=IF(LEFT(E3,5)=""16000"",1,B2+1)"
.AutoFill Destination:=Range("B3:B" &
Range("b500").End(xlUp).Row)
End With

Range("A2").Select


Sheets("Coda").Visible = False
Sheets("Coda Template").Visible = False

Sheets("Claim Form").Select

Range("B10").Select

End Sub








Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default What does Excel97 not like about this Code Q
An update on this. I've found that it debugs on "Round"

I am Rounding whatever value is in Coda Template "H"

Is there a similar function in Excel97?

Thanks


"John" wrote in message
...
I am getting an error "compile error in hidden module EMail". It relates to
the code below not exceuting an I can't work out why. It works fine on
Excel 2000 and above. It doesn't go into debugjust the dialog box with the
message above

Thanks



Sub Email()


Dim IngPosY As Long ' Input Cell Number
Dim IngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean


Sheets("Coda").Visible = True
Sheets("Coda Template").Visible = True
Sheets("Coda").Select

ActiveSheet.Unprotect Password:="1234"
Cells.Select
Selection.ClearContents
Range("A1").Select


IngPosY = 2 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "Document_Type"
Range("D1") = "Document_date"
Range("E1") = "Nominal"
Range("F1") = "Subaccount"
Range("G1") = "Level3"
Range("H1") = "Document_Value"
Range("I1") = "Document_Year"
Range("J1") = "Document_Period"
Range("K1") = "External_Text"
Range("L1") = "Quantity_1"
Range("M1") = "Description"


IngOutY = 2

Do While Len(Worksheets("Coda Template").Range("A" & IngPosY)) 0
Range("a" & IngOutY) = Worksheets("Coda Template").Range("A" &
IngPosY)
Range("B" & IngOutY) = Worksheets("Coda Template").Range("B" &
IngPosY)
Range("C" & IngOutY) = Worksheets("Coda Template").Range("C" &
IngPosY)
Range("D" & IngOutY) = Worksheets("Coda Template").Range("D" &
IngPosY)
Range("E" & IngOutY) = Worksheets("Coda Template").Range("E" &
IngPosY)
Range("F" & IngOutY) = Worksheets("Coda Template").Range("F" &
IngPosY)
Range("G" & IngOutY) = Worksheets("Coda Template").Range("G" &
IngPosY)
Range("H" & IngOutY) = Round(Worksheets("Coda Template").Range("H"
& IngPosY), 2)
Range("I" & IngOutY) = Worksheets("Coda Template").Range("I" &
IngPosY)
Range("J" & IngOutY) = Worksheets("Coda Template").Range("J" &
IngPosY)
Range("K" & IngOutY) = Worksheets("Coda Template").Range("K" &
IngPosY)
Range("L" & IngOutY) = Worksheets("Coda Template").Range("L" &
IngPosY)
Range("M" & IngOutY) = Worksheets("Coda Template").Range("M" &
IngPosY)


IngPosY = IngPosY + 1
IngOutY = IngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D:D").Select
Selection.NumberFormat = "dd/mm/yyyy"
Columns("E:G").Select
Selection.NumberFormat = "0"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("I:J").Select
Selection.NumberFormat = "0"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "0.00"
Cells.Select
Columns("M:M").Select
Selection.NumberFormat = "@"
Cells.EntireColumn.AutoFit

For i = Range("E500").End(xlUp).Row To 2 Step -1
If Left(Range("E" & i).Value, 3) < "VAT" Or _
Range("L" & i).Value = 0 Then
If Range("H" & i).Value = 0 Then
Range("H" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=IF(LEFT(E3,5)=""16000"",1,B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b500").End(xlUp).Row)
End With

Range("A2").Select


Sheets("Coda").Visible = False
Sheets("Coda Template").Visible = False

Sheets("Claim Form").Select

Range("B10").Select

End Sub








Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default What does Excel97 not like about this Code Q
Thanks Chip


"Chip Pearson" wrote in message
...
John,

The problem is with the line

Range("H" & IngOutY) = Round(Worksheets("Coda Template").Range("H" &
IngPosY), 2)

VBA5 (which is what Excel97 runs) doesn't have a Round function. Round was
added in VBA6 (which is in Excel 2000 and later).

Change the line to
Range("H" & IngOutY) = Application.Round(Worksheets("Coda
Template").Range("H" & IngPosY), 2)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"John" wrote in message
...
I am getting an error "compile error in hidden module EMail". It relates
to the code below not exceuting an I can't work out why. It works fine on
Excel 2000 and above. It doesn't go into debugjust the dialog box with the
message above

Thanks



Sub Email()


Dim IngPosY As Long ' Input Cell Number
Dim IngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean


Sheets("Coda").Visible = True
Sheets("Coda Template").Visible = True
Sheets("Coda").Select

ActiveSheet.Unprotect Password:="1234"
Cells.Select
Selection.ClearContents
Range("A1").Select


IngPosY = 2 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "Document_Type"
Range("D1") = "Document_date"
Range("E1") = "Nominal"
Range("F1") = "Subaccount"
Range("G1") = "Level3"
Range("H1") = "Document_Value"
Range("I1") = "Document_Year"
Range("J1") = "Document_Period"
Range("K1") = "External_Text"
Range("L1") = "Quantity_1"
Range("M1") = "Description"


IngOutY = 2

Do While Len(Worksheets("Coda Template").Range("A" & IngPosY)) 0
Range("a" & IngOutY) = Worksheets("Coda Template").Range("A" &
IngPosY)
Range("B" & IngOutY) = Worksheets("Coda Template").Range("B" &
IngPosY)
Range("C" & IngOutY) = Worksheets("Coda Template").Range("C" &
IngPosY)
Range("D" & IngOutY) = Worksheets("Coda Template").Range("D" &
IngPosY)
Range("E" & IngOutY) = Worksheets("Coda Template").Range("E" &
IngPosY)
Range("F" & IngOutY) = Worksheets("Coda Template").Range("F" &
IngPosY)
Range("G" & IngOutY) = Worksheets("Coda Template").Range("G" &
IngPosY)
Range("H" & IngOutY) = Round(Worksheets("Coda Template").Range("H"
& IngPosY), 2)
Range("I" & IngOutY) = Worksheets("Coda Template").Range("I" &
IngPosY)
Range("J" & IngOutY) = Worksheets("Coda Template").Range("J" &
IngPosY)
Range("K" & IngOutY) = Worksheets("Coda Template").Range("K" &
IngPosY)
Range("L" & IngOutY) = Worksheets("Coda Template").Range("L" &
IngPosY)
Range("M" & IngOutY) = Worksheets("Coda Template").Range("M" &
IngPosY)


IngPosY = IngPosY + 1
IngOutY = IngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D:D").Select
Selection.NumberFormat = "dd/mm/yyyy"
Columns("E:G").Select
Selection.NumberFormat = "0"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("I:J").Select
Selection.NumberFormat = "0"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "0.00"
Cells.Select
Columns("M:M").Select
Selection.NumberFormat = "@"
Cells.EntireColumn.AutoFit

For i = Range("E500").End(xlUp).Row To 2 Step -1
If Left(Range("E" & i).Value, 3) < "VAT" Or _
Range("L" & i).Value = 0 Then
If Range("H" & i).Value = 0 Then
Range("H" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=IF(LEFT(E3,5)=""16000"",1,B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b500").End(xlUp).Row)
End With

Range("A2").Select


Sheets("Coda").Visible = False
Sheets("Coda Template").Visible = False

Sheets("Claim Form").Select

Range("B10").Select

End Sub










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel97: Show UserForm while running code? Kozo Morimoto Excel Programming 3 August 11th 05 10:37 AM
PointsToScreenPixels for Excel97 MRay Excel Programming 4 March 18th 05 04:39 AM
MS-Query code from EXCEL97 doesn't work in EXCEL2003 ?? ln54 Excel Discussion (Misc queries) 4 February 21st 05 08:12 PM
why doesn't excel97 winNT vba macro code work in excel2002 winXP??? m a Excel Programming 4 June 23rd 04 06:20 PM
Help with Excel97 formatting Dave Peterson[_3_] Excel Programming 0 September 13th 03 02:57 PM


All times are GMT +1. The time now is 02:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement