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

 

[Johnny Thunder]

Why not just change your if Error statement to return a unique string so you can find it that way. So change the "" at the end of your formula to "No Value" and then modify the VBA to If R= "No Value"

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

 

[JumboCactuar]

Why not just change your if Error statement to return a unique string so you can find it that way. So change the "" at the end of your formula to "No Value" and then modify the VBA to If R= "No Value"

Could do that or even use an helper column, just thought there would be a simple way to do this without modifying my Sheets

If it helps, without the IFERROR , "" , the cell would display #NA

 

[JumboCactuar]

Bump

 

[mopp1]

use r.value=""

 

[JumboCactuar]

use r.value=""

Tried and it does nothing

 

[Fluff]

Your code should work.
In an unused cell put
=LEN(J2)
& point it at one of the "Blank" cells in col J, what does it return?

 

[mopp1]

Tried and it does nothing

also r.value=r.offsett...

 

[JumboCactuar]

Thanks all

I ended up modifying my formula to output zero instead of ""

Then custom formatting to hide the zeros

It now works though currently the loop is very slow
I can probably speed it up by disabling screen updating / calcs

Though is there a better way to do the loop?

 

[mopp1]

Copy the range into an array like: RngArray= Range("J2:J10000").value

That creates a twodimensional array RngArray(1,1)= J2, RngArray(2,1)=J3 etc
Do your loop on the array.
When finished write the array back to the range: Range("J2:J10000").value=RngArray