If formula finds corresponding data, copies into table- how?

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

I am attempting to create an automated data grab type formula which I will then use to set up an automatic email macro.

What I'm looking to do is have a formula which looks through the table (table 2), finds any dates that are more than 2.5 years old (some sort of a if/find function using today()-912 I would presume) then locates the corresponding training course title in row two, and name in column C&D (it may be easier to concatenate these into one? Not sure.). Then copy this data into a table in another sheet (outstanding training) with said mined data.

So it would look at this:

1597653740594.png


and regurgitate this:

1597654183583.png


Has anyone done anything like this before? I'm afraid this is beyond the realms of my understanding...so any help appreciated!

Thanks all :)
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Ah, sorry for being thick. I assumed that because VBA has sheet 1,2,3 etc listed, the script would refer to these. Have changed accordingly.

I'm familliar with how to assign macros, I've done this and when clicking the shape, I get 'subscript out of range':

1597844780805.png


As you can see I have another script also running, I hope there is no interferance.
I'm trying to make sense of the code and I can kind of see what is happening (you know when you can sort of read a foreign language, but wouldn't have a clue as to how to actually speak it?) but not enough to solve this problem.

I've now put an underscore in "outstanding training" to see if that helped; it did not.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Sorry, ignore last- I noted I had misspelled my sheet name. I am now getting a message saying 'type mismatch'. It feels like we're getting somewhere- it's at least not saying its out of range or asking me to debug it!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
Glad you found the error, even after your second reply it took me a while to find 'Oustanding Training', guess my autocorrect is working too well :oops:

Not sure why you're getting a type mismatch, the code ran fine for me on a quick test. Such an error should be highlighted with debug when you try to run it. Type mismatch would mean that a variable has been incorrectly declared, those visible in the image all appear to be correct.
Your existing code shouldn't cause any conflict as it only runs when changes are made on the summary sheet. The new code is only making changes to outstanding training.

Also, you can refer to the sheets as Sheet1 etc, but that is a vba code name not the excel name so it is done differently. Set ws1 = Sheet1
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
ok, looks like retracing steps/ giving a full picture may be handy here, in order to know completely whats going on.

This is how the spreadsheet looks:

1597911050699.png


This is the grab_data button, in the oustanding training tab, assigned to the macro:

1597911116931.png


and this is the code at present. Note, I've changed the name of the code to make sure that the macro I was assigning to the button was the correct one.

1597911209341.png

Running the macro yields the Type Mismatch dialogue box, however does not open any debugger- it just closes the dialogue box and returns you to excel.

Time for a game of spot the difference?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try moving the code to a proper module and running it manually, not with a button.
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Moved it to a module, and it worked! At least, it found 2 entries, one in F7 and one in I7. So this is solid progress- how do I get it to look at the full table range?

I assumed that I would want a button to make it fire as I don't want it to run in the background say whenever the file is opened or edited.

I've also changed the references to rows 2 and 3 in the line below as it was picking up some extra (wrong) data.

It also said mismatch again, and highlighted this much of the code:

1597938560507.png

Another user has suggested I dl the XL2BB addin, will do this now so that this makes more sense.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
I think that the columns were adrift from deleting the 'Currently employed' column, I had based the code on the image in post 1 which still had that in place.

The new mismatch error indicates that there might be something other than a date in the cell it is looking at, or possibly it needs a slight change to work with your date settings. Without seeing what is causing the error I could be guessing all day and changing many wrong things. If you hover your cursor over c.Value when you debug it will tell you the value of the cell causing the error.

Code below should cover the whole table, but the error above still needs inversigating.
VBA Code:
Option Explicit
Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim c As Range, rng As Range, rw As Long, lRow As Long, lCol As Long
Set ws1 = Worksheets("Summary"): Set ws2 = Worksheets("Outstanding Training")
With ws1
    lRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    lCol = .Cells(2, Columns.Count).End(xlToLeft).Column
    Set rng = .Range("F3", .Cells(lRow, lCol))
End With
rw = 2
For Each c In rng.SpecialCells(xlConstants)
    If IsDate(c.Value) Then
        If Date > (c.Value + 912) Then
            ws2.Cells(rw, 1) = ws1.Cells(c.Row, 2) & " " & ws1.Cells(c.Row, 3)
            ws2.Cells(rw, 2) = ws1.Cells(2, c.Column)
            ws2.Cells(rw, 3) = c.Value
            rw = rw + 1
        End If
    End If
Next
End Sub
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
So I think basically the database I was given to work on had dates that we all formatted wrong. I've just spent a while re-typing them all and it appears to have solved that issue. I've plugged in your new code and it works a treat thank you.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,698
Messages
5,765,994
Members
425,322
Latest member
galaxy6623top

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