Macro To Lookup Many Codes
Nov 17, 2008
I have an excel workbook with two worksheets. The first worksheet contains a lot of data with more than 20.000 rows and 20 columns. The important part is that there are two different columns (column F and I) containing codes of items. What I would like the macro to do is the following:
write codes of items in the second worksheet column B under each other. I would like the macro to lookup the codes from this column in the first worksheet (column F and I) and if it finds then display in worksheet 2 column C the column in which found it on worksheet1. So basically I write on worksheet2 column B many many codes and then I press a button to do a search and displays next to the items (in column C) if it found the code in worksheet1 column F, or column I or both, or did not found it.
View 2 Replies
ADVERTISEMENT
May 30, 2012
I'm currently creating a formula to look up the date based on the alphanumeric codes, my problem is if it crosses the year, say 2011 & 2012, the lookup will be distorted because codes for jan. 1 2011 & jan. 1 2012 is the same. Our alpha numeric code is A01= Jan. 1.
to elaborate further:
wanted to get the ageing days between todays date and item manufactured date:
today: May 30 2012
when the administrator enters the manufacturing date code of "A01" I have a look up of the code that equates A01=Jan 1. which will compute the difference of May 30 2012-Jan. 1 2012 and give me the Age date of 150. Now, if I enter H1, I wanted to have it look up at the 2011 not 2012 of Aug. 1; is there a look up formula that looks up the date already past (current date below), not the future date of under the same database?
View 5 Replies
View Related
Jan 26, 2012
Reference with the different possible codes used in creating your own conditional formatting in a macro?
I need to conditionally format cells, based on a different column of cells having a particular letter, to have a black up or down arrow.
View 9 Replies
View Related
Jul 18, 2008
I have 4 worksheets in a workbook: “choose security”, “download”, “MC” and “Lending”,
In the choose security” worksheet in CELL A2 is an HEX code, i.e. stock code.
2. In MC worksheet is raw data of all holdings related to various p’folios and securities, the information we need from this worksheet is,
a) p’folios, which are in column A,
b) Holdings which is Col B
c) Security code i.e. stock code which is in Col D
I need a macro or a formula to look at what security code is in “choose security” worksheet CELL A2, then look for this security in worksheet “MC” Col D, once matched, copy the data for this security to worksheet “download” and paste in CELL A3, I only need p’folios and holdings to be copied to “download” so data in Col A and B.
Once data has been copied to worksheet “download”, I then need another macro or formula to repeat step 2, but this time the macro will be looking at worksheet “Lending”,
a) P’folios, which are in Col B
b) Security code is in Col D
c) Holdings is in Col H
Once the macro or formula has found a match of security code, it will then copy the data from Col B and H to download worksheet CELL E3.
View 9 Replies
View Related
May 5, 2006
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
View 3 Replies
View Related
Jan 28, 2014
Working with a macro enabled workbook that has many sheets what would be a good code that would save Sheet1 into a new MACRO enabled workbook so that the 'Buttons' (which are linked to macros) on this specific sheet will be the only macros that will be copied to the new workbook as opposed to the rest of the macros that are found in different sheets?
Here is what I g=have:
[Code] ......
View 11 Replies
View Related
Jul 20, 2014
I have trying for 2 days now to write a macro/VBA that looks up IF the stock codes (i.e., in column A and G) match THEN insert the ID_ISIN from column H in the empty ISIN column C.
or should i use Vlookup?
View 3 Replies
View Related
Oct 2, 2007
I have the following sendmail macro. However I need the "To" section to be the result of a lookup in a worksheet. The specific function that i need it to be is: =VLOOKUP(A837,'Tenacity Jobs'!1:65536,5,FALSE)
How can I incorporate this function so it will insert the result of the lookup into the "To" box ?
View 11 Replies
View Related
Apr 11, 2013
excel/vba/macro as well
I want to make a macro, which can look up a specific cell value in a column and then replace this value only the first time.
E.G.:
value - 25
desired_value - 31
Peers 30
apples 25
oranges 25
I want it to check the values in the range and change the first 25 to 31.
View 1 Replies
View Related
Dec 26, 2008
I need to know how can I code a Lookup formula in a VBA.
In Sheet1 of excel, I have several records of data in column A to E.
I have designed a userform, in which user enters data of column A in a textbox and on click of a button, it writes the textbox value in Sheet2.
I need to code a formula in VBA, so that column B and E of Sheet1 data is written in column B and C respectively of Sheet2 (corresponding data of textbox value).
View 9 Replies
View Related
Feb 5, 2008
The Vlookup portion is already working but I need the value located one row below that one.
here is what I have, which return the value itself, I cant seem to incorporate the offset function ion VBA:
Cells(x + 1, y) = Application.WorksheetFunction.VLookup(TempName, Worksheets("Data").Range("F1:F100"), 1, False)
View 3 Replies
View Related
Feb 26, 2009
I have two columns, zipA and zipB. There are over 7000 rows and I need to calculate the distance between zipA and zipB for each row (so the result should be over 7000 rows of distances (in miles)). I posted this in the programming board because I figured it would require some. I don't have any add-ons.
View 14 Replies
View Related
Jun 9, 2013
Just starting to learn VBA. I have the 2 following codes.Can they be combined into 1 code.I have 2 buttons one say hide rows and the other button says unhide rows....can 1 button be used and when the rows are visible the button says hide rows and when the rows are hidden the button says unhide rows
Sub hiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = True
End Sub
Sub unhiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = False
End Sub
View 6 Replies
View Related
Feb 24, 2007
At my last job we purchased a zip code addin from spheresoft that could give you a distance between two distance ie: =ZipCodeDistance(zip1, zip2)
This worked well but I am now at a new job and wondering if there is any data out there that could help me build my own sheet. I just need California
I have both zip codes now just need the distance, I could sit down, drink a ton of coffee and enter zip codes all night into Mapquest but am hoping for a better way.
View 9 Replies
View Related
Mar 22, 2007
I have a spreadsheet containing data in coloumns A to L.
Where all the codes in column B are the same then a border must be placed around all the codes that are the same i.e the border must start in Column A and end with column L.
provide me with the code the will put borders around the codes in column B that are the same ...
View 9 Replies
View Related
Apr 1, 2008
I have written a VBA code.
I thought I could just save it as an ADD-IN and then have my colleagues to add it, but that does not work. The macro will be stored in a seperate workbook and that is not what I want. I want them to be able to open any workbook and want them to be able to run the macro. I.e. everytime they open a workbook the macro should be available and be ready to run.
View 9 Replies
View Related
Feb 10, 2009
VLOOKUP returns incorrect results when comparing zip codes across worksheets. I think the problem could be caused by 2 things:
1. There are duplicate rows in the lookup_value range because each represents an individual and needs to remain a duplicate to show whether there are multiple individuals from the same zip. In these cases, VLOOKUP only returns the first matching zip from rows containing duplicate zips. (See 07042 in worksheet 1 of the file linked below.)
2. Zip code format is not being treated as a number during sort. Do I need to use a different format during sort? Don't want to lose the leading zeros on the zips.
View 9 Replies
View Related
May 28, 2009
I am looking out for a macro by which i can combine multiple PDF's into one through excel using vba codes.
Please note that:
> all these PDF's are in one folder
> the code needs to be dynamic (i can have an option to combine selected PDF's
> i am working in excel 2007
> i have acrobat 8.0 installed
View 9 Replies
View Related
Oct 2, 2009
I have names in the following format in Column A: e.g.
Mr. Doe SmithSmith DoeJr. Doe SmithJr. Smith DoeMiss Abc XyzMrs Abc XyzXyz Abc
I want a VBA code that would turn all different formats of names listed above as:
Doe Smith
Abc Xyz
I also want another code that would turn above formats as follows: (by the last name)
View 16 Replies
View Related
May 12, 2014
I have the following code that works well with IE but I use chrome almost exclusively. how to tweak this code so that I can pull up a website with my default browser which is chrome instead of IE.
View 2 Replies
View Related
Apr 24, 2014
I currently have a spreadsheet using this formula:
=IF(OR($C9-{17}$E9="",ISTEXT(E9)),"",$E9-LOOKUP(9.99e+307,$E1:$38))
It works great (a tip I got from this site Now, I would like to take the LOOKUP portion & use it in a macro.
What I want to accomplish is to have the macro look at the prior month's data, scroll to the last entry in a column then LOOKUP to get the 1st numeric it finds and copy it to another spreadsheet. I need it to bypass any text it finds, and only accept a numeric for calculation.
View 3 Replies
View Related
Nov 8, 2008
I need a macro to search for certain info on different sheets
combine them and place it on a Report sheet.
This is extensive and complicated so it would be easier if I
emailed the .xls file
If you think you can help, let me email you the file.
View 9 Replies
View Related
Oct 24, 2009
I have a worksheet called "City State Matrix" this contains two columns 1) Location which has the names of cities and 2) State - that has the names of appropriate USA States
I have another worksheet (called Data) and I if I enter the location into Column C of that worksheet I want to auto load the state (based on the data stored in the "City State Matrix" worksheet) into column M (State) of the worksheet called "Data"
View 9 Replies
View Related
Jul 9, 2007
I have search and searched the forums, and maybe am not entering the right info to get an answer, BUT I'm trying to do the following and have no idea where to begin... Workbook2: go to Workbook1, lookup c4:n43 and enter the cell value1 (if any) into I4, Workbook2
THEN
In the row the value1 was found, go to column A, retrieve it's value and return it to D4, Workbook2
THEN
Look at row 1 Workbook1, if the column that value1 was found = Jun-Aug then return 01/2008 to B4, Workbook2.
Of course I need this to go down line by line in Workbook2 to fill in all the info.
View 3 Replies
View Related
Oct 23, 2009
I have a database where each subject has data for 4 days and a number of different codes each day. I am looking to calculate how many different codes they have on day one, how many unique codes they have on each subsequent day and the sum of all these codes.
For example, in the data attached:
Subject 10002 = 2,1,0,0 (3)
Subject 10005 = 3,1,0,0 (4)
Subject 10009 = 3,1,1,0 (5)
View 12 Replies
View Related
Jun 8, 2009
I have the following seven codes used to fill a ListBox with data retrieved form a range (DACNRange).. they are almost the same .. the only difference between them is the range column number (highlighted with red). Instead of these seven similar codes, Can we create a general code replaces them all?
View 2 Replies
View Related
Jan 12, 2009
City, state 29785
city, state 29767
city, state 29719
I need the zip codes to be pushed to the next column, to column 'D' to be exact. I already tried using the "text to columns" function. I selected "delimited" file type and used "2". it pushed the data to the next column everytime there was a 2. The problem is, there are 2's in the middle of the zip code sometimes. Also, it gets rid of all the 2's. Also, I can't use the "fixed width" option because there are some city/states that have more letters in them obviously, so the column is not a perfect width.
View 8 Replies
View Related
Dec 30, 2012
I am just thinking if there is a way to delete or hide all vba codes as I dont want readers to see them.
View 4 Replies
View Related
Apr 18, 2013
I have a spreadsheet that has Zip Codes by Service Days, and then I have another Spreadsheet Zip Code Ranges and the Zone they fall in. I want find the Zip Code associated within that Zone for the Zip Code. I listed some data below.
Zip Code Spreadsheet
State Destination Zip Service Days
NY 12095 2
Zone Chart
From Zip
To Zip
Zones
[Code].....
View 5 Replies
View Related
Jan 3, 2014
How to merge two codes too allow (2) functionalist
I have a rather complex situation that I'm not sure how to handle. First let me provide some background on what I have accomplished thus far.
Background: I have an excel file with two worksheets. Worksheet two, named Data, is where all my data is located. It begins with A1 going through JC4 (A1:JC4). However, for the purpose of my project the data I'm concerned with starts on E1 through JC4. Also, every other column is formatted with ="".
I have used the following sequence of commands, obtained from a website, to make it much simpler to define name ranges:
Select the worksheet "Data"Select cell A1Select the current region using CTRL + SHIFT + 8Press CTRL + G to bring up the Go To dialog boxClick on the "Special" button to bring up the Go To Special dialog boxSelect "Constants" and press the ENTER key (or click the "OK" button)Now define the Named Ranges using your selection by pressing CTRL + SHIFT + F3Excel will ask you to "Create names from values in the:" Make sure only "Top row" is selected, then press the ENTER key (or click the "OK" button)Check that the correct named ranges have been defined by opening the Name Manager with CTRL + F3 While I have defined named ranges with the above steps, this is not the same as a dynamic named range. At least I don't think its the same thing.
I have also protected the worksheet to prevent anyone from changing the formatting. Note: the top row of each column that contains information has been designated as the name for the information that comes after it (see step 8 and 9 above). Now, my first worksheet is where I have created conditional drop down screen by using data validation. The drop down screen are conditionally formatted. Meaning depending on the selection from the drop down screen in cell E27 will determine the list on the drop down screen in cell F27, and so on through cell H27. Furthermore, the drop down items on:
1) Cell E27 (in the first worksheet) are located on the second worksheet in cells E1, G1, I1, K1. Each of those columns have a list of items that fall under them.
The lay out of the data worksheet is too combursome to list out, but I have attached a picture. IF you follow the data you will see some of the information is listed in two cells, for conditional formatting purposes. I have also attached a picture of worksheet 1 with the drop down screens.
Code 1: (allows the selection of multiple items in one cell separated by a comma)
Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
[code]....
Code 2: (allows for manually adding directly to the drop down list a new item that is not on the list. It will then add it to the list after the hard entry directly to the drop down menu). Remember the list will be on the Data worksheet, worksheet #2. The drop down menus are on the first worksheet).
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
[code]....
I have added the first code by right clicking on the worksheet and viewing the code, then adding Code 1. It works beautifully.
What I'm trying to figure out is how to add Code 2 to the mix with out unding Code 1. I'm trying to either combine the codes or insert the second code without disrupting Code 1. Remember I have already created a defined name range in my Data worksheet for all my columns/information. I tried deleting Code 1 and only inserting Code 2 but it didnt work. Code 2 does not work based on how I have designed the formatting. How do I apply Code 2 to what I have done thus far? Can this be done by bypassing a dynamic name range, since I have already assigned a defined name range? I want both codes to apply to Columns E, F, G, and H on the first worksheet.
View 1 Replies
View Related
