| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filldown stops abruptly
A command button fires the following code
Range("AV10").Select Selection.AutoFill Destination:=Range("AV10:AV7800"), Type:=xlFillDefault Range("AV10:AV7800").Select When AV2967 is reached the following formula changes from {=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B2962&"G house")),"G house","")} to: {=#VALUE!} If I manually fill down the formula past where it changes to {=#VALUE!} the formula does not revert to {=#VALUE!} Does anyone know why the formula suddenly should change |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filldown stops abruptly
Is B2962 within the formula an absolute reference or a relative reference
The below format should work when you have a formula in G4. Range("G4").AutoFill Destination:=Range("G4:G24"), Type:=xlFillDefault OR without using autofill you can apply the formula to a range as below. Excel would automatically change the references Range("G4:G24").Formula = "=SUM(A4:F4)" If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: A command button fires the following code Range("AV10").Select Selection.AutoFill Destination:=Range("AV10:AV7800"), Type:=xlFillDefault Range("AV10:AV7800").Select When AV2967 is reached the following formula changes from {=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B2962&"G house")),"G house","")} to: {=#VALUE!} If I manually fill down the formula past where it changes to {=#VALUE!} the formula does not revert to {=#VALUE!} Does anyone know why the formula suddenly should change |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filldown stops abruptly
Hi Jacob Skaria
B2962 is a relative reference I tried out your example Range("G4:G24").Formula = "=SUM(A4:F4)" and it worked great. my formula is an array entered formula and I could not replicate the results I got from your formula Here is my formula Range("L10:L7800").Formula = "=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B10&'G house')),'G house',"")" I am getting an debug error when executed. When i modified it to Range("L10:L7800").Formula = {"=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B10&'G house')),'G house',"")"} it fill the range L10:L7800 but was not received by the range as a formula, more like a text entry, strange! "Jacob Skaria" wrote: Is B2962 within the formula an absolute reference or a relative reference The below format should work when you have a formula in G4. Range("G4").AutoFill Destination:=Range("G4:G24"), Type:=xlFillDefault OR without using autofill you can apply the formula to a range as below. Excel would automatically change the references Range("G4:G24").Formula = "=SUM(A4:F4)" If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: A command button fires the following code Range("AV10").Select Selection.AutoFill Destination:=Range("AV10:AV7800"), Type:=xlFillDefault Range("AV10:AV7800").Select When AV2967 is reached the following formula changes from {=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B2962&"G house")),"G house","")} to: {=#VALUE!} If I manually fill down the formula past where it changes to {=#VALUE!} the formula does not revert to {=#VALUE!} Does anyone know why the formula suddenly should change |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filldown stops abruptly
Try
Range("L10:L7800").FormulaArray = "=yourformula" If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: Hi Jacob Skaria B2962 is a relative reference I tried out your example Range("G4:G24").Formula = "=SUM(A4:F4)" and it worked great. my formula is an array entered formula and I could not replicate the results I got from your formula Here is my formula Range("L10:L7800").Formula = "=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B10&'G house')),'G house',"")" I am getting an debug error when executed. When i modified it to Range("L10:L7800").Formula = {"=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B10&'G house')),'G house',"")"} it fill the range L10:L7800 but was not received by the range as a formula, more like a text entry, strange! "Jacob Skaria" wrote: Is B2962 within the formula an absolute reference or a relative reference The below format should work when you have a formula in G4. Range("G4").AutoFill Destination:=Range("G4:G24"), Type:=xlFillDefault OR without using autofill you can apply the formula to a range as below. Excel would automatically change the references Range("G4:G24").Formula = "=SUM(A4:F4)" If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: A command button fires the following code Range("AV10").Select Selection.AutoFill Destination:=Range("AV10:AV7800"), Type:=xlFillDefault Range("AV10:AV7800").Select When AV2967 is reached the following formula changes from {=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B2962&"G house")),"G house","")} to: {=#VALUE!} If I manually fill down the formula past where it changes to {=#VALUE!} the formula does not revert to {=#VALUE!} Does anyone know why the formula suddenly should change |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filldown stops abruptly
I am getting an error, here is the formula I am using Range("L10:L7800").FormulaArray = "=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B10&"G house")),"G house","")" B10 is a relative reference. Thank you for helping me out. "Jacob Skaria" wrote: Try Range("L10:L7800").FormulaArray = "=yourformula" If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: Hi Jacob Skaria B2962 is a relative reference I tried out your example Range("G4:G24").Formula = "=SUM(A4:F4)" and it worked great. my formula is an array entered formula and I could not replicate the results I got from your formula Here is my formula Range("L10:L7800").Formula = "=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B10&'G house')),'G house',"")" I am getting an debug error when executed. When i modified it to Range("L10:L7800").Formula = {"=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B10&'G house')),'G house',"")"} it fill the range L10:L7800 but was not received by the range as a formula, more like a text entry, strange! "Jacob Skaria" wrote: Is B2962 within the formula an absolute reference or a relative reference The below format should work when you have a formula in G4. Range("G4").AutoFill Destination:=Range("G4:G24"), Type:=xlFillDefault OR without using autofill you can apply the formula to a range as below. Excel would automatically change the references Range("G4:G24").Formula = "=SUM(A4:F4)" If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: A command button fires the following code Range("AV10").Select Selection.AutoFill Destination:=Range("AV10:AV7800"), Type:=xlFillDefault Range("AV10:AV7800").Select When AV2967 is reached the following formula changes from {=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B2962&"G house")),"G house","")} to: {=#VALUE!} If I manually fill down the formula past where it changes to {=#VALUE!} the formula does not revert to {=#VALUE!} Does anyone know why the formula suddenly should change |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Prevent filldown | Excel Discussion (Misc queries) | |||
| Macro filldown | Excel Worksheet Functions | |||
| FillDown Macro | Excel Worksheet Functions | |||
| Macro Filldown | Excel Worksheet Functions | |||
| Formula abruptly changed | Excel Worksheet Functions | |||
Hybrid Mode
