Macro Help

[Kinsdono]

Hello,

Can anyone help me with a macro question. I want to be able to create a macro that searches within a database based upon a unqiue identifier and match the item and bring back different coulumns worth of data, also i want the macro to be able to insert a row if there is a new unique identifier added?

Thanks,

Kinsdono

 

[XLGibbs]

Do you want that supersized?


When you say database, is this a table of data within the excel file? or an external database...if not in excel.

If it is in excel...you can use VLOOKUP or HLOOKUP for starters...no code, just formulas.

You can set up a dynamic range to adjust the size of the seach table when new entries are added.

If not in excel is it Access, ODBC, or other database?

 

[Kinsdono]

Sorry, i need to be more specific. It is Excel and i am using fomrulas already, but the problem is that the formulas are on multiple tabs refering to one tab "Data Extract" that i use to get the information from. The problem occurs when i import my data into the "Data Extract" tab and new items occur. I then have to go and insert a row on different tabs depending on what the tab is testing. It causes a lot if manual work and could lead to errors. Here is a sample of a macro i just wrote for just getting the data from the "New Jersey File" which pulls the data from the "Data Extract Tab", So the tab this code is for is "Total Holding's over 5%" and the formulas look in the "New Jersey Tab" which pulls from the "Data Extract" Tab. Please let me know if you have questions because i know this might seem confusing?

With Sheets("Total Holding's over 5%")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

.Range("B2:B" & LastRow).Formula = "=VLOOKUP(A2,'New Jersey File'!$A$2:$A$82:'New Jersey File'!$M$2:$M$82,13,0)"
.Range("C2:C" & LastRow).Formula = "=SUMIF('New Jersey File'!$A$2:$A$32,A2,'New Jersey File'!$W$2:$W$32)"
.Range("D2:D" & LastRow).Formula = "=C2/$C$34"
.Range("E2:E" & LastRow).Formula = "=VLOOKUP(A2,'New Jersey File'!$AH$2:$AH$82:'New Jersey File'!$AR$2:$AR$82,11,0)"
.Range("B2:E" & LastRow).Value = .Range("B2:E" & LastRow).Value

 

[XLGibbs]

So is it the NewJersey set of data that may change size? or only the data extract data that may change size?

 

[lenze]

Try MSQUERY. It works quite well in retrieveing data within a workbook and is much more flexible than lookups. See Tushar's tutorial "RDBMS in Excel" at
http://www.tushar-mehta.com/excel/newsgroups/

lenze

 

[Kinsdono]

It is the "Data Extract" that changes size, new items can be added or removed from day to day. So i have formulas pulling (vlookups) into the "New Jersey File" tab to organize and tie out my data. And then i use other tabs to sort and define certain tests and compliances. So basically i need the macro to check to see if the unique identifier i have created on the "Data Extract" tab (which gets pulled into the "New Jersey File" tab) is still there and pull in all relative info i am looking for, and if it is new item to somehow insert a row on the "New Jersey Tab" and the other Tab's based upon each tabs testing?

 

[XLGibbs]

Refer to the new data via dynamic range

OFFSET(startcell, rows to offset, columns to offset, height of range, width of range)

Example:

If Date Extract is your tab name and the data starts on cell A1 of that sheet:

=OFFSET('Data Extract'!$A$1,0,0,COUNTA('Data Extract'$A:$A),COUNTA('Data Extract'$1:$1)

You can put that formula in a defined name range, and it will adjust to the size of the table after importing new data.

The formulas looking at the Data Extract would have to be adjusted to look at the named range instead ....then the adjustments are not as necessary....

closer?

 

[Kinsdono]

i think i know what you mean but could you break out the code as an example and for the Data Extract Tab my Unique identifier starts in cell C1 and were i have the Unique Identifier on the New Jersey File (using the vlookup) is starting in cell C1 as well. So for my Code for the New Jersey Tab i have as following:

Sub ComplianceTest()
'To create formulas automatically'
Dim LastRow As Long

With Sheets("New Jersey File")
LastRow = .Range("C" & Rows.Count).End(xlUp).Row

.Range("I2:I" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$F$4:$F$660,4,0)"
.Range("J2:J" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$H$4:$H$660,6,0)"
.Range("K2:K" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$I$4:$I$660,7,0)"
.Range("L2:L" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$J$4:$J$660,8,0)"
.Range("M2:M" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$K$4:$K$660,9,0)"
.Range("N2:N" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$L$4:$L$660,10,0)"
.Range("O2:O" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$M$4:$M$660,11,0)"
.Range("P2:P" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$N$4:$N$660,12,0)"
.Range("Q2:Q" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$O$4:$O$660,13,0)"
.Range("R2:R" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$P$4:$P$660,14,0)"
.Range("S2:S" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$Q$4:$Q$660,15,0)"
.Range("T2:T" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$R$4:$R$660,16,0)"
.Range("U2:U" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$S$4:$S$660,17,0)"
.Range("V2:V" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$T$4:$T$660,18,0)"
.Range("W2:W" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$U$4:$U$660,19,0)"
.Range("X2:X" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$V$4:$V$660,20,0)"
.Range("Y2:Y" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$W$4:$W$660,21,0)"
.Range("I2:Y" & LastRow).Value = .Range("I2:Y" & LastRow).Value

End With


Columns I through Y are pulling data from the Data Extract Tab using cell C2 (My unique identifier) as the lookup value.