Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code Help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Code Help
First of all, I would like to explain what I am trying to do. I hav
Many columns of data, with a blank column set up next to each one.
was scanning the data for empty cells and placing "-" in the colum
next to empty, if not empty, placing the value instead. Then, it wen
back and deleted all of the old columns. I did a record macro. Now
my problem is my data is longer (in terms of rows) and the macro is n
good. The code looks insane, with all kinds of scrolls in it. Coul
someone look at the code and give some advice on making it simpler?
Thank you

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,""-"",RC[-1])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D157")
Range("D2:D157").Select
Range("D2").Select
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F2:F157")
Range("F2:F157").Select
Range("F2").Select
Selection.Copy
Range("H2").Select
ActiveSheet.Paste
Range("J2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Range("L2").Select
ActiveSheet.Paste
Range("N2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("P2").Select
ActiveSheet.Paste
Range("R2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("T2").Select
ActiveSheet.Paste
Range("V2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Range("X2").Select
ActiveSheet.Paste
Range("Z2").Select
ActiveSheet.Paste
Range("AB2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("AD2").Select
ActiveSheet.Paste
Range("AF2").Select
ActiveSheet.Paste
Range("AH2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("AJ2").Select
ActiveSheet.Paste
Range("AL2").Select
ActiveSheet.Paste
Range("AM37").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-0.839999973773956"
Range("AL2").Select
Selection.Copy
Range("AN2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=2
Range("AP2").Select
ActiveSheet.Paste
Range("AR2").Select
ActiveSheet.Paste
Range("AT2").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-15
Range("H2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H2:H157")
Range("H2:H157").Select
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J157")
Range("J2:J157").Select
ActiveWindow.SmallScroll ToRight:=3
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L157")
Range("L2:L157").Select
ActiveWindow.SmallScroll ToRight:=3
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N157")
Range("N2:N157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P157")
Range("P2:P157").Select
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R157")
Range("R2:R157").Select
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T157")
Range("T2:T157").Select
ActiveWindow.SmallScroll ToRight:=5
Range("V2").Select
Selection.AutoFill Destination:=Range("V2:V157")
Range("V2:V157").Select
Range("X2").Select
Selection.AutoFill Destination:=Range("X2:X157")
Range("X2:X157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("Z2").Select
Selection.AutoFill Destination:=Range("Z2:Z157")
Range("Z2:Z157").Select
Range("AB2").Select
Selection.AutoFill Destination:=Range("AB2:AB157")
Range("AB2:AB157").Select
ActiveWindow.SmallScroll ToRight:=5
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD157")
Range("AD2:AD157").Select
Range("AF2").Select
Selection.AutoFill Destination:=Range("AF2:AF157")
Range("AF2:AF157").Select
Range("AH2").Select
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("AH2:AH157")
Range("AH2:AH157").Select
Range("AJ2").Select
Selection.AutoFill Destination:=Range("AJ2:AJ157")
Range("AJ2:AJ157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AL2").Select
Selection.AutoFill Destination:=Range("AL2:AL157")
Range("AL2:AL157").Select
Range("AN2").Select
Selection.AutoFill Destination:=Range("AN2:AN157")
Range("AN2:AN157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AP2").Select
Selection.AutoFill Destination:=Range("AP2:AP157")
Range("AP2:AP157").Select
Range("AR2").Select
Selection.AutoFill Destination:=Range("AR2:AR157")
Range("AR2:AR157").Select
Range("AT2").Select
Selection.AutoFill Destination:=Range("AT2:AT157")
Range("AT2:AT157").Select


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Code Help
The rest:

ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=5
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("N:N").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=5
Columns("P:P").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=5
Columns("V:V").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("X:X").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
Columns("Z:Z").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("AB:AB").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("AD:AD").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=4
Columns("AF:AF").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("AH:AH").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=4
Columns("AJ:AJ").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AL:AL").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=4
Columns("AN:AN").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AP:AP").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AR:AR").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AT:AT").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
ActiveWindow.SmallScroll ToRight:=9
Selection.Delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Columns("M:M").Select
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("P:P").Select
Selection.Delete Shift:=xlToLeft
Columns("Q:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("R:R").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=5
Columns("S:S").Select
Selection.Delete Shift:=xlToLeft
Columns("T:T").Select
Selection.Delete Shift:=xlToLeft
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Columns("V:V").Select
Selection.Delete Shift:=xlToLeft
Columns("W:W").Select
Selection.Delete Shift:=xlToLeft
Columns("X:X").Select
Selection.Delete Shift:=xlToLeft
End Sub


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Code Help
Actually, is there any way to just make a blank cell contain "-". Lik
in the custom format field

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default Code Help
Not through custom formats, but you could use Replace.

Select your entire area from D2:AT157 then select Edit Replace from the
menu.
Make sure the Find What box is empty, and type your hyphen into the Replace
With box.

I tried this with "Make sure that "Find Entire Cells Only" ticked and again
with it unticked and it didn't seem to make any difference, although I would
have thought it would have. Huh.

--
Dianne Butterworth


Actually, is there any way to just make a blank cell contain "-".
Like in the custom format field?


---
Message posted from http://www.ExcelForum.com/


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

Advanced Search
Display Modes
Hybrid Mode Hybrid 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
split post code (zip code) out of cell that includes full address Concord Excel Discussion (Misc queries) 4 October 15th 09 06:59 PM
Code to conditional format all black after date specified in code? wx4usa Excel Discussion (Misc queries) 3 December 26th 08 07:06 PM
Drop Down/List w/Code and Definition, only code entered when selec Spiritdancer Excel Worksheet Functions 2 November 2nd 07 03:57 AM
option buttons run Click code when value is changed via VBA code neonangel Excel Programming 5 July 27th 04 08:32 AM
VBA code delete code but ask for password and unlock VBA protection WashoeJeff Excel Programming 0 January 27th 04 07:07 AM


All times are GMT +1. The time now is 12:04 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement