Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range & Fill Column with Formula - Help
Here is the code I have but am having problems with my loop for filling the
cells in the new range: Sub AutoFillColE() ' ' AutoFillColE Macro ' Auto fill col E based upon length of Current Region ' Dim rngCell As Range Dim rngCurrent As Range Dim shtFlagelNoB05 As Worksheet Set shtFlagelNoB05 = Application.ActiveWorkbook.Worksheets("Flagel_ERP_ NoB05") Set rngCurrent = shtFlagelNoB05.Range("A4").CurrentRegion Set rngCurrent = rngCurrent.Offset(rowoffset:=1, columnoffset:=4) Set rngCurrent = rngCurrent.Resize(RowSize:=rngCurrent, ColumnSize:=1) 'Paste formula in each cell in Column E from E5 down to bottom of new range For Each rngCell In rngCurrent rngcell.Formula = "=IF(B4 <= TODAY()-77,"11+ Weeks",IF(B4 <= TODAY()-42,"6 to 10 Weeks",IF(B4 <= TODAY()-7,"1 to 5 Weeks",IF(B4 TODAY()-7,"Current Week"))))" Next rngCell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range & Fill Column with Formula - Help
TRY THIS, THIS WILL PUT THAT FORMULA IN EVERY CELL FROM E5 TO THE LAST USED ROW IN COLUMN E: 'Paste formula in each cell in Column E from E5 down to bottom of new range For Each rngCell In range("E5:E" & Range("E" & rows.count).end(xlup).Row) rngcell.Formula = "=IF(B4 <= TODAY()-77,"11+ Weeks",IF(B4 <= TODAY()-42,"6 to 10 Weeks",IF(B4 <= TODAY()-7,"1 to 5 Weeks",IF(B4 TODAY()-7,"Current Week"))))" Next rngCell End Sub *Is this what you wanted?* -- Breakfast Guy ------------------------------------------------------------------------ Breakfast Guy's Profile: http://www.thecodecage.com/forumz/member.php?userid=5 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6849 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range & Fill Column with Formula - Help
For some reason my VBA editor does not like the formula line. It stays red:
rngcell.Formula = "=IF(B4 <= TODAY()-77,"11+ Weeks",IF(B4 <=TODAY()-42, _ "6 to 10 Weeks",IF(B4 <= TODAY()-7,"1 to 5 Weeks",IF(B4 TODAY()-7, _ "Current Week"))))" "Breakfast Guy" wrote: TRY THIS, THIS WILL PUT THAT FORMULA IN EVERY CELL FROM E5 TO THE LAST USED ROW IN COLUMN E: 'Paste formula in each cell in Column E from E5 down to bottom of new range For Each rngCell In range("E5:E" & Range("E" & rows.count).end(xlup).Row) rngcell.Formula = "=IF(B4 <= TODAY()-77,"11+ Weeks",IF(B4 <= TODAY()-42,"6 to 10 Weeks",IF(B4 <= TODAY()-7,"1 to 5 Weeks",IF(B4 TODAY()-7,"Current Week"))))" Next rngCell End Sub *Is this what you wanted?* -- Breakfast Guy ------------------------------------------------------------------------ Breakfast Guy's Profile: http://www.thecodecage.com/forumz/member.php?userid=5 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6849 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range & Fill Column with Formula - Help
Its simpley because you have your text mixed up with out the & operator, it should read: rngcell.Formula = "=IF(B4 <= TODAY()-77,11+ Weeks,IF(B4 <=TODAY()-42,6 to 10 Weeks,IF(B4 <= _ TODAY()-7,1 to 5 Weeks,IF(B4 TODAY()-7,Current Week))))" But if you want to use your way, after you close a quote you need to add & then open quotes for your next section then after you close the quotes use &...etc Does this help? 'The Code Cage' (http://www.thecodecage.com) -- Breakfast Guy ------------------------------------------------------------------------ Breakfast Guy's Profile: http://www.thecodecage.com/forumz/member.php?userid=5 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6849 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range & Fill Column with Formula - Help
In the IF statement the "11+ Weeks" needs to be entered ""11+ Weeks"" etc.
Two ways to enter code One - break code into pieces with: " & _ rngcell.Formula.Formula = "=IF(B4<= TODAY()-77,""11+ Weeks""," & _ "IF(B4<=TODAY()-42,""6 to 10 Weeks""," & _ "IF(B4<=TODAY()-7,""1 to 5 Weeks""," & _ "IF(B4TODAY()-7,""Current Week""))))" Two - enter the code all together on one line of code (messy for printout) (it wraps in the email but enter the entire code on one line in VB) rngcell.Formula.Formula = "=IF(B4<= TODAY()-77,""11+ Weeks"",IF(B4<=TODAY()-42,""6 to 10 Weeks"",IF(B4<=TODAY()-7,""1 to 5 Weeks"",IF(B4TODAY()-7,""Current Week""))))" Jim K "Walter" wrote: For some reason my VBA editor does not like the formula line. It stays red: rngcell.Formula = "=IF(B4 <= TODAY()-77,"11+ Weeks",IF(B4 <=TODAY()-42, _ "6 to 10 Weeks",IF(B4 <= TODAY()-7,"1 to 5 Weeks",IF(B4 TODAY()-7, _ "Current Week"))))" "Breakfast Guy" wrote: TRY THIS, THIS WILL PUT THAT FORMULA IN EVERY CELL FROM E5 TO THE LAST USED ROW IN COLUMN E: 'Paste formula in each cell in Column E from E5 down to bottom of new range For Each rngCell In range("E5:E" & Range("E" & rows.count).end(xlup).Row) rngcell.Formula = "=IF(B4 <= TODAY()-77,"11+ Weeks",IF(B4 <= TODAY()-42,"6 to 10 Weeks",IF(B4 <= TODAY()-7,"1 to 5 Weeks",IF(B4 TODAY()-7,"Current Week"))))" Next rngCell End Sub *Is this what you wanted?* -- Breakfast Guy ------------------------------------------------------------------------ Breakfast Guy's Profile: http://www.thecodecage.com/forumz/member.php?userid=5 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6849 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range & Fill Column with Formula - Help
Correction - rngcell.Formula.Formula in first post should be rngcell.Formula
One - break code into pieces with: " & _ rngcell.Formula = "=IF(B4<= TODAY()-77,""11+ Weeks""," & _ "IF(B4<=TODAY()-42,""6 to 10 Weeks""," & _ "IF(B4<=TODAY()-7,""1 to 5 Weeks""," & _ "IF(B4TODAY()-7,""Current Week""))))" Two - enter the code all together on one line of code (messy for printout) (it wraps in the email but enter the entire code on one line in VB) rngcell.Formula = "=IF(B4<= TODAY()-77,""11+ Weeks"",IF(B4<=TODAY()-42,""6 to 10 Weeks"",IF(B4<=TODAY()-7,""1 to 5 Weeks"",IF(B4TODAY()-7,""Current Week""))))" Jim K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Resize Chart to fit picture fill Excel 2007 | Charts and Charting in Excel | |||
how do I resize a range of cells in a column in excel? | Excel Discussion (Misc queries) | |||
Resize Last Row to Fill a Page Width | Excel Discussion (Misc queries) | |||
Getting Range to move 1 column left and resize by 1 | Excel Programming | |||
How to fill range with formula using VBA? | Excel Programming |