Selecting Cells With Sumproduct Formula, Then Pasting Its Value Over Top
 

Hello,

In testing, I tried the formula below without success. Basically, I
was trying to select only those cells within a range that contained the
function SUMPRODUCT. After selecting all instances where this is true,
I want to copy/paste the actual value of the result of that formula
over top of the formula. I have other formulas in my spreadsheet that
do not use SUMPRODUCT, but I don't want to touch those.

It appears that ActiveCell.Value is the value that the function
returns, not the formula itself, so I know that this doesn't help me,
but I couldn't find another property for ActiveCell that would make it
work.

Ultimately, I want to include multiple sheets in my search, so if you
know code where I can do it all in one fell swoop, that would be great.

Any ideas out there?

Sub Test()
Dim SearchString, SearchSubstr, MyPos
Worksheets("Receipts-Initial").Activate
SearchSubstr = "SUMPRODUCT"
For Each Cell In Worksheets("Receipts-Initial").Range("A16:Q16")
SearchString = ActiveCell.Value
MyPos = InStr(1, SearchString, SearchSubstr, 1)
If MyPos < 0 Then
ActiveCell.Select
End If
Next
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Thanks,
Carroll Rinehart

Selecting Cells With Sumproduct Formula, Then Pasting Its Value Over Top
 

Sub Test()
Dim SearchString, SearchSubstr, MyPos
Worksheets("Receipts-Initial").Activate
SearchSubstr = "SUMPRODUCT"
For Each Cell In Worksheets("Receipts-Initial").Range("A16:Q16")
SearchString = ActiveCell.Formula
MyPos = InStr(1, SearchString, SearchSubstr, vbTextCompare)
If MyPos < 0 Then
ActiveCell.Formula = ActiveCell.Value
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Carroll" wrote in message
oups.com...
Hello,

In testing, I tried the formula below without success. Basically, I
was trying to select only those cells within a range that contained the
function SUMPRODUCT. After selecting all instances where this is true,
I want to copy/paste the actual value of the result of that formula
over top of the formula. I have other formulas in my spreadsheet that
do not use SUMPRODUCT, but I don't want to touch those.

It appears that ActiveCell.Value is the value that the function
returns, not the formula itself, so I know that this doesn't help me,
but I couldn't find another property for ActiveCell that would make it
work.

Ultimately, I want to include multiple sheets in my search, so if you
know code where I can do it all in one fell swoop, that would be great.

Any ideas out there?

Sub Test()
Dim SearchString, SearchSubstr, MyPos
Worksheets("Receipts-Initial").Activate
SearchSubstr = "SUMPRODUCT"
For Each Cell In Worksheets("Receipts-Initial").Range("A16:Q16")
SearchString = ActiveCell.Value
MyPos = InStr(1, SearchString, SearchSubstr, 1)
If MyPos < 0 Then
ActiveCell.Select
End If
Next
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Thanks,
Carroll Rinehart

Selecting Cells With Sumproduct Formula, Then Pasting Its Value Over Top
 

Tom,

I'm finding that the SearchString ends up being the value that the
function returns rather than the function itself, so MyPos is always
equal to 0, so it ends up doing nothing.

Carroll

Selecting Cells With Sumproduct Formula, Then Pasting Its Value Over Top
 

Also, I notice with a Debug.Print ActiveCell.Address thrown into the
for..next loop, that the active cell actually never changes, so I'm
guessing that this approach won't work.

Carroll

Selecting Cells With Sumproduct Formula, Then Pasting Its Value Over Top
 

I figured out what was wrong. Instead of saying ActiveCell.Formula and
ActiveCell.Value, I should be using Cell.Formula and Cell.Value. I
guess when you're in a For Each...Next loop, ActiveCell never changes,
but you have to reference what cell it's on in the range by using just
the word "Cell".

Thanks Tom for giving me some guidance here.

Selecting Cells With Sumproduct Formula, Then Pasting Its Value Over Top
 

I had not really looked at your loop, but focused on the specifics of your
question. You are correct that the active cell would not change unless you
did

Sub Test()
Dim SearchString, SearchSubstr, MyPos
Worksheets("Receipts-Initial").Activate
SearchSubstr = "SUMPRODUCT"
For Each Cell In Worksheets("Receipts-Initial").Range("A16:Q16")
cell.Activate
SearchString = ActiveCell.Formula
MyPos = InStr(1, SearchString, SearchSubstr, vbTextCompare)
If MyPos < 0 Then
ActiveCell.Formula = ActiveCell.Value
End If
Next
End Sub

but not activating the cell is the preferred method so If I had looked at
your code a little more closely, I would have suggested

Sub Test()
Dim SearchSubstr as String, Cell as Range
SearchSubstr = "SUMPRODUCT"
For Each Cell In Worksheets("Receipts-Initial").Range("A16:Q16")
If InStr(1, cell.formula, SearchSubstr, vbTextCompare) then
Cell.Formula = Cell.Value
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Carroll" wrote in message
oups.com...
I figured out what was wrong. Instead of saying ActiveCell.Formula and
ActiveCell.Value, I should be using Cell.Formula and Cell.Value. I
guess when you're in a For Each...Next loop, ActiveCell never changes,
but you have to reference what cell it's on in the range by using just
the word "Cell".

Thanks Tom for giving me some guidance here.