Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-08-31
15
Medium Priority
?
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 46

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 32

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 32

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

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 46

Assisted Solution

by:aikimark
aikimark earned 400 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 32

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
 
LVL 32

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 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 1600 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 32

Expert Comment

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

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

722 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