Message ti alert the user to link additional cells

[bearcub]

I have a spreadsheet (sheet2) that is linked to sheet (sheet1) that is imported from our database. I would like a message alert to pop up to notify me that I have less data on the linked sheet (sheet2) than the sheet that was just imported. Sheet2 is linked to an Access database to run a report. I just discovered that if I have formulas in rows without any data each will show up as a blank row in our published directory - which is a no no.

What I was thinking of is creating a message box alert to inform the user (most likely an admin who isn't savvy in Excel) that their are more rows of data in the imported sheet than on the linked sheet. And, to let them know they have to copy the formulas down to capture all the missing data.

For example, if I imported 400 rows of data and the linked sheet only has formulas that go down to row 350 I would have to copy the formulas down another 50 rows. I would like alert the admin that 400 rows were imported but she only has 350 linked rows and she needs to add an additional 50 rows of formulas to capture all the data that should be going into Access. Does that sense or should I explain further?

Is this possible and would it be attached to so sort of worksheet event?

Thank you for your help,

Michael

 

[AlphaFrog]

You're welcome.

Autofill down does the same function as copy\paste. The counts are to calculate how far down to autofill. This (in red) will reduce the autofill down by one row.

[COLOR=darkblue]Sub[/COLOR] Fill_Links()
[COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], NextRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], LinkedRowsCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
LastRow = Sheets("Sheet1").UsedRange.Rows.Count
[COLOR=darkblue]With[/COLOR] Sheets("Sheet2")
    NextRow = .UsedRange.Rows.Count
    LinkedRowsCount = .Columns("A").SpecialCells(xlCellTypeFormulas).Count
    [COLOR=darkblue]With[/COLOR] .Rows(NextRow).Range("A1:BZ1")
        .AutoFill Destination:=.Resize(LastRow - LinkedRowsCount [COLOR="#FF0000"]- 1[/COLOR])
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] With
End [COLOR=darkblue]Sub[/COLOR]

 

[bearcub]

Cool! I was thinking I had to include an entire line to get rid of that last row. But I can see where that incrementer decreases the size by one row (adding the "-1" to the Resize property).

This is really slick code. I can imagine if I were to record this with the recorder that the code would be 3 or 4 times the amount of code!

Thank you again for your help,

Oh, by the way, how do you get your code to show up the way it does? I seem to have difficult making my code come out this way whether I want to paste code that I'm working on.

I just thought of this one. If I more more formula cells on my page (like I'm importing 300 lines but my present sheet as 310 rows of data) will this take care of that (not that I'm asking you to include anything else. Just curious if this will remove any extra formula rows in the way that you have it now).

Michael

 

[AlphaFrog]

An old VBA add-in called HTML Maker copies the VBA code from your VBA editor with HTML or BB code tags to color the keywords. It's only useful if you paste a lot of code to the forum.


The code indentation is done using another VBA add-in called Smart Indenter. Recommended if you write a lot of code.


One other useful VBA add-in is called MZ Tools. It adds a few utilities to the editor. It has a tool to store your favorite code snippets.


All those links can be found here:
https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html


Didn't understand the last question.

 

[bearcub]

Thank you for the tips on the code.

What I meant by the last question if I imported 500 rows, for example, but my sheet2 had 510 rows (the previous import had 510 rows of data meaning 10 members are no longer in the company database). This would mean that I would have 10 blank rows of data which I would have to manually delete. Otherwise, these would should up in the access reports as an empty row of data.

I was wondering if there was a way to delete the excess rows in Sheet2 if necessary. Otherwise, I could add a note to the admins to delete any rows that show Zeros or errors.

Michael

 

[AlphaFrog]

It currently doesn't delete any excess rows. You never really described your data import process. I don't know if you add to previously imported data or overwrite it. I just assumed you always added imported data.

 

[bearcub]

yes, that was my bad. This is an file that is constantly being updated. I work for the California Teacher's Association and we have leadership directories that are published annual. It seems that the access database that we initially built was used for different types of reports and ended up breaking because one query could be used for different types of reports. I saw one query that used 10 different tables but only 3 of them pertained to this report that I'm referring to. So I had to reverse engineer the entire database and ended up creating different DB's for different offices.

The file that you've help with is comprised of sheets that are linked to individual tables in Access. Since the admins are more familiar with Excel i thought it would be easier for them to update an Excel spreadsheet rather than rummaging around in Access which they don't have any training on (or use).

They have a database called Falcon which contains all the CTA members. The Sheet1 import I've referred to is from the database. When it comes out of the database into Excel (as a data dump) it contains over 67000 rows of data and 77 columns. I ran a series of macros initially to strip out non - leadership members to make it easier to manage in Access.

Sheet2 maybe has 40 columns, not 77. Most of the data on this sheet comes from sheet1 but also from other sheets in the file that I had to create as work arounds because some of the data coming from Falcon is not the same data being published (the leadership position names don't match).

Thank you for providing me with the code, this is very helpful and will make it easier on the admins (I'm just a consultant and once my assignment is up there won't be anyone there to fix what I've done if it breaks. The systems they use are not compatible so they have to do a lot of a manipulation in Word and Excel to make things work for their reports they have to run and publish.

So, this is why I was asking if there was an easy fix to remove the excess rows on sheet2 (if they don't contain any positions). Most of the time, more rows won't be added - I was just thinking ahead in case this did happen. Then, I thought, what happens if there are less members in the later report. The admin may not be aware that this the case and run the repo

 

[AlphaFrog]

[color=darkblue]Sub[/color] Fill_Links()
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color], NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color], LinkedRowsCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
    LastRow = Sheets("Sheet1").Cells.Find("*", Sheets("Sheet1").Cells(1, 1), xlFormulas, , 1, 2).Row
    [color=darkblue]With[/color] Sheets("Sheet2")
        NextRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, , 1, 2).Row
        LinkedRowsCount = .Columns("A").SpecialCells(xlCellTypeFormulas).Count
        [color=darkblue]If[/color] LastRow - LinkedRowsCount - 1 > 1 [color=darkblue]Then[/color]
            [color=darkblue]With[/color] .Rows(NextRow).Range("A1:BZ1")
                .AutoFill Destination:=.Resize(LastRow - LinkedRowsCount - 1)
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]ElseIf[/color] LastRow - LinkedRowsCount - 1 < 1 [color=darkblue]Then[/color]   [color=green]'delete excess rows[/color]
            .Rows(NextRow - (LinkedRowsCount - LastRow) - 1 & ":" & NextRow).ClearContents
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[bearcub]

Thank you so much for your help. The girls unknowingly will appreciate this. Once I get the process done I have to go back and document everything for them, even the code I have received from folks like yourself on the forum. I'm really not looking forward to the documentation piece because it feels like pulling teeth though I know it's necessary.

Thanks again,

Michael

 

[bearcub]

Hi AlphaFrog (I don't know your real name.

When I was trying out your code at home it worked exactly has I had expected. However, I just tried the macro out and there seems to be a snafu. It works with 3 columns of data but not 40 or 50 columns.

I called this routine from my main one and nothing happened. I moved the Extract and the linked sheet to a new file and ran the code, nothing happened. I then deleted all but 3 rows of data and it worked! I then copied over all the columns (the original set) and it worked fine. Really strange.

I thought at first that I had the wrong names which is why used a new file just calling them sheet1 and sheet2. Worked ago.

This is the file that I used that didn't work:

Sub Fill_Links4()
    Dim LastRow As Long, NextRow As Long, LinkedRowsCount As Long
    LastRow = Sheets("REGIONAL EXTRACT").Cells.Find("*", Sheets("REGIONAL EXTRACT").Cells(1, 1), xlFormulas, , 1, 2).Row
    With Sheets("All SCC Ready to Import")
        NextRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, , 1, 2).Row
        LinkedRowsCount = .Columns("A").SpecialCells(xlCellTypeFormulas).Count
        If LastRow - LinkedRowsCount > 1 Then
            With .Rows(NextRow).Range("A1:BZ1")
                .AutoFill Destination:=.Resize(LastRow - LinkedRowsCount)            
            End With
        ElseIf LastRow - LinkedRowsCount < 1 Then    'delete excess rows
            .Rows(NextRow - (LinkedRowsCount - LastRow) & ":" & NextRow).ClearContents
        End If
    End With
End Sub

Sub Fill_Links()
Dim LastRow As Long, NextRow As Long, LinkedRowsCount As Long
LastRow = Sheets("REGIONAL EXTRACT").UsedRange.Rows.Count
With Sheets("All SCC Ready to Import")
    NextRow = .UsedRange.Rows.Count
    LinkedRowsCount = .Columns("A").SpecialCells(xlCellTypeFormulas).Count
    With .Rows(NextRow).Range("A1:BZ1")
        .AutoFill Destination:=.Resize(LastRow - LinkedRowsCount)
    End With
End With
End Sub

The regional extract is the imported file and the all SCC data to import is the one that is linked to it. What I can't understand is why 3 columns work but the all the columns don't.

This is code that I used that worked with 3 columns then more columns:

Sub Fill_Links()
Dim LastRow As Long, NextRow As Long, LinkedRowsCount As Long
LastRow = Sheets("Sheet1").UsedRange.Rows.Count
With Sheets("Sheet2")
    NextRow = .UsedRange.Rows.Count
    LinkedRowsCount = .Columns("A").SpecialCells(xlCellTypeFormulas).Count
    With .Rows(NextRow).Range("A1:BZ1")
        .AutoFill Destination:=.Resize(LastRow - LinkedRowsCount)
    End With
End With
End Sub

is there anything that I'm doing wrong?

Thanks for your help,

Michael

 

[AlphaFrog]

I have no idea.

Upload the broken file to a file share site and post the link here. I'll take a look at it.