Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Incorrect code...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 102
Default Incorrect code...
Hi all
I'm trying to write a code to:

- Select values from a range on worksheet "Schedule" and copy/paste values
to worksheet "Thousands"

- then on the Thousands worksheet, add a formula to each cell in range
("AC22:DO100") to convert anything greater than zero to report Thousands.

- then copy/paste that range over itself so I'm just left with values.

- Lastly I've done a bit of cleanup work (get rid of zeros in currently
unused cells) and formatting...

Sounds easy enough but I'm a novice and I've obviously got this wrong
because my file size is blowing out because of the functions on this one
worksheet.

Where I've entered the "|" at the left margin of the code is where I think
my biggest problem is...???

(FYI: I am writing the code is a separate "Master Workbook" and it is
activated via autoopen/hide workbook and a button to keep it away from users).



Sub ConverttoThousands()
Dim txt1 As String
txt1 =
"=IF(AND(Schedule!$H22<0,Schedule!AC22<0),0,IF(AND (Schedule!$H220,Schedule!AC220),$Y22*IF($Z220,$ Z22/1000,Schedule!$CF$6/1000),Schedule!AC22))"
Application.ScreenUpdating = False
Sheets("Schedule").Select
Range("A13:DY100").Select
Selection.Copy
Range("H22").Select
Sheets("Thousands").Select
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Interior.ColorIndex = xlNone
Range("L13").Select
| For Each cell In Range("AC22:DO100")
| With Worksheets("Thousands")
| Range("AC22:DO100").Formula = txt1
| End With
| Next
Range("AC22:DO100").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "#,##0.00"
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AB15").Select
ActiveCell.FormulaR1C1 = "(NOTE: All figures are in '000's)"
Columns("DR:DW").Select
Selection.ClearContents
Range("L22").Select
End Sub



--
Thank for your help
BeSmart
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Incorrect code...
I am not sure the reference to the file size, but where you highlighted "|",
I changed it to this ( well removed the loop), it gave the result.

' For Each cell In Range("AC22:DO100") -- Removed this line
' With Worksheets("Thousands") -- Removed this line
Worksheets("Thousands").Range("AC22:DO100").Formul a = txt1
' End With -- Removed this line
' Next -- Removed this line

Harry

"BeSmart" wrote in message
...
Hi all
I'm trying to write a code to:

- Select values from a range on worksheet "Schedule" and copy/paste values
to worksheet "Thousands"

- then on the Thousands worksheet, add a formula to each cell in range
("AC22:DO100") to convert anything greater than zero to report Thousands.

- then copy/paste that range over itself so I'm just left with values.

- Lastly I've done a bit of cleanup work (get rid of zeros in currently
unused cells) and formatting...

Sounds easy enough but I'm a novice and I've obviously got this wrong
because my file size is blowing out because of the functions on this one
worksheet.

Where I've entered the "|" at the left margin of the code is where I think
my biggest problem is...???

(FYI: I am writing the code is a separate "Master Workbook" and it is
activated via autoopen/hide workbook and a button to keep it away from
users).



Sub ConverttoThousands()
Dim txt1 As String
txt1 =

"=IF(AND(Schedule!$H22<0,Schedule!AC22<0),0,IF(AND (Schedule!$H220,Schedule!
AC220),$Y22*IF($Z220,$Z22/1000,Schedule!$CF$6/1000),Schedule!AC22))"
Application.ScreenUpdating = False
Sheets("Schedule").Select
Range("A13:DY100").Select
Selection.Copy
Range("H22").Select
Sheets("Thousands").Select
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Interior.ColorIndex = xlNone
Range("L13").Select
| For Each cell In Range("AC22:DO100")
| With Worksheets("Thousands")
| Range("AC22:DO100").Formula = txt1
| End With
| Next
Range("AC22:DO100").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "#,##0.00"
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AB15").Select
ActiveCell.FormulaR1C1 = "(NOTE: All figures are in '000's)"
Columns("DR:DW").Select
Selection.ClearContents
Range("L22").Select
End Sub



--
Thank for your help
BeSmart


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.746 / Virus Database: 498 - Release Date: 31/08/2004


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 102
Default Incorrect code...
Thanks Harry
That worked great.
Another question on it though...

If the range ("AC22:DO100") is defined as the name "Schedule" on the excel
worksheet how do I replace the range with the name on that line?

TFYH
BeSmart

"Harry" wrote:

I am not sure the reference to the file size, but where you highlighted "|",
I changed it to this ( well removed the loop), it gave the result.

' For Each cell In Range("AC22:DO100") -- Removed this line
' With Worksheets("Thousands") -- Removed this line
Worksheets("Thousands").Range("AC22:DO100").Formul a = txt1
' End With -- Removed this line
' Next -- Removed this line

Harry

"BeSmart" wrote in message
...
Hi all
I'm trying to write a code to:

- Select values from a range on worksheet "Schedule" and copy/paste values
to worksheet "Thousands"

- then on the Thousands worksheet, add a formula to each cell in range
("AC22:DO100") to convert anything greater than zero to report Thousands.

- then copy/paste that range over itself so I'm just left with values.

- Lastly I've done a bit of cleanup work (get rid of zeros in currently
unused cells) and formatting...

Sounds easy enough but I'm a novice and I've obviously got this wrong
because my file size is blowing out because of the functions on this one
worksheet.

Where I've entered the "|" at the left margin of the code is where I think
my biggest problem is...???

(FYI: I am writing the code is a separate "Master Workbook" and it is
activated via autoopen/hide workbook and a button to keep it away from
users).



Sub ConverttoThousands()
Dim txt1 As String
txt1 =

"=IF(AND(Schedule!$H22<0,Schedule!AC22<0),0,IF(AND (Schedule!$H220,Schedule!
AC220),$Y22*IF($Z220,$Z22/1000,Schedule!$CF$6/1000),Schedule!AC22))"
Application.ScreenUpdating = False
Sheets("Schedule").Select
Range("A13:DY100").Select
Selection.Copy
Range("H22").Select
Sheets("Thousands").Select
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Interior.ColorIndex = xlNone
Range("L13").Select
| For Each cell In Range("AC22:DO100")
| With Worksheets("Thousands")
| Range("AC22:DO100").Formula = txt1
| End With
| Next
Range("AC22:DO100").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "#,##0.00"
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AB15").Select
ActiveCell.FormulaR1C1 = "(NOTE: All figures are in '000's)"
Columns("DR:DW").Select
Selection.ClearContents
Range("L22").Select
End Sub



--
Thank for your help
BeSmart


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.746 / Virus Database: 498 - Release Date: 31/08/2004



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
Incorrect Am and Pm MrBlackForest Excel Discussion (Misc queries) 2 January 26th 06 03:11 AM
Incorrect subject ... but B. [email protected] Excel Discussion (Misc queries) 1 June 12th 05 01:44 AM
Incorrect Sum ashlandpmac Excel Discussion (Misc queries) 1 April 15th 05 01:25 AM
Incorrect Sum AshlandPmac Excel Discussion (Misc queries) 1 April 14th 05 10:41 PM
Incorrect total Karen Excel Worksheet Functions 5 November 26th 04 02:41 AM


All times are GMT +1. The time now is 06:02 PM.

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