Solved

Excel to find last cell in column empty and copy name to another cell

Posted on 2016-08-31
15
43 Views
Last Modified: 2016-09-01
Hi Experts,
I have a workbook that I need help with.
When you enter a new name to the last cell in the last row of a column in MasterLog, I want that name to copy to another cell in the second sheet. I would like to copy the name for a total of 5 times. Both sheets have data in many other columns too. This is the dummy WB for you. (no color formatting needed, I just colored it to help understanding)
If we can do this in a formula that would be great. Is it possible in formula?
Have a look here.help01.JPG help02.JPGDummyCopyLastEntry.xlsx
0
Comment
Question by:chris pike
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41778352
Put this code into the Master Log worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet
    Dim rng As Range
    Dim lst As ListObject
    Dim strName As String
    Set wks = ActiveSheet
    Set lst = wks.ListObjects(1)
    Debug.Print wks.ListObjects.Count
    If Intersect(Target, lst.Range) Is Nothing Then
    Else
        strName = lst.ListRows(lst.ListRows.Count).Range.Value
        Set wks = Worksheets("ExpDate")
        Set rng = wks.Cells(wks.Rows.Count, 2).End(xlUp)
        wks.Range(rng.Offset(1), rng.Offset(5)).Value = strName
    End If
End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41778369
Please try this....

Place the following code on Master Log Sheet Module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim sws As Worksheet, dws As Worksheet
Dim tbl1 As ListObject, tbl2 As ListObject
Dim slr As Long, dlr As Long
Set sws = Sheets("Master Log")
Set dws = Sheets("ExpDate")
Set tbl1 = sws.ListObjects(1)
Set tbl2 = dws.ListObjects(1)
slr = tbl1.Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
dlr = tbl2.Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
On Error GoTo SkipError
If Not Intersect(Target, sws.Cells(slr, tbl1.Range.Columns(1).Column)) Is Nothing Then
   Application.EnableEvents = False
   If Target <> "" Then
      dws.Cells(dlr, tbl2.Range.Columns(1).Column).Resize(5).Value = sws.Cells(slr, tbl1.Range.Columns(1).Column)
   End If
   Application.EnableEvents = True
End If
SkipError:
Application.EnableEvents = True
End Sub

Open in new window

For details, refer to the attached.
DummyCopyLastEntry.xlsm
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41778373
@aikimark
If I am not wrong, your code will be triggered if any name in the table column is changed and it will copy the last name to the ExpDate sheet each time the code is triggered.
I am not sure if that is the original requirement of the question.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41778389
Hi Chris

I was looking at a formula solution using INDEX function on source table but had to dash for a train. I will look again later when I am back at a pc.

Thanks
Rob
0
 

Author Comment

by:chris pike
ID: 41778403
@aikimark
Neerraj is correct, it duplicates last entry every time there is a change. This does not work, Neeraj's solution works great.

Neeraj, could you help me with putting in notes into the VB so I can make this work with my real sheet.
I need where to change table, etc  name..... I need to edit it easily.
It works great but having hard time putting into my working WorkBook
Thanks so much.
Chris
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 41778411
Good catch, Neeraj.

This will look for a change to the last cell in the table.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet
    Dim rng As Range
    Dim lst As ListObject
    Dim strName As String
    Set wks = ActiveSheet
    Set lst = wks.ListObjects(1)
    Debug.Print wks.ListObjects.Count
    If Intersect(Target, lst.ListRows(lst.ListRows.Count).Range) Is Nothing Then
    Else
        strName = lst.ListRows(lst.ListRows.Count).Range.Value
        Set wks = Worksheets("ExpDate")
        Set rng = wks.Cells(wks.Rows.Count, 2).End(xlUp)
        wks.Range(rng.Offset(1), rng.Offset(5)).Value = strName
    End If
End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41778417
Please find the attached with the comments added so that you can tweak it as per your requirement.
DummyCopyLastEntry.xlsm
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41778423
@aikimark
Thanks!
0
 

Author Comment

by:chris pike
ID: 41778442
@Neerraj
There is only one table on each sheet.
I am trying to change the column numbers now.
Master Log is named Table4
ExpDate is named TableExpDate

I tried your code, but can not get it to work,
This is what Master Log sheet looks like.
Column "C" on Master Log is the column I will add the names.
help03.JPG
On ExpDate Column "B" is the column to copy the names.
help04.JPG
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 400 total points
ID: 41778457
Change the sheet names in the following lines of code...
Set sws = Sheets("Master Log")
Set dws = Sheets("ExpDate")

Open in new window


The following line of code assumes that the Names are in column 1 of the Table on Master Log Sheet.
tbl1.Range.Columns(1)

Similarly the following line of code assumes that the Names are to be added in column 1 of the table on ExpDate sheet.
tbl2.Range.Columns(1)

Please note that the highlighted number represents the columns in table not the columns of the sheet.
So if the names are in column 3 of the Table on Master Log sheet, you will have to replace all the instances of tbl1.Range.Columns(1) with the following line......
tbl1.Range.Columns(3)

The same changes you will have to make for table column reference for the table on ExpDate sheet as well.
0
 

Author Comment

by:chris pike
ID: 41778606
Thanks Neeraj, Do I need to change the table names?
I changed tbl1.Range.Columns(1) for MAster log to column# 3
I didnt have to change EXP as that one is already column #1 of the table.
Still cant get it to work,
Thanks
Chris
0
 

Author Closing Comment

by:chris pike
ID: 41778678
Thank you very much.
Aikimark's solution works for my dummy sheet so I awarded him 100/

Thank you very much Neeraj for your help, always a pleasure.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41778685
You're welcome Chris! Glad to help.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41779463
I know you have already accepted a VBA solution but here is the formula solution as promised.

Formula in B4 of ExpDate:

=INDEX(Table1[Column1],CEILING(COUNTA(B$3:B3)/5,1),1)

Open in new window

Copy down as far as required, used in a table it should auto complete as new rows are entered in the table.

Note the $ to fix the first row of the count, as copied down this will expand the count range. Dividing the count by 5 will give a decimal value ending in a factor of 0.2; using CEILING with a factor of 1 will then round this up to the next integer.

So the first 5 entries give a count of 1 to 5 which divided by 5 give a decimal of 0.2, 0.4, 0.6, 0.8 and 1, when rounded up all become 1 so the INDEX uses the first entry in the list. The next 5 give a count of 6 to 10 which divided give 1.2, 1.4, 1.6, 1.8 and 2 which all rounded up give 2 so the INDEX uses the second entry in the list and so on.

Thanks
Rob
0
 

Author Comment

by:chris pike
ID: 41779882
@Rob
Wow, you guys are amazing,
Rob I was able to make yours work in ,my working program. It's weird how sometimes you cant just use the table name or column name, but you have to actually click on the sheet name first then the column name to link the formula up properly. I don't get why this works something and sometimes you can just plug in the name into the formula and everything works. But, I was able to fiddle and make it work.
Awesome work....I saved the formula, and will use it for sure in future projects.
Thanks
Chris
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now