Matching data from table in VB

[Chi Man Wong]

I have a problem using VB which I'll try to explain:

In sheet A I have a table with :
column 1: dates (e.g. 01-01-2005) over a range of 3 years
column 2 till 8: numbers

In sheet B I have a row with dates (say row 1)

what I want to do is to use the date of row 1 in sheet B, look it up
in the table in sheet A and copy the values of the corresponding
numbers into sheet B row 2
For example:

Sheet B Cell(1,1) is 01-01-2005. I want to find the numbers
corresponding to that date in the table and paste the values in row 2
untill 9

An "If" function would take ages since the table contains data of 3
years. Anybody an efficient sollution? Thanks in advance.

[Cor Ligthert]

Re: Matching data from table in VB

Chi,

Why dont you use two datatables, create as primary key the date and set than
relations between them in a dataset.

When you want to know more than reply (because I don't like it to type a lot
when you don't want to do it like this)

I hope this helps?

Cor

[Chi Man Wong]

Re: Matching data from table in VB

Cor,

thanks for your quick reply but I think I forgot to add some crucial
inforamtion: I'm using Excel and not Access. I'm pretty new using VB.

A solution could be using HLOOKUP and VLOOKUP functions but that would
mean I have to use those functions a milloin times which will slow the
program down too much.

Any suggestions?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cor Ligthert]

Re: Matching data from table in VB

Chi,
[color=blue]
> thanks for your quick reply but I think I forgot to add some crucial
> inforamtion: I'm using Excel and not Access. I'm pretty new using VB.
>[/color]

That would be even more a reason to use an dataset and a datatable.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp

You have to read both as table of course than set the primary keys

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatatableclassprimarykeytopic.asp

Than set the relations

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclassrelationstopic.asp

And than proces accoording to that

Don't become afraid of the samples that are in these pages, you need only a
part of it everytime

I hope this helps?

Cor

[Chi Man Wong]

Re: Matching data from table in VB

Thanks for your help.
Let's say I have a sheet which is set up like:

column1 column2 column 3
1-1-2005 1 4
2-1-2005 2 5
3-1-2005 3 6

And in sheet #2 I have this:

column1 column2 column 3
1-1-2005 2-1-2005 3-1-2005

You suggest that I should:
- Create a dataset of the data in sheet 1
- Add a primary key, in this case the date
- For a specific date in sheet 2, look up in the table using the primary
key

Am I correct? How would you do it? If you can post some code, would be
awesome. Code in the links you gave me is quit hard for me to read.

Thanx a million for the info u already gave me.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cor Ligthert]

Re: Matching data from table in VB

Chi,

Exact, however the code to do that would be to much for me to write as a
sample.
I think that you should do yourself.

Cor

[Chi Man Wong]

Re: Matching data from table in VB


Cor,

I understand. Thanx again.
Just one more question: I should do this using classes right?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cor Ligthert]

Re: Matching data from table in VB

Chi,

This you can do normal in line, there is no need to create extra classes in
my opinion.

I see nothing that you can reuse so that is as well no reason.

First create the two datatables in the same dataset

Thinking about it you can even forget the primary key and the relation maybe
that this is even more simple, this is very roughly written.

You set the second table a dataview
\\\
dim dv as new dataview(ds.tab le(1))
///
Than you can while you go through your first table do something like this
\\\
for i as integer = 0 to ds.tables(0).ro ws.count-1
dv.rowfilter = "Key = ' & ds.tables(0).ro ws.(i)("mykey") .ToString & "'"
'assuming it is a string
for y as integer = 0 to dv.count - 1
'here you get all the related rows in the second table from which is
the first field
dim myfirstfieldInT heRow as string = dv(y)("myfirstf ield").ToString
next
next
///

I hope this helps?

Cor

[Chi Man Wong]

Re: Matching data from table in VB

Cor,

looks good even though I don't understand every line. I'm gonna try this
later. First I have to figure out how to create the datasets since I've
never done that.
Question, will the code you give me work for the Visual Basic version of
Excel 97. I see a few commands that I haven't seen before.

Thanks a lot



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cor Ligthert]

Re: Matching data from table in VB

Chi Man Wong.

This newsgroup is VBNet not VBA.
That is the same as
Java and JavaScript

Cor