VBA, Find, Critera

Resolved · High Priority · Version 2010

Thu 3 Dec 2015, 14:57 Delegate Melissa said...

Melissa has attended:
Excel Dashboards for Business Intelligence course

VBA, Find, Critera

Hi All,

I have a large data table, with KPI's from several site/countries. (Will be imported monthly).

I want to split it into smaller tables with a country on each workbook.

Instead of manually creating a new table and finding and referencing individual cells I would like to automate it.

I've been trying IF/AND's and VLOOKUP/INDEX/MATCH but i thinks its too complicated to work, and i think i need some VBA Script instead, but it been over 6 months since my training...

I would like to create a new table, by looking for and finding data in the large table.

For example...

Look in Table1 A:A = "France" AND B:B = "Le Harve" AND C:C = "KPI-01" AND F:F = "KPI Score" if all conditions are met in that row to return Column A in that row.

The new table would continue to fill in this way with B returning Table1 B in that row, C returning Table1 C, D returning Table1 G, etc as needed in the new table.

Please help, i've been trying all day! I know that once the first bit works i'll be able to amend it accordingly for everything else.

Thank you.

Fri 4 Dec 2015, 20:28 Trainer Jens said...

RE: VBA, Find, Critera

Hi Melissa,

Thank you for the forum question.

How much VBA knowledge do you have?

You can use a decision Code (If Then Else or Select Case) to test the conditions. Loop through (Do Loop, For Next or For Each loops) can all loop through your records.

If conditions are true you can use EntireRow.copy.

Then you will need to navigate to the worksheet where you want to create the new table. A variable can be used to find the first blank row for pasting the records.

To write the code you will need to have the workbook in front of you.

I hope I have guided you in the right direction.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 7 Dec 2015, 08:04 Delegate Melissa said...

RE: VBA, Find, Critera

Thank you Jens,

I understood VBA when i did my training, but i haven't put it into practice since then. I'll give it a go.

Kind regards,

Mel.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Use RANDBETWEEN to generate random numbers

There may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify.

The function looks like this:

=randbetween(LOW,HIGH)

where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.

This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:

=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION).

where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).

One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.