Find & Replace Loop

[kod2th3e]

I have the following formula in column B of a worksheet:

=IF(INDIRECT("Programado!B4")="","",INDIRECT("Programado!B4"))

The formula's purpose is only to display what text/value is found in cell B4 on the Programado worksheet of my workbook. Because this is an indirect formula, when I fill it down several rows the cell reference doesn't increment. So all cells that are filled with this formula reference B4 of the Programado worksheet instead of increasing to B5, B6, B7, etc. Does anyone now how I can loop through all the cells in the column that this formula is located and replace the 4?

For example:

dim i as integer
    i = 5
dim c as range

for each c in selection
    .find(what:="4",replacement:=i)
    i = i +1
next

I know this doesn't work and is missing a lot of other things, but I was looking for something to that effect. I hope this isn't too vague, I appreciate any help that anyone will be able to provide, thanks.

-Cody

 

[hurgadion]

Hi,
I think that Your Formula (it is only my suggestion):

=IF(INDIRECT("Programado!B4")="","",INDIRECT("Programado!B4"))

is equivalent to a Formula

=Programado!B4

Best regards.

 

[davesweep]

Hi,

It's possibly easier to re-enter the formula if you're using code.

Sub indirection()
Dim iCounter As Integer
Dim rCell As Range

iCounter = 4

For Each rCell In Range("A1:A100") 'adjust as required
    rCell.FormulaR1C1 = "=IF(INDIRECT(""Programado!B" & iCounter & """)="""","""",INDIRECT(""Programado!B" & iCounter & """))"
    iCounter = iCounter + 1
Next rCell

End Sub

Note that INDIRECT is a volatile function, and as such will slow your sheet significantly if used to excess.

 

[kod2th3e]

That is what I was originally using but the reason why I switched to the indirect formula is because the data on the programado sheet gets cleared out and changed and when that happens the cell reference gets jacked up.

 

[kod2th3e]

This works great, thanks for the help!

Hi,

It's possibly easier to re-enter the formula if you're using code.

Sub indirection()
Dim iCounter As Integer
Dim rCell As Range

iCounter = 4

For Each rCell In Range("A1:A100") 'adjust as required
    rCell.FormulaR1C1 = "=IF(INDIRECT(""Programado!B" & iCounter & """)="""","""",INDIRECT(""Programado!B" & iCounter & """))"
    iCounter = iCounter + 1
Next rCell

End Sub

Note that INDIRECT is a volatile function, and as such will slow your sheet significantly if used to excess.