For each cell... VB HELP Solved/Closed

Question

Hello,

Column A contains either "Ja" (yes), "Nee" (no) or nothing. I would like to know on which date the cell value is changed to "Ja".
Therefore I used the first available column (M) to input the following formula:
=IF(A4="Ja",TODAY(),"" )

When I open the file the next day the date changes as well, which is not desired.

So I would turn to VBE and write a code like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("M4") <> "" Then Range("M4").Copy
Range("N4").PasteSpecial Paste:=xlPasteValues
End Sub

Now I would like to do this for a range of M4:M1010.
Repeating these two lines of code a thousand times is obviously not an option.
How do I write a code like:
For each cell in range M4:M1010 <> "" Then Copy respective cell to next column ...

Is this possible or is there a better way to solve this issue?

Kind regards,
Trowa

rizvisa1 · Answer

Trowa, I am not sure if "N4" is what you meant or its a typing error. Presuming it is what you want then you can use a loop. I am not sure if worksheet_change is best for this. I think you should use Workbook_BeforeClose. Only time there could be an issue would be if some one was working around mid night :P

Any how, you would know best, here is my solution. Though again, this will be triggred on change. I think you should on do it if only COL A changes as this is when you want your date to be as a value instead of formula.
Target.Column should tell u if the trigger was A column or not


Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    'if you want to find out the last used cell in column M
    'llastrow = Cells(ActiveSheet.Rows.Count, "M").End(xlUp).Row

    'in case you want to hard code
    llastrow = 1040


    processrow = 4

    Do While (processrow <= llastrow)
    
        If (Cells(processrow, "M") <> "") Then
        
            Cells(processrow, "N").Value = Cells(processrow, "M").Value
        End If
        
    processrow = processrow + 1
    
    Loop
    Application.EnableEvents = True

End Sub

aquarelle · Answer

Hi ,

Here is an other macro that you could also try :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set MyPlage = Range("A4:A1040")
    For Each Cell In MyPlage
    If Cell.Value = "Ja" And Cell.Offset(0, 13).Value = "" Then
            Cell.Offset(0, 13).FormulaLocal = Format(Now, "dd/mm/yy")
        End If
    Next
End Sub

See you :)

Trowa · Answer

Wow, this gives me new insight in how to write codes.

Both codes works great.

I thought I had to use two columns; one for the formula and one to copy the value to.

For some reason Aquarelle always seems to know what I need. All I have to do is change the cell.offset(0,13) to (0,12) and I'm done.

Also thanks Rizvisa for providing an understandable (to me) loop code along with your explanation.

This has been a very profitable post for me.
Many thanks for the reply's.

Whishing you both all the best,
Trowa

For each cell... VB HELP

3 responses

Similar discussions

Subscribe To Our Newsletter!