Copying/ Transferring Data from cell to cell

[elaine6985]

Good Day!

I would like to ask if it is possible to transfer data from cell to another cell?
See sample below:

BEFORE:

A B C
1 PAID UNPAID REMARKS

2 100 NOT

AFTER:

A B C
1 PAID UNPAID REMARKS

2 100 PAID

*What i mean is that the data in UNPAID will be transferred to PAID once the remarks is Paid.
Thank you very much in advance...

-Elaine

 

[sakthikumar]

try this!!! =sumif(c1:c7"=",B1:B7) IN THIS IF YOU GIVE THE AMOUNT IN ANY ROW OF B IT WILL REFLECT IN C ALSO !!!!!

 

[elaine6985]

"actually there are spaces, 600 entries exclusive of spaces. Will this run even if i have blank spaces?

 

[shadow12345]

needs a slight change otherwise it might miss the last few rows.... just use this one (max 5000 rows including spaces but you can increase if you want to.)

Sub Marine()
Application.ScreenUpdating = False
Dim C As Range

    For Each C In Range("c2:c5000")
    
        If C.Value = "Paid" Then
        
            C.Offset(0, -2).Value = C.Offset(0, -1).Value
            C.Offset(0, -1).Value = 0
            
        End If
        
        
    
    Next C
Application.ScreenUpdating = True
End Sub

 

[elaine6985]

Thank you very much for the big help.. May God Bless you always! Take Care!

 

[elaine6985]

I make it worked already thanks.

 

[elaine6985]

I would like to ask something.. Do I need to paste this code to the module and sheet 1? and also I want to ask do i need to run it always? Is there other way that I don't need to run it?

 

[shadow12345]

no, just into a module... and I can change it so it runs every time you make a change to the data if you like?

Or you can assign a short cut key to run it for you... using this.

On the Tools menu, point to Macro, and then click Macros.
In the Macro name box, enter the name of the macro you want to assign to a keyboard shortcut key.
Click Options.
If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key cannot use a number or special character, such as @ or #.

Note: The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open.

If you want to include a description of the macro, type it in the Description box.

Click OK.
Click Cancel.

 

[elaine6985]

yes pls.. Thank you..

 

[shadow12345]

Im just about to run out of the door, but this should do it. This one has to go into the Sheet code, NOT the module code. Every time you change a value... it will on that cell.

Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Value = "Paid" Then
        
            Target.Offset(0, -2).Value = Target.Offset(0, -1).Value
            Target.Offset(0, -1).Value = 0
            
        End If
End Sub

 

[elaine6985]

Thank you and sorry to disturb you.. But I really appreciate your help.. Thank you very much.. :D Take care!