Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub won't fill down via code
Hi, I have a sub that inserts the fill color of A1 into B2. The code is
supposed to copy the formula down but it doesn't although the fill down code works all other times. I can manually pull the fill down but don't want my end users to have to do that. Here is the code. Anyone see anything I don't? Thanks. Function GetFillColor(rng As Range) As Long GetFillColor = rng.Interior.ColorIndex End Function Sub GetFillNEW() Application.ScreenUpdating = False Application.StatusBar = "Now processing" Application.DisplayAlerts = False Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "=GetFillColor(RC[-1])" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B1705"), Type:=xlFillDefault Application.ScreenUpdating = True Application.StatusBar = "Ready" Application.DisplayAlerts = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub won't fill down via code
Add this at the end of your code:
Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "SLP" wrote: Hi, I have a sub that inserts the fill color of A1 into B2. The code is supposed to copy the formula down but it doesn't although the fill down code works all other times. I can manually pull the fill down but don't want my end users to have to do that. Here is the code. Anyone see anything I don't? Thanks. Function GetFillColor(rng As Range) As Long GetFillColor = rng.Interior.ColorIndex End Function Sub GetFillNEW() Application.ScreenUpdating = False Application.StatusBar = "Now processing" Application.DisplayAlerts = False Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "=GetFillColor(RC[-1])" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B1705"), Type:=xlFillDefault Application.ScreenUpdating = True Application.StatusBar = "Ready" Application.DisplayAlerts = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub won't fill down via code
My bad, I checked the code and it tries to recalculate the formula each time
therefore just replace your code for this. I did test it and it works.Function GetFillColor(rng As Range) As Long GetFillColor = rng.Interior.ColorIndex End Function Sub GetFillNEW() Application.ScreenUpdating = False Application.StatusBar = "Now processing" Application.DisplayAlerts = False Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1:B1705").FormulaR1C1 = "=GetFillColor(RC[-1])" Application.ScreenUpdating = True Application.StatusBar = "Ready" Application.DisplayAlerts = True End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "SLP" wrote: Hi, I have a sub that inserts the fill color of A1 into B2. The code is supposed to copy the formula down but it doesn't although the fill down code works all other times. I can manually pull the fill down but don't want my end users to have to do that. Here is the code. Anyone see anything I don't? Thanks. Function GetFillColor(rng As Range) As Long GetFillColor = rng.Interior.ColorIndex End Function Sub GetFillNEW() Application.ScreenUpdating = False Application.StatusBar = "Now processing" Application.DisplayAlerts = False Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "=GetFillColor(RC[-1])" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B1705"), Type:=xlFillDefault Application.ScreenUpdating = True Application.StatusBar = "Ready" Application.DisplayAlerts = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub won't fill down via code
Thank you very much. I'll have happy end users now. Enjoy the weekend.
"Michael" wrote: My bad, I checked the code and it tries to recalculate the formula each time therefore just replace your code for this. I did test it and it works.Function GetFillColor(rng As Range) As Long GetFillColor = rng.Interior.ColorIndex End Function Sub GetFillNEW() Application.ScreenUpdating = False Application.StatusBar = "Now processing" Application.DisplayAlerts = False Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1:B1705").FormulaR1C1 = "=GetFillColor(RC[-1])" Application.ScreenUpdating = True Application.StatusBar = "Ready" Application.DisplayAlerts = True End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "SLP" wrote: Hi, I have a sub that inserts the fill color of A1 into B2. The code is supposed to copy the formula down but it doesn't although the fill down code works all other times. I can manually pull the fill down but don't want my end users to have to do that. Here is the code. Anyone see anything I don't? Thanks. Function GetFillColor(rng As Range) As Long GetFillColor = rng.Interior.ColorIndex End Function Sub GetFillNEW() Application.ScreenUpdating = False Application.StatusBar = "Now processing" Application.DisplayAlerts = False Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "=GetFillColor(RC[-1])" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B1705"), Type:=xlFillDefault Application.ScreenUpdating = True Application.StatusBar = "Ready" Application.DisplayAlerts = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to fill down | Excel Discussion (Misc queries) | |||
auto fill code | Excel Discussion (Misc queries) | |||
Fill down - adjust code | Excel Programming | |||
List Fill Code | Excel Programming | |||
find & delete + fill to end code? | Excel Programming |