Macro to find and replace formulas with cell value

[lx_lthr2000]

Hi All

Apologies if this has already been asked in the forum, but I couldn't find it from my search.

I'm looking for a macro that will find all the formulas in a workbook (with multiple tabs) that specifically begin with "=HsGetValue" and replace it with the value of that cell - e.g. if a cell has the formula =HSGetValue(......) which gives the value 4, then I want to replace the formula / cell with that value (i.e. a bit like 'past special, value only').

Hope that makes sense. Would be grateful for any help.

Many thanks

 

[sheetspread]

I'll test this right now, but try it (on a copy) meanwhile:

sub specformula2val()
dim x as integer
for x = 1 to sheets.count
for each cell in sheets(x).usedrange
if left(cell.formula,11) = "=HSGetValue" then
cell.value = cell.value
end if
next cell
next x
end sub

 

[lx_lthr2000]

Many thanks for the quick response. Have just tried it and unfortunately it does not seem to do anything....

I'll test this right now, but try it (on a copy) meanwhile:

sub specformula2val()
dim x as integer
for x = 1 to sheets.count
for each cell in sheets(x).usedrange
if left(cell.formula,11) = "=HSGetValue" then
cell.value = cell.value
end if
next cell
next x
end sub

 

[sheetspread]

Is the formula =HSGETVALUE ? The if statement and/or left function seems to be case sensitive.

 

[lx_lthr2000]

Hi

Many apologies for the delayed response, but I was off work due to a personal bereavement.

However, I just wanted to say thank you very much, as having adjusted for the point below, the macro now works perfectly.

My only follow on point if I may is how would I get the macro to not just pick up "=HsGetValue" but also "=-HsGetValue" or "=(HsGetValue"

Is the formula =HSGETVALUE ? The if statement and/or left function seems to be case sensitive.

 

[sheetspread]

Try:

if left(cell.formula,11) = "=HsGetValue" or left(cell.formula,12) = "=-HsGetValue" or left(cell.formula,12) = "=(HSGetValue" then

 

[lx_lthr2000]

Perfect! Many many thanks.

Try:

if left(cell.formula,11) = "=HsGetValue" or left(cell.formula,12) = "=-HsGetValue" or left(cell.formula,12) = "=(HSGetValue" then