Copy and paste after looping

[viomman]

Yep another noob question
I am attempting to loop through a range to test for a value and then copy and paste another range to effectively "Reset the formulas each time there is a value this is the code but I am not getting what i want


Public Sub ResetSheet()
Dim ResetRange As Range
Dim lRow As Long
Dim lColumn As Long
Application.ScreenUpdating = False
On Error Resume Next
Set ResetRange = Sheets("LCCACalculations").Range("F7:CZ95")
ResetRange.Copy
For lRow = 10 To 10
Do

For lColumn = 6 To 105

If ActiveCell > 0 Then
ActiveCell.Offset(-3, 1).Select
' ActiveCell.Paste

ApplicationCutCopyMode = False

End If
Next lColumn
Loop

Next lRow



Application.ScreenUpdating = True
ApplicationCutCopyMode = False
End Sub



I have to reset all of my formulas every time there is a value in the tested cells next to the new value
can someone help me out with this?

[mdmackillop]

Can you make up a sample workbook showing where the formulae are and to where they should be pasted (we don't need the real formulae)

[viomman]

Ok here is a dirty example of my workbook.
Ok in my example i have to loop through when the sheet changes and the sheet will change by its self so i will run this macro on
Private Sub Worksheet_Change(ByVal Target As Range)
'Run "ResetSheet"

End Sub
i have it remed out right now
to continue
i need to test througu row 10 all of the cell values if it is greater than 0 then copy all of the formulas from f7 to p 78 and paste by offseting (-3 ,1) from the cell that tested true. It also needs to continue on down the row to test the entire row for a reset

[GTO]

Greetings Viomman,

Speaking only for myself, it remains unclear as to what exactly we are trying to do. While a bit of a 'stab in the dark', it seems that you are maybe trying to readjust formulas if/after a priorly entered formula in row 10 is overwritten.

Is that anywhere in the ballpark?

Also - am I correct in presuming that Sheet2 in the example wb supposed to be named "LCCACalculations"?

In no certain order, I would suggest first unmerging the cells in row 23 (and any other merged cells if I missed any) as these don't appear to be needed, and will jam you on the paste I believe.

I would also suggest ridding yourself of the 'On Error Resume Next' in your code, because it is masking problems; thus preventing you from seeing where stuff is going awry. For instance: ApplicationCutCopyMode = False should be Application.CutCopyMode (etc) . That little dot missing will stop 'er in her tracks.

Sorry not much help thus far, but I think that with a little clearer picture, better help will be possible :-)

Mark

[viomman]

thank dude. i do have all of those issues worked out in my spread sheet
but this is the direction i am testing now
Public Sub ResetSheet()
Dim ResetRange As Range
Dim StpRng As Range

Application.ScreenUpdating = False
On Error Resume Next
Set ResetRange = Sheets("LCCACalculations").Range("F7:CZ95")
ResetRange.Copy
Sheets("LCCACalculations").Range("f10").Select

Set StpRng = Range("DA10")
Do
If ActiveRange = StpRng Then
Exit Do
End If
If ActiveCell.Value > 0 Then
ActiveCell.Offset(-3, 1).Select
ActiveCell.PasteSpecial
ActiveCell.Offset(3, 0).Select
Else
ActiveCell.Offset(0, 1).Select
End If
Loop



Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
But I am still hitting the wall

[viomman]

Ok that did not work going down another road

Dim rCcells As Range, rLoopCells As Range
Dim ResetRange As Range
Application.ScreenUpdating = False
'Set variable to all used cells
Set rAcells = Sheets("LCCACalculations").Range("F10:CZ10")
Set ResetRange = Sheets("LCCACalculations").Range("F7:CZ95")
rAcells.Select
On Error Resume Next 'In case of no formula or constants.

ResetRange.Copy
'Loop through needed cells only see if negative
For Each rLoopCells In rAcells
If rLoopCells.Value > 0 Then
With rLoopCells
.Offset(-3, 1).Select
.PasteSpecial xlPasteFormulas

End With
End If
Next rLoopCells

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

still not getting what i want
I am think I am going to have to take the long way around

[mdmackillop]

You need to explain in simple steps what you are trying to do. I can't follow your code and your ranges seem to bear no relation to the data on your sheets, which as noted, do not use the name in the code.

[GTO]

Please answer:


...it seems that you are maybe trying to readjust formulas if/after a priorly entered formula in row 10 is overwritten.

Is that anywhere in the ballpark?...


As to:

...am I correct in presuming that Sheet2 in the example wb supposed to be named "LCCACalculations"?

...I would suggest first unmerging the cells in row 23...

...I would also suggest ridding yourself of the 'On Error Resume Next' in your code...

IF:

...i do have all of those issues worked out in my spread sheet...

...means that you altered/corrected the workbook/worksheets, could you post an up-to-date sample with the sheets correctly named and your now current code so maybe we can see what is happening?

Thanks,

Mark