Automatically Extend Formula

[bobsell]

Hi,

I am quite new to the forum and VB so I really need your help.

Basically I have:

=VLOOKUP($B$28,' Source Volume'!$C$2:$P$500,5,0)

And I need to update it to:

=IF(VLOOKUP($B$28,' Source Volume'!$C$2:$P$500,5,0)<=0, #N/A, VLOOKUP($B$28,' Source Volume'!$C$2:$P$500,5,0))

For every cell in a really large table. I wrote this:

Sub AddTextToCell()

'Add an extra formula code to an existing frmula
'-----------------------------------------------

'Copies the initial formula
Vcell = ActiveCell.Formula
'Removes the = before the formula
Vcell = Right(Vcell, Len(Vcell) - 1)
'Adds the parts for the new formula
Vcell = "=IF(" & Vcell & "<=0, #N/A, " & Vcell & ")"
'Puts the new formula in the cell
ActiveCell.Formula = Vcell

End Sub

But I can not make it loop for each cell. PLEASE HELP!!!!

 

[Weaver]

Welcome to the board

If you're happy that all the cells in the given range have formulas you want to update, this ought to work

Sub amendformula()
    df = "=if(xxx<0,na(),xxx)"
    For Each c In Range("B1:B10")
        f = Mid(c.Formula, 2, 255)
        c.Formula = Replace(df, "xxx", f)
    Next
End Sub

Change "B1:B10" to suit

HTH

 

[bobsell]

It works perfectly. Thank you very much for the INSANELY QUICK response!!

On another note, do you have any recommended books I can read about learning VBA and macros for Excel? I have one called "VBA and Macros: Microsoft Excel 2010" by Que; 1 edition (June, 2010), ISBN-10: 0789743140.

Cheers,
Bobsell

 

[Weaver]

It works perfectly. Thank you very much for the INSANELY QUICK response!!

On another note, do you have any recommended books I can read about learning VBA and macros for Excel? I have one called "VBA and Macros: Microsoft Excel 2010" by Que; 1 edition (June, 2010), ISBN-10: 0789743140.

Cheers,
Bobsell

My response to this is usually the same and is based on my own experience. Books are useful for reference, but I've never managed to read one in chapter order. I learned by trial and error, by spending (way too much) time on this site and by typing questions into google. You would be amazed at how much you can pick up simply by trying to answer other peoples queries then comparing your own responses to those of the real experts!