VBA excel macro help 1

[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

code tag

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

/code tag

 

[dhookom]

You might try post this question in a more appropriate forum. Perhaps VBA Visual Basic for Applications (Microsoft).

Duane
Hook'D on Access
MS Access MVP