Macro: Find matching DATA, copy correspondently values into another list

Okey guys - please advise me on this.
(There was unfortunately no quick response to my latest problem - and
I begun to swim...
However - that brought me out on deep VBA-water...again - and now need
a life buoy (metaphorical spoken)).

The core of my macro problem is to look for all the values in column A
(Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in
another list placed in column A in sheet "LIST".
When finding the match I will copy correspondent celle.value in column
D / sheet "BOARD" into column B in sheet "LIST" - this will update my
sheet "LIST" column B values from every new input in sheet "BOARD" /
column D

Refrazing Pink Floyd:
"Is there anybody out there" - that in this case will pull me on
shore.

0
Snoopy
11/19/2009 2:45:23 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1364 Views

Similar Articles

[PageSpeed] 27

If you want the value in column B of the List sheet to be the corresponding
value in column D of the Board sheet when there's a match in column A of that
same row, you could use a formula like:

=vlookup(a1,board!a:d,4,false)
and drag down the column as far as you need.

If you could have multiple matches in Board column A for a single value in
column A of List and you want the values added to the bottom of column B
(essentially independent lists in column A and column B of sheet List).

Option Explicit
Sub testme()
    Dim ListWks As Worksheet
    Dim BoardWks As Worksheet
    Dim myCell As Range
    Dim myRng As Range
    Dim Res As Variant
    Dim DestCell As Range
    
    Set ListWks = Worksheets("list")
    Set BoardWks = Worksheets("Board")
    
    With BoardWks
        Set myRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    For Each myCell In myRng.Cells
        Res = Application.Match(myCell.Value, ListWks.Range("A:a"), 0)
        If IsError(Res) Then
            'not found
        Else
            With ListWks
                Set DestCell = .Cells(.Rows.Count, "B").End(xlUp)
                If IsEmpty(DestCell.Value) Then
                    'keep it there
                Else
                    'move down a row
                    Set DestCell = DestCell.Offset(1, 0)
                End If
            End With
            
            DestCell.Value = myRng(Res).Offset(0, 3).Value
        End If
    Next myCell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Snoopy wrote:
> 
> Okey guys - please advise me on this.
> (There was unfortunately no quick response to my latest problem - and
> I begun to swim...
> However - that brought me out on deep VBA-water...again - and now need
> a life buoy (metaphorical spoken)).
> 
> The core of my macro problem is to look for all the values in column A
> (Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in
> another list placed in column A in sheet "LIST".
> When finding the match I will copy correspondent celle.value in column
> D / sheet "BOARD" into column B in sheet "LIST" - this will update my
> sheet "LIST" column B values from every new input in sheet "BOARD" /
> column D
> 
> Refrazing Pink Floyd:
> "Is there anybody out there" - that in this case will pull me on
> shore.

-- 

Dave Peterson
0
Dave
11/19/2009 6:00:05 PM
On 19 Nov, 19:00, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> If you want the value in column B of the List sheet to be the correspondi=
ng
> value in column D of the Board sheet when there's a match in column A of =
that
> same row, you could use a formula like:
>
> =3Dvlookup(a1,board!a:d,4,false)
> and drag down the column as far as you need.
>
> If you could have multiple matches in Board column A for a single value i=
n
> column A of List and you want the values added to the bottom of column B
> (essentially independent lists in column A and column B of sheet List).
>
> Option Explicit
> Sub testme()
> =A0 =A0 Dim ListWks As Worksheet
> =A0 =A0 Dim BoardWks As Worksheet
> =A0 =A0 Dim myCell As Range
> =A0 =A0 Dim myRng As Range
> =A0 =A0 Dim Res As Variant
> =A0 =A0 Dim DestCell As Range
>
> =A0 =A0 Set ListWks =3D Worksheets("list")
> =A0 =A0 Set BoardWks =3D Worksheets("Board")
>
> =A0 =A0 With BoardWks
> =A0 =A0 =A0 =A0 Set myRng =3D .Range("A5", .Cells(.Rows.Count, "A").End(x=
lUp))
> =A0 =A0 End With
>
> =A0 =A0 For Each myCell In myRng.Cells
> =A0 =A0 =A0 =A0 Res =3D Application.Match(myCell.Value, ListWks.Range("A:=
a"), 0)
> =A0 =A0 =A0 =A0 If IsError(Res) Then
> =A0 =A0 =A0 =A0 =A0 =A0 'not found
> =A0 =A0 =A0 =A0 Else
> =A0 =A0 =A0 =A0 =A0 =A0 With ListWks
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Set DestCell =3D .Cells(.Rows.Count, "B")=
..End(xlUp)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 If IsEmpty(DestCell.Value) Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 'keep it there
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Else
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 'move down a row
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Set DestCell =3D DestCell.Offset(=
1, 0)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 End If
> =A0 =A0 =A0 =A0 =A0 =A0 End With
>
> =A0 =A0 =A0 =A0 =A0 =A0 DestCell.Value =3D myRng(Res).Offset(0, 3).Value
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 Next myCell
> End Sub
>
> If you're new to macros:
>
> Debra Dalgleish has some notes how to implement macros here:http://www.co=
ntextures.com/xlvba01.html
>
> David McRitchie has an intro to macros:http://www.mvps.org/dmcritchie/exc=
el/getstarted.htm
>
> Ron de Bruin's intro to macros:http://www.rondebruin.nl/code.htm
>
> (General, Regular and Standard modules all describe the same thing.)
>
>
>
>
>
> Snoopy wrote:
>
> > Okey guys - please advise me on this.
> > (There was unfortunately no quick response to my latest problem - and
> > I begun to swim...
> > However - that brought me out on deep VBA-water...again - and now need
> > a life buoy (metaphorical spoken)).
>
> > The core of my macro problem is to look for all the values in column A
> > (Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in
> > another list placed in column A in sheet "LIST".
> > When finding the match I will copy correspondent celle.value in column
> > D / sheet "BOARD" into column B in sheet "LIST" - this will update my
> > sheet "LIST" column B values from every new input in sheet "BOARD" /
> > column D
>
> > Refrazing Pink Floyd:
> > "Is there anybody out there" - that in this case will pull me on
> > shore.
>
> --
>
> Dave Peterson=96 Skjul sitert tekst =96
>
> =96 Vis sitert tekst =96

Thanks a lot Dave - I will try this out
Best regards Snoopy
0
Snoopy
11/20/2009 7:43:20 AM
Reply:

Similar Artilces:

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Matching transactions in 2005
I upgraded from Money 2004 to Money 2005. I've always had trouble getting transactions to match when they are downloaded from the bank. For instance, I would enter a transaction into Money, and then when it downloaded my statement from the bank it would enter a new transaction instead of matching it to the one that I created. In 2004, you could right-click on the transaction and then choose "match", or something along those lines. I don't see that option in 2005. How do you match duplicate transactions in 2005? I'm using Premium if it matters, and my register view ...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

how to convert lookup values to the "display text"
I'm using an sql code (below) which uses a few lookup fields. Unfortunately in the datasheet view, I get the "bound values" instead of the "display values". How can I change the properties for the these lookup fields so I can see the "display values" from the datasheet view? SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [Form_9_Status] UNION ALL SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [TDY_Status] UNION ALL SELECT [Funding],[Date],[Description],[C...

find action on log file
Hello there I want to use outside tool to find who made some update on table in my server I know that there are many tools for this. But can they do it on simple recovery model? Roy Goldhammer (royg@yahoo.com) writes: > I want to use outside tool to find who made some update on table in my > server > > I know that there are many tools for this. But can they do it on simple > recovery model? No. If you are using the simple recovery model, the contents of the log is wasted away everyonce in a while. Well, if the disk area has not been overwritten...

Find and Replace Footnote Numbering
I often have documents where I need to put formatting before or after the footnote number for every footnote, often for hundreds of footnotes...anyone know how I can do this in one shot? I know I can specify ^f in find and replace, but only in the find portion...it gives an error if you put it in replace. ...

How to delete a set of rows depending on Value
I have two worksheets Worksheet A 27 Columns 1600 Rows. Worksheet B 1 Column 200 Rows I need Worksheet A to look at each cell in Worksheet B, if a cell exists in Worksheet A, then I need the row deleted... Basically I have a list of Grand list of items to do, then a list of items already completed. I need to now remove all entries in the grand list that have been completed. Is this feasible or should I look at using some Unix script. It sounds like you could use VLOOKUP to find out if the value in B exists on A: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel....

'top' is not a valid value for the 'vertical-align' property.
Well, it is, actually, and the resulting .chm file works properly in HTML Help. But it's a bit tedious getting that wrong useless error message from VS2002 on every build. (Well, not every build, it only does it sometimes actually.) Any idea how I can persuade it to stop telling me this? -- Tim Ward Brett Ward Limited - www.brettward.co.uk ...

contact list #13
Good Day, I hope I am in the right area, we are running Exchange 2003 one Server 2003 and have Outlook 03 clients. We have address lists in the public folder an admin and a vendor, these contain contact information on contrators and vendors. In the folder list I can browse down and few and click on a contact to send them an email, but when I open a new email to send and I click on to and what to chose the list from the address book drop down box they are not there. How do I set it up so vendors and Admin contact list appear in the drop down box in the contact list? Thank you David ...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

I need a macro
I would like a macro to do the following: I would place the the cursor in any cell and this macro would give me the average of all the entries to the left if the selected cell. EX: I place the cursor in cell M12. I want this macro on display in cell M12 the average of all the values from Col C12 to Col L12. Note that there may NOT be entries in all the cells in that range. Also the start point would always be col C .Thanks You can do this easily with a worksheet_selection event in the sheet module but you would probably want to restrict to a certain range or columns and rows or it woul...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

Average of absolute values of moving ranges
I'm trying to get the average of the absolute values of a set of data over 8 weeks. Each week is on a seaparate sheet so to capture the moving ranges I've been using the formula below to get my result. Is there an easier way? =AVERAGE(ABS('Week 1'!G2-'Week 2'!G2),ABS('Week 2'!G2-'Week 3'!G2),ABS('Week 3'!G2-'Week 4'!G2),ABS('Week 4'!G2-'Week 5'!G2),ABS('Week 5'!G2-'Week 6'!G2),ABS('Week 6'!G2-'Week 7'!G2),ABS('Week 7'!G2-'Week 8'!G2)) Thanks! Amy The use of t...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Reference / Copy Dynamic Data
Have a worksheet listing products and prices for numerous suppliers. eg.: Supplier--Product---Price ABC-------apple-----1.00 ABC-------orange----1.20 XYZ-------brick-----3.40 XYZ-------cement----0.80 This worksheet will change often. What I would like is to reference this information on other worksheets. I would also split the info onto a worksheet for each supplier. Therefore i will have a worksheet for ABC and for XYZ, and the info in these worksheets will change as the main "index" worksheet changes. eg. Worksheet ABC contains: Product---Price apple-----1.00 orange----1.20 ...

Automatically copy input from one cell to another
After I enter a value in one cell, how can I have it automatically enter it into another cell, within the same worksheet, or into a different worksheet. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com If you enter the value in A1 of sheet1, put this in the other cell in sheet1: =A1 or in another worksheet: =Sheet1!A1 In article <picktr.15c6uy@excelforum-nospam.com>, picktr <<picktr.15c6uy@excelforum-nospam.com>> wrote: > After I enter a value in one cell, how can > I have it automatically enter it into another cell...

How do I find the Data Analysis Plus toolpak for excel
does anyone know where I could find the data analysis plus toolpak. opher32 - > does anyone know where I could find the data analysis plus toolpak. < Start by looking on the CD that was in the back of the textbook. - Mike www.mikemiddleton.com ...

Find Text in a Query
Does anyone know of a way to search thru AC2003 queries to find a text string? For example, I need to find all queries that contain the text "Forms". Thanks for your help! On Sat, 6 Mar 2010 14:10:01 -0800, PeterM <PeterM@discussions.microsoft.com> wrote: >Does anyone know of a way to search thru AC2003 queries to find a text >string? For example, I need to find all queries that contain the text >"Forms". > >Thanks for your help! There might be a way to use the systems tables, but I wasn't able to figure it out. Try: Pu...

Contact oddity and why are there 2 Contact Lists?
Running Win 7, and was receiving emails in WLM with my maiden name listed in the TO:. I found out that I had my maiden name listed in Contacts (I run a high school mailing list here) so I decided to check it out and changed the last name to all caps. Sure enuf the next email I rec'd showed that name. I then deleted the email address connected to my name in the contact list and everything is back to "normal" (whatever that is <G>) Why in the world are there 2 contact lists on my computer......one thru WLM and one thru Windows Contacts? I'm confused. ...

Formula to return non-zero values in a list
I have a list of numbers in a column like so - 2 3 4 1 8 0 0 0 0 What formula would I create to always give me the a SUM of the las couple values before the zero value. The list always ends with a valu other than zero, so in this case, the '8' is the end of the list, and would like to SUM the 3 "last" values, those being 8, 1 and 4. As use this file, the zero values will automatically be updated with ne information, so I need a formula which would constantly reflect thi change -- Message posted from http://www.ExcelForum.com If you don't mind having an extra col...