Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA syntax
Hi I posted this message yesterday and would really
appreciate some help. I would like to add the values from a lookup table as shown below, to the row beneath my 'fields' (based on an extract file) on worksheet 1 of my spreadsheet where there is a match in description. The table looks like this and is held on a separate worksheet called "table". Column A Column B GWP 1 Earned 2 RI Result CY 6 RI Result PY 7 Claims CY 4 Claims PY 5 Expenses 3 Tax Investments Result 8 The fields on my worksheet are output across columns and are organised in the same order as column A above so that GWP, Earned, RI Result CY, RI Result PY, Claims CY etc. However in an empty row beneath these fields I'd like to somehow insert the values shown by Column B where it finds a match. This is so I can run mulitple reports from the same file and effectively resort the fields to achieve a different layout. Note, Column B of the table does not include a field number for each item because these will be dropped from the report. What VBA syntax would I use to do this. Do I need to create an array and use the Redim Preserve. Is it a Do Loop type routine. I'd really appreciate some help. Would I read through all the values in the table first and then step through each field on worksheet 1? Or would it be done on a individual basis. How do I then pick up the values in Column B? Can anyone provide a brief example? Many thanks Jacqui |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA syntax
Sorry that you didn't geta response yesterday.
To bonest, I dont' understand your question. You have a worksheet with data organised so that each column has the same structure as per your 'table' What you need is to extract all the data where the values match Well, the easiest was is simply to put a pivot table on the data. The problem is that your table is not normal. Your table should have those names in 'Table' as headers, and then there'd be an item on each row. Yor task would be so simple as using a filter would do the job without any code. So. We're left with doing an extract for each 'field' And as you say, maybe a loop. Copy the following code to a standard module Option Explicit Sub Key_Filter() Dim index As Long Dim cl As Long Dim key As Long Dim ws As Worksheet Sheet2.Copy Set ws = ActiveSheet For index = 1 To 10 If Sheet1.Cells(index, "B") < "" Then key = Sheet1.Cells(index, "B") For cl = ws.Range("IV1").End(xlToLeft).Column To 2 Step -1 If ws.Cells(index, cl).Value < key Then ws.Columns(cl).Delete End If Next ' column End If Next ' index End Sub This demo code assumes (1) your table is on sheet1 and the values are in B1:B10 and (2) you data is in sheet2 The code copies the data then loops through each item in the table. Starting from th eright most column and moving left, the code deletes each column from the copied data if the key value doesn't nact the data in the table. This is a "brute" force method. A nicer method would be to copy/transpose your data, filter for the values you want then copy the visible rows to your report. HTH Patrick Molloy Microsoft Excel MVP -----Original Message----- Hi I posted this message yesterday and would really appreciate some help. I would like to add the values from a lookup table as shown below, to the row beneath my 'fields' (based on an extract file) on worksheet 1 of my spreadsheet where there is a match in description. The table looks like this and is held on a separate worksheet called "table". Column A Column B GWP 1 Earned 2 RI Result CY 6 RI Result PY 7 Claims CY 4 Claims PY 5 Expenses 3 Tax Investments Result 8 The fields on my worksheet are output across columns and are organised in the same order as column A above so that GWP, Earned, RI Result CY, RI Result PY, Claims CY etc. However in an empty row beneath these fields I'd like to somehow insert the values shown by Column B where it finds a match. This is so I can run mulitple reports from the same file and effectively resort the fields to achieve a different layout. Note, Column B of the table does not include a field number for each item because these will be dropped from the report. What VBA syntax would I use to do this. Do I need to create an array and use the Redim Preserve. Is it a Do Loop type routine. I'd really appreciate some help. Would I read through all the values in the table first and then step through each field on worksheet 1? Or would it be done on a individual basis. How do I then pick up the values in Column B? Can anyone provide a brief example? Many thanks Jacqui . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA syntax
Dear Patrick Thank you for your reply. The example I'd given was a really simplified version of the actual data and I knew it probably wouldn't lend itself to a written explanation. Sorry if it didn't make sense. Am I able to send you a file with a small XL example if not then I've attempted to explain again. Pls note I do not wish to use filters or pivot tables etc Worksheet 1 contains data organised across columns rather like fields in a database. The captions/headers of this row are A B C D E F G H GWP UPR Earned RI CY RI PY Claims CY Claims PY Expenses and so on across 95 columns. Now what I'd like to do is reorganise those columns in a different order according to the report type, ie whether it's a UKGAAP report , a FR GAAP report but give the user control via a front-end table rather than hard-coding anything. Hence on worksheet 2 (called table) I've created the following Column A Column B Column C Desc Report1 Report2 GWP 1 UPR Earned 3 1 RI CY 6 RI PY 7 Claims CY 4 2 Claims PY 5 3 and so on. The idea is that the number denotes the field reference number. In actual fact in Columns C, D there will be other definitions to create a number of reports from the same extract file. What I'd like the VBA to do is step through column A of the table for each item with an entry in column B (don't worry about column C 'cause I know how to refer to that). VBA then needs to remember the description and related number so that where a match is found against the original data (on worksheet 1), the detail is transferred so that A B C D E F G H GWP UPR Earned RI CY RI PY Claims CY Claims PY Expenses 1 3 6 7 4 5 I can then use this row as a basis for resorting my columns, using VBA. I'm ok with the code to hide/delete unwanted columns but thank you for your example. Please note I cannot organise my source data in the correct order to just run the routine by hiding/deleting columns (that was my original plan) it's a no go because of the differing report layouts we use. Just trust me on that one. I hope this makes a little more sense. Any more help would be greatly appreciated. Kind regards Jacqui *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA syntax
of course - I'm emailing a direct address for you
-----Original Message----- Dear Patrick Thank you for your reply. The example I'd given was a really simplified version of the actual data and I knew it probably wouldn't lend itself to a written explanation. Sorry if it didn't make sense. Am I able to send you a file with a small XL example if not then I've attempted to explain again. Pls note I do not wish to use filters or pivot tables etc Worksheet 1 contains data organised across columns rather like fields in a database. The captions/headers of this row are A B C D E F G H GWP UPR Earned RI CY RI PY Claims CY Claims PY Expenses and so on across 95 columns. Now what I'd like to do is reorganise those columns in a different order according to the report type, ie whether it's a UKGAAP report , a FR GAAP report but give the user control via a front-end table rather than hard-coding anything. Hence on worksheet 2 (called table) I've created the following Column A Column B Column C Desc Report1 Report2 GWP 1 UPR Earned 3 1 RI CY 6 RI PY 7 Claims CY 4 2 Claims PY 5 3 and so on. The idea is that the number denotes the field reference number. In actual fact in Columns C, D there will be other definitions to create a number of reports from the same extract file. What I'd like the VBA to do is step through column A of the table for each item with an entry in column B (don't worry about column C 'cause I know how to refer to that). VBA then needs to remember the description and related number so that where a match is found against the original data (on worksheet 1), the detail is transferred so that A B C D E F G H GWP UPR Earned RI CY RI PY Claims CY Claims PY Expenses 1 3 6 7 4 5 I can then use this row as a basis for resorting my columns, using VBA. I'm ok with the code to hide/delete unwanted columns but thank you for your example. Please note I cannot organise my source data in the correct order to just run the routine by hiding/deleting columns (that was my original plan) it's a no go because of the differing report layouts we use. Just trust me on that one. I hope this makes a little more sense. Any more help would be greatly appreciated. Kind regards Jacqui *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
right syntax | Excel Worksheet Functions | |||
vba syntax | Excel Discussion (Misc queries) | |||
SQL syntax | Excel Worksheet Functions | |||
Syntax Help | Excel Worksheet Functions | |||
syntax | Excel Programming |