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"

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"

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"

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"

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"

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"