Newbe need excel search and copy value

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rolf123

Thread Starter
Joined
Jan 25, 2013
Messages
4
Very new to macro vbs. Need to solve a problem, not become an expert.

Wish to search a second excel data table and find line where 2 cell values are equal to 2 cell values in first tables line and then copy the 3:d value in table 2 to table 1 line (1 line table 1 search all lines in table 2, then 2 line table 1 search all lines in table 2 etc.)

Table 1 is a set of deliveries from-to, table 2 distance table with the distance between from and to. Data tables can during run be copied in same spreadsheet (different columns)

Tryed this (for value in columns p-u), did not compile due to errors in code:

Sub loop1()
Dim I As Integer
Dim J As Integer
For I = 1 To 10
For J = 1 To 10
If Cells(x, 16).Value Like Cells(y, 19).Value And Cells(x, 17).Value Like Cells(y, 20).Value Then Cells(y, 21).Copy Cells(x, 18).Paste
Next
Next
End Sub

Thankful for any help
 
Joined
Jun 29, 2012
Messages
518
Welcome to the Forum, Rolf123. Can you please tell us what version of OS and Excel you are using, and using the Go Advanced option below attach a dummy example of the workbook in question? (No Sensitive Data)
 
Joined
Jun 29, 2012
Messages
518
Rolf - not following you exactly, I think, but looks like if, for example, D20 = E20 and f20 = G 20 then G 20 = B20 or "" (nothing). If that sounds close, I tested just placing 9 in b20, 1 in d20, 1 in e20, 2 in f20 and 2 in G20. If D20& e20 match AND f20&G20 Match, the n from B20 is placed in G20 with the formula
=IF(AND(D20=E20,F20=G20),B20,"")
With 16000 lines, a formula in the destination cells may be quicker, and etaf (who posts here) is awesome with these, but even with a macro, I am mostly checking exactly what you are comparing to what you are comparing to where you want the answers. My fault, I am simply not following your question.
 

rolf123

Thread Starter
Joined
Jan 25, 2013
Messages
4

Hi 20_2_Many,

Thanks for answer. I shouldtry to explain better.

This is the problem: I wantto compute our CO2 footprint, that is how much CO2 emissions we are responsiblefor. To do this I need to add the distance that each transport has traveled tothe main database. The main database consists of lines with transport information,every line is a transport from point A to point B. In every line there isinformation on starting point and end point in form of a 4 digit code. Unfortunatelythe database do not store the travel distance. But, I have access to a seconddatabase where the transport distance between 2 locations is stored (3 cellsper line, code for starting point, code for end point, distance in km).

I need to compare everyline in the main database with every line in the entire distance table and whenthe search finds the right line (cells) (point A = point A, point B = point B) thencopy the distance value from the line (cell 3) in the distance table to themain table. Data in the 2 tables are not on the same lines (ex. Line 345 inmain table could find a match at line 1254 in the distance table).

I have tried severaldifferent ways to do it in Macro but I have to limited knowledge of Macro. Ican´t get any macro to start due to syntax error every time I try.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hi, one more that joins is :)
I cannot figure out whihc column(s) in table one are the travel codes.

Table 2 here is I hope not in the same sheet?
Could you indicate whihc column is the point A and which column is poitn B in Table 1?

Table 2 is the translation tabel to get the Kmts?
 

rolf123

Thread Starter
Joined
Jan 25, 2013
Messages
4
Hi Keebellah, thanks for answering.

Column N= point 1, column W= point2 in example.
The value in column distance should be copied to the right line in column add copy data.


Yes I put table 2 in same sheet. Do not need to be in same sheet. Can be cut and paste to other sheet or excel.

Brgds
Rolf

Hi, one more that joins is :)
I cannot figure out whihc column(s) in table one are the travel codes.

Table 2 here is I hope not in the same sheet?
Could you indicate whihc column is the point A and which column is poitn B in Table 1?

Table 2 is the translation tabel to get the Kmts?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Well this helps.
I'm no good in formulas but I can write a function for it if it's okay with you, you can then just place the function in the column where you want the distance, pass the two points as parameters and it should work.

If this is okay with you'll I'll try and put it together during the weekend.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top