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

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

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

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

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

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