By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Matching data from table in VB

P: n/a
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.
Nov 21 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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
Nov 21 '05 #2

P: n/a
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!
Nov 21 '05 #3

P: n/a
Chi,
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.


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

http://msdn.microsoft.com/library/de...ntodataset.asp

You have to read both as table of course than set the primary keys
http://msdn.microsoft.com/library/de...rykeytopic.asp

Than set the relations
http://msdn.microsoft.com/library/de...tionstopic.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
Nov 21 '05 #4

P: n/a
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!
Nov 21 '05 #5

P: n/a
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
Nov 21 '05 #6

P: n/a

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!
Nov 21 '05 #7

P: n/a
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.table(1))
///
Than you can while you go through your first table do something like this
\\\
for i as integer = 0 to ds.tables(0).rows.count-1
dv.rowfilter = "Key = ' & ds.tables(0).rows.(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 myfirstfieldInTheRow as string = dv(y)("myfirstfield").ToString
next
next
///

I hope this helps?

Cor
Nov 21 '05 #8

P: n/a
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!
Nov 21 '05 #9

P: n/a
Chi Man Wong.

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

Cor
Nov 21 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.