VBA store references using arrays

[bluehigh5]

Hello everyone,

I am a beginner with VBA and this is my first post here, so I want to start off by saying thanks for this great source.

Here is my issue:

- In my "output" workbook I have 2 sheets: "database" and "report"
- In the first column of database I have my reference numbers, and other columns contain some data-info.
- In the report sheet, again in the first column I have my reference number and according to these ref numbers I have some formulas in other cells.
- Here comes the question, How can I pass some data from two cells in my database to one cell in my report sheet by using the reference numbers? The formats of the texts(font color, size) in the database sheet should be kept when transferring to report sheet.

I hope I could have explained well,

Thanks in advance for your help!

 

[hiker95]

bluehigh5,

Welcome to the MrExcel forum.

What version of Excel are you using?

Can you post the database worksheet, and, post the report worksheet (manually formatted by you) with the results that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker
Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[bluehigh5]

Hi hiker95,

I use excel 2007.

Here is the database sheet:


-- removed inline image ---


and report:


-- removed inline image ---


On the report sheet you can see what I want to have, but notice that data in column B includes some formulas, so no need to use VBA for that.
I would like to have macros that will enable me to transfer data in column C and D of database sheet to the merged cells(C and D) in the report sheet.

The reference numbers in database sheet are fixed, whenever I enter a reference number in report sheet and then run the macro I will have the data needed in the merged cells there. Please notice that in database sheet column C and D have different formats(color, font..).

Thanks again for your help!

 

[bluehigh5]

database

*	A	B	C	D	E
1	*	*	*	*	*
2	2	app2	physics1	physics2	wed
3	5	app3	chemistry1	chemistry2	thurs
4	8	app4	math1	math2	mon
5	10	app9	foto1	foto2	tue
6	66	h5	eng1	eng2	fri
7	34	h6	comp1	comp2	sat
8	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:75px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



report

*	A	B	C	D	E
1	*	*	*	*	*
2	ref	app3	chemistry1 chemistry2	*
3	5	thurs	*
4	*	*	*
5	*	*	*
6	*	*	*
7	ref	h5	eng1 eng2	*
8	66	fri	*
9	*	*	*
10	*	*	*
11	*	*	*
12	ref	app9	foto1 foto2	*
13	10	tue	*
14	*	*	*
15	*	*	*
16	*	*	*
17	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:108px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =IFERROR(INDEX(database!$A$1:$E$499,MATCH($A3,database!$A$1:$A$499,0),2),"") B3 =IFERROR(INDEX(database!$A$1:$E$499,MATCH($A3,database!$A$1:$A$499,0),5),"") B7 =IFERROR(INDEX(database!$A$1:$E$499,MATCH($A8,database!$A$1:$A$499,0),2),"") B8 =IFERROR(INDEX(database!$A$1:$E$499,MATCH($A8,database!$A$1:$A$499,0),5),"") B12 =IFERROR(INDEX(database!$A$1:$E$499,MATCH($A13,database!$A$1:$A$499,0),2),"") B13 =IFERROR(INDEX(database!$A$1:$E$499,MATCH($A13,database!$A$1:$A$499,0),5),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[bluehigh5]

Same post here:

VBA store references with arrays

 

[hiker95]

bluehigh5,

A message to forum cross posters
Please read this
Excelguru Help Site - A message to forum cross posters


I would suggest that you start a New Post, with a new descriptive title that includes VBA macro, and then:

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.


If you can not do the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.