Vba code for copy paste loop one cell each time

[rajiv_bishnoi]

Hi

Will appreciate any help in this

I have one sheet where in one range (say D23: D200) computes the cash flow ( which could be negative or positive ) . I need to do this
1. Read the value in cell D 23 and if its negative then
2. Copy it
3. Do a paste special in cell F 23 with change of sign
4. Recalculate the entire sheet ( if it does not happen automatically)

Loop to next cell E 23

Do the same procedure ( 1,2,3 and 4 ) until the last cell D200 or could be also set until the cell is blank.

 

[JoeMo]

and the data from column E goes to column G?

 

[rajiv_bishnoi]

and the data from column E goes to column G?

Thank you for pointing out error in my post. The original data is in a row , not a column ( say d23: bq 23) . Pasting data in same column, couple of rows below. Correcting the post now .

 

[rajiv_bishnoi]

Can't seem to find an edit button . Please note that original data is in a row ( say D23 : BM23) . Pasting data after couple of rows in same column D 25 , before looping to E23 . Sorry for inconvenience

 

[JoeMo]

Can't seem to find an edit button . Please note that original data is in a row ( say D23 : BM23) . Pasting data after couple of rows in same column D 25 , before looping to E23 . Sorry for inconvenience

Try this:

Sub NegativeToPositive()
Dim lC As Long, R As Range
lC = Cells(23, Columns.Count).End(xlToLeft).Column
Set R = Range(Cells(23, "D"), Cells(23, lC))
With R.Offset(2, 0)
    .FormulaR1C1 = "=IF(R[-2]C<0,R[-2]C,"""")"
    ActiveSheet.Calculate
    .Value = .Value
End With
End Sub

 

[rajiv_bishnoi]

Try this:

Sub NegativeToPositive()
Dim lC As Long, R As Range
lC = Cells(23, Columns.Count).End(xlToLeft).Column
Set R = Range(Cells(23, "D"), Cells(23, lC))
With R.Offset(2, 0)
    .FormulaR1C1 = "=IF(R[-2]C<0,R[-2]C,"""")"
    ActiveSheet.Calculate
    .Value = .Value
End With
End Sub

Dear Joe,

Thank you for your time and help, but it is not working as desired. The value computed are not correct and it is not looping to next cells. I wish there was a way to upload a simple sheet, but couldn't find one. Attaching a link at googledocs, wherein I had uploaded a sample sheet with your code. Pressing button runs the macro but as you can see the results are not the same as I want ( created a manual punch of desired result which I want in row 25 through running a macro). Many thanks for your help
https://docs.google.com/file/d/0B8GzDQsJwFD1ZVNiXzBaU0NWR1E/edit?usp=sharing

 

[rajiv_bishnoi]

Aha... the pleasure of working out a problem...

Thanks for your initial help Joe, but after searching around and several trial and errors, I think I was able to develop the code which works as I intend it to

**
Sub NegativeToPositive()





Dim lC As Long, R As Range, Q As Range, Cl As Range


lC = Cells(23, Columns.Count).End(xlToLeft).Column

Set R = Range(Cells(23, "D"), Cells(23, lC))

Set Q = Range(Cells(25, "D"), Cells(25, lC))


Q.Value = 0



' Disable automatic calculation
Application.Calculation = xlCalculationManual
For Each Cl In R



With Cl.Offset(2, 0)

.FormulaR1C1 = "=-R[-2]C"
.Value = .Value

End With

' Force a calculation
Application.Calculate


Next Cl


' Then remember to run automatic calculations back on
Application.Calculation = xlCalculationAutomatic


End Sub
**