1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Newbe need excel search and copy value

Discussion in 'Business Applications' started by rolf123, Jan 25, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. rolf123

    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
     
  2. 20_2_Many

    20_2_Many

    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)
     
  3. rolf123

    rolf123 Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    4
    Brgds
    Rolf
     

    Attached Files:

  4. 20_2_Many

    20_2_Many

    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.
     
  5. rolf123

    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.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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?
     
  7. rolf123

    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

     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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.
     
  9. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1086750

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice