Looping through cells that are blank but have formula ?

[JumboCactuar]

Hi,
having trouble with this one

basically i have a column (J) of cells with the following formula

=IFERROR(INDEX(B2:$B$10000,MATCH(I2,F2:$F$10000,0)),"")

and i want to write values into all the cells that are blank

something like:

Sub test()
Dim r As Range
For Each r In Range("J2:J10000")
    If r = "" and r.Offset(0, -1).value < 10 then
    r = r.Offset(0, -1).value + 10
    End If
Next
End Sub

ive tried
if r = 0
if len(r) = 0
if isblank(r)

no luck

any help appreciated

 

[Fluff]

Do you need to keep the formula in the cells that returns a value?

 

[Fluff]

@mopp1
That will destroy the formulae & the OP may need to keep them.

 

[mopp1]

Good point.

 

[JumboCactuar]

Do you need to keep the formula in the cells that returns a value?

Yes I need the formulas kept

I recall a way of doing it with filtering but can't remember how

 

[Fluff]

If you're happy getting rid of the iferror so that the cells return #N/A, you could use this

Sub test()
Dim r As Range
For Each r In Range("J2:J10000").SpecialCells(xlFormulas, xlErrors)
    If r.Offset(0, -1).Value < 10 Then
    r = r.Offset(0, -1).Value + 10
    End If
Next
End Sub

 

[JumboCactuar]

Thanks Fluff, though I think to keep the errors hidden.

I have another criteria for the loop, which not sure if could speed things up.

Only values (S2:S50) which are found in column C (C2:C10000)

Maybe create a dictionary of the S2:S50 values

 

[Fluff]

I don't see what connection there is between cols S & C and Col J

 

[JumboCactuar]

I don't see what connection there is between cols S & C and Col J

i changed columns to make it easier to understand, here is my code currently:

Sub test()
Dim r As Range
For Each r In Range("J2:J10000")
    If r.Value = 0 And r.Offset(0, -1).Value > 0 And r.Offset(0, -1).Value < 10 Then
    r = r.Offset(0, -1).Value + 10
    End If
Next
End Sub

Which outputs like:

however i only want the loop to affect names found in the column (M)

so output should be like:

thanks for any help

 

[Leith Ross]

Hello Jumbo,

Since you are already using formulas, why not use a formula in column "J"?

I copied this into J2 and dragged it down. It would be easier to use a dynamic named range for the cells in column "M". It would allow you to edit column "M" without needing to update the formulas.

=IF(ISERROR(MATCH($H2,$M$2:$M$4,0)),"",IF(AND($I2>0,$I2<10),$I2+10,""))

 

[JumboCactuar]

Hello Jumbo,

Since you are already using formulas, why not use a formula in column "J"?

I copied this into J2 and dragged it down. It would be easier to use a dynamic named range for the cells in column "M". It would allow you to edit column "M" without needing to update the formulas.

=IF(ISERROR(MATCH($H2,$M$2:$M$4,0)),"",IF(AND($I2>0,$I2<10),$I2+10,""))

Thanks for the reply though this isn't an option with my sheet.
I need to keep the existing formulas but be able to manually write the values to the cells with the loop.