VBA macro to replace part of a formula

[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 replacing with !E.

Apologies for the code holding my many attempts commented out.

Hope someone can help me out.

Thanks in advance

code as follows;


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

 

[Scott Huish]

I don't quite understand what your question is. Your first statement says that you were successful in what you were trying to accomplish.
What is your question? What are you trying to accomplish?

Also, it is very rare that you need to Select, you can work with ranges directly in VBA.

I also don't understand your dimming variables as Cell, they should be dimmed as Range.

 

[Andycapp28]

Hotpepper, hi, thanks

My issue is this, my variable OutputCells (range) is not receiving formulas that exist in my variable InputCells (range). Watching local I can see InputCells contains formulas. I need OutputCells to get these and then my macro to do two things;
1st replace InputCells with values only
2nd replace OutputCells formula from !F to !E.

 

[Norie]

Like Hotpeper I don't quite understand the question or what you want to do.

One thing I do see is that you appear to be trying to use your variables as named range.

For example here:

Range("OUTPUTCELLS")...

VBA will be looking for a named range called 'OUTPUTCELLS'.

Another thing I would recommend is that you remove On Error Resume Next.

That could just be hiding errors that are causing the problem.

 

[Andycapp28]

Thanks for the advice about removing On error.

It produced this[FONT=&quot]
Run time error 1004

Method 'Range' of object'_Global' failed

so what now?


<!--[endif]-->[/FONT]

 

[Scott Huish]

On what line? Did you dim your variables as Range instead of Cell?

What are you trying to accomplish?

 

[Norie]

Well that error is most likely produced by what I mentioned previously regarding named ranges.

Do you have named ranges?

I've got a feeling you don't.

By the way is the code you posted really a recorded macro - I'd be surprised if it is because I've never seen recorded code that had variable declarations.

 

[Scott Huish]

You have told us what your issues are but not what you are trying to accomplish. I don't think you are writing code just to write code, there must be something you want to happen in your spreadsheet? That's what we are asking when we ask what are you trying to accomplish. We are not asking what your problem is, we are asking what you are trying to do.

But in general, I guess your looking for at least this.

If you define a variable as a range object then you treat it just like a range. It's not generally necessary to Select, this is just an example of how to use range object variables.

Dim a as Range
Set a=Range("A1")
a.Select

 

[Andycapp28]

Thanks everyone.

I took the decision to scrap previous code and start again.

All sorted now.