VBA write note “system” - Look if cell is empty

jjhr

New Member
Joined
Nov 30, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Basically I'am making a project which is retreiveing data from a database. I want to make function where a employee are able to write a note and should save them in colum L, M, N.

I am having issues with step 1, 2, 3 can anyone help?


When i write in colum E (write note) and clik "save notes" it should;

  1. it should take the data from the cell and insert it in A together with the date from colum D.
  2. It should copy the note and place it in colum L (E-note 1) if that is not empty then go colum M (E-note 2). It should also copy the date from colum D.
  3. it should delete the note from colum E.
  4. When i write a new note in the same row it should do step 1, 2 and 3.

This is the code i have made :

VBA Code:
Sub Test()
'Saves note
Application.ScreenUpdating = False
Dim Rng As Range
    If [E9999].End(xlUp).Row <= 3 Then Exit Sub 'Will exit sub if column E is empty
        Set Rng = Range("E4:E" & [E9999].End(xlUp).Row) 'Defines column E's range to be used
                
            For Each c In Rng
            Let i = c.Row
                If Not IsEmpty(c) And IsEmpty(c.Offset(, -1)) And IsEmpty(c.Offset(, -3)) Then
                    Range("A" & i) = Range("E" & i).Value
                    Range("B" & i) = Application.UserName 'Retreives value from Computer's Username
                    Range("D" & i) = Format(Now())   'Datestamp
                    
                    'Input value in first non empty L, M, or N column
                    If Range("L" & i) = "" Then
                            Range("L" & i) = c.Value
                        ElseIf Range("L" & i) <> "" And Range("M" & i) = "" Then
                            Range("M" & i) = c.Value
                        ElseIf Range("L" & i) <> "" And Range("M" & i) <> "" And Range("N" & i) = "" Then
                            Range("N" & i) = c.Value
                        Else
                    End If
                End If
            Next
Rng.ClearContents 'Deletes values in column E
Application.ScreenUpdating = True
End Sub


image.png
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
If I understood your request the only way to do what you ask is to change this line:
from:
If Not IsEmpty(c) And IsEmpty(c.Offset(, -1)) And IsEmpty(c.Offset(, -3)) Then
to:
If Not IsEmpty(c) Then
 

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,387
Latest member
Vincent88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top