Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore Command in VBA
I am trying to use a macro that uses this same formula for different columns
approximately 50 times- but in some of the columns there will be no constants and I want it to ignore that command if there are no constants. How can I do this? Range("I3:I10").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.FormulaR1C1 = "=R2C9" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore Command in VBA
You need to avoid making the selection of cells that may not exist...
Something like this... dim rngConstants as Range on error resume next set rngConstants = Range("I3:I10").SpecialCells(xlCellTypeConstants, 23) on error goto 0 if not rngConstants is nothing then rngConstants.FormulaR1C1 = "=R2C9" -- HTH... Jim Thomlinson "Katie" wrote: I am trying to use a macro that uses this same formula for different columns approximately 50 times- but in some of the columns there will be no constants and I want it to ignore that command if there are no constants. How can I do this? Range("I3:I10").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.FormulaR1C1 = "=R2C9" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore Command in VBA
Hi Katie,
One way would be like this Sub test() Dim rng As Range On Error Resume Next Set rng = Range("I3:I10").SpecialCells(xlCellTypeConstants, 23) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R2C9" End Sub -- Hope that helps. Vergel Adriano "Katie" wrote: I am trying to use a macro that uses this same formula for different columns approximately 50 times- but in some of the columns there will be no constants and I want it to ignore that command if there are no constants. How can I do this? Range("I3:I10").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.FormulaR1C1 = "=R2C9" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore Command in VBA
These two responses are great. Thank you both.
Now here's my follow-up, instead of having to manually change the code for the next 50 columns, is there some "repeat with column to the right" function? :P Katie "Vergel Adriano" wrote: Hi Katie, One way would be like this Sub test() Dim rng As Range On Error Resume Next Set rng = Range("I3:I10").SpecialCells(xlCellTypeConstants, 23) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R2C9" End Sub -- Hope that helps. Vergel Adriano "Katie" wrote: I am trying to use a macro that uses this same formula for different columns approximately 50 times- but in some of the columns there will be no constants and I want it to ignore that command if there are no constants. How can I do this? Range("I3:I10").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.FormulaR1C1 = "=R2C9" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore Command in VBA
Hi Katie,
If you wanted to apply the formula from coulmn I to column BF (50 columns), try it this way: Sub test() Dim rng As Range On Error Resume Next Set rng = Range("I3:BF10").SpecialCells(xlCellTypeConstants, 23) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R2C" End Sub -- Hope that helps. Vergel Adriano "Katie" wrote: These two responses are great. Thank you both. Now here's my follow-up, instead of having to manually change the code for the next 50 columns, is there some "repeat with column to the right" function? :P Katie "Vergel Adriano" wrote: Hi Katie, One way would be like this Sub test() Dim rng As Range On Error Resume Next Set rng = Range("I3:I10").SpecialCells(xlCellTypeConstants, 23) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R2C9" End Sub -- Hope that helps. Vergel Adriano "Katie" wrote: I am trying to use a macro that uses this same formula for different columns approximately 50 times- but in some of the columns there will be no constants and I want it to ignore that command if there are no constants. How can I do this? Range("I3:I10").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.FormulaR1C1 = "=R2C9" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore Command in VBA
I think I need to buy a book. :-)
This would be awesome- thank you! "Vergel Adriano" wrote: Hi Katie, If you wanted to apply the formula from coulmn I to column BF (50 columns), try it this way: Sub test() Dim rng As Range On Error Resume Next Set rng = Range("I3:BF10").SpecialCells(xlCellTypeConstants, 23) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R2C" End Sub -- Hope that helps. Vergel Adriano "Katie" wrote: These two responses are great. Thank you both. Now here's my follow-up, instead of having to manually change the code for the next 50 columns, is there some "repeat with column to the right" function? :P Katie "Vergel Adriano" wrote: Hi Katie, One way would be like this Sub test() Dim rng As Range On Error Resume Next Set rng = Range("I3:I10").SpecialCells(xlCellTypeConstants, 23) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R2C9" End Sub -- Hope that helps. Vergel Adriano "Katie" wrote: I am trying to use a macro that uses this same formula for different columns approximately 50 times- but in some of the columns there will be no constants and I want it to ignore that command if there are no constants. How can I do this? Range("I3:I10").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.FormulaR1C1 = "=R2C9" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One command in one cell initiating another command in another cel. | Excel Worksheet Functions | |||
command code ( GOTO command) in formula | New Users to Excel | |||
Pivot Table Error Message - "Command Text not set for command obje | Excel Discussion (Misc queries) | |||
Print an excel file from command line or "cron" type command ??? | Excel Programming | |||
Excel to load COMMAND and type a Run Command? | Excel Programming |