VBA Macro to replace part of formula in range of cells

[andycapp28]

I am unable to understand why in my macro I can use the range from input boxes successfully to pasteall or pastevalues. However I have tried several ways to use the outputcells variable in my code to have each cell replace part of the formula they contain. Testing for !F and replace with !E.

Apologies for the code holding my many attempts commented out.

Hope someone can help me out.

Thanks in advance

Sub byMonth()
'
' byMonth Macro
' Macro recorded 28/02/2010 by Terry
'
' Keyboard Shortcut: Ctrl+b

Dim InputCells As Excel.Range
Dim OutputCells As Excel.Range
Dim First As Cell
Dim Last As Cell
On Error Resume Next

'Show input box to get range of cells that want to copy
Set InputCells = _
Application.InputBox(Prompt:="Block input cells/range", _
Title:="Copy Paste", Type:=8)

'Show input box to get where they want it paste
Set OutputCells = _
Application.InputBox(Prompt:="Block output cells/range", _
Title:="Copy Paste", Type:=8)

'Copy range of input cells
InputCells.Copy

'Paste it into output cells reference
OutputCells.PasteSpecial (xlPasteAll)

'Paste it into output cells reference
InputCells.PasteSpecial (xlPasteValues)

' ActiveSheet.Range("OutputCells").Select

' Selection.Find What:="!F", Replacement:="!E", LookAt:=xlFormulas, _
SearchOrder:=xlByRows, MatchCase:=False

Range("bvM5:M31").Select
Range("OUTPUTCELLS").End(xlDown).Select
Set First = Selection.Cell
Range("OUTPUTCELLS").End(xlUp).Select
Set Last = Selection.Cell
' Cells.Find What:=phrase, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False

Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Replace What:="!F", Replacement:="!E", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False


End Sub

 

[SkipVought]

Hi,

Range("OUTPUTCELLS").CurrentRegion.SpecialCells(xlCellTypeFormulas, 23).Replace What:="!F", Replacement:="!E", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Select is TOTALLY unnecessary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What is this supposed to be?

    Range("[red][b]bv[/b][/red]M5:M31").Select

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Thanks but the results are not replacing !F

as for Range("bvM5:M31").Select

it was a typo after I dispensed with hard coding and commented out

 

[SkipVought]

Odd that my test works.

Is it the formulas in your OUTPUTCELLS range?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

The range of outputcells are pasted from my inputcells range(holding !E formulas), hence the pasted column then become !F formulas, hence my need to replace.

The reason for replace is that the results(values) are from an associated SUM elsewhere in the worksheet.

 

[SkipVought]

I care not where they came FROM.

What RANGE do you intend to change?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

outputcells

 

[SkipVought]

please post the code that you are using.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Skip,

I embedded it in my thread or is that the wrong way to do it?
I do not have the macro with me I ran last evening. In the office today I have recreated it from my original thread so here it is as I have just run it today. It fails to replace !F with !E.

If you are fed up with trying to help I will understand.

regards
Acapp


Sub byMonth()

Dim InputCells As Excel.Range
Dim OutputCells As Excel.Range

On Error Resume Next

'Show input box to get range of cells that want copying

Set InputCells = Application.InputBox(Prompt:="Block input cells/range", Title:="Copy Paste", Type:=8)

'Show input box to get where they want pasteing

Set OutputCells = Application.InputBox(Prompt:="Block output cells/range", Title:="Copy Paste", Type:=8)

'Copy from variable InputCells'
InputCells.Copy

'Paste it into variable OutputCells
OutputCells.PasteSpecial (xlPasteAll)

'Clear variable InputCells to values only
InputCells.PasteSpecial (xlPasteValues)

' Find !F and replace with !E in formula in OutputCells

Range("OutputCells").CurrentRegion.SpecialCells(xlCellTypeFormulas, 23).Replace What:="!F", Replacement:="!E", LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False

 

[PHV]

I'd use LookIn:=xlFormulas

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[andycapp28]

Thanks but I tried that before posting the thread.

I've tried it again but with this code it gives a compilation error.

 

[PHV]

OOps, sorry, lookin isn't a named parameter of this method :~/

 

[Gavona]

I think that Range("OutputCells").CurrentRegion will return the currentregion for the top let cell in OutputCells. So if you have blank rows/columns in Outputcells the results may not be as intended.

Add a watch for Range("OutputCells").CurrentRegion.Address to explore what is happening.

Gavin

 

[SkipVought]

hence, whagt I posted above...

Range("OUTPUTCELLS")[b].CurrentRegion[/b].SpecialCells(xlCellTypeFormulas, 23).Replace What:="!F", Replacement:="!E", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Sorry Skip, I don't see that.
If OutputCells is A1 to Z1
but C1 is totally blank

Then Range("OutputCells")CurrentRegion will be A1 to B1

So no replacements will be made in D1 to Z1

Am I missing something?

Gavin

 

[andycapp28]

Thanks for all your help.

I decided to back to basics and clear down code and start again.

All sorted now.