Help with VBA syntax

  • Thread starter Thread starter jacqui
  • Start date Start date
J

jacqui

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
 
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
 
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
 
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


.
Click to expand...
 
Back
Top