Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a macro
I use a macro to change information in csv files before importing into a
l;ivestock program. The csv files always contains the same columns of info but vary in the number of rows of information. Part of my macro is to look at column A and depending on what each cell contains place a letter in the coreesponding cell column G. The formula is below but all that is happening is that it fixes cellA1 but doesn't look at the other entries in column A =LOOKUP(A:A,{"bull","cow","steer","weaner"},{"M"," F","S","U"}) What am I doing wrong thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a macro
macro may not be required
A1 to B4 contains bull m cow f steer s weiner u in G1 type =VLOOKUP(A1,A1:B4,2,FALSE) and <enter and copy G1 to g2 g3 and g4 perhaps better solutiion is avialable. "Anthony" wrote in message ... I use a macro to change information in csv files before importing into a l;ivestock program. The csv files always contains the same columns of info but vary in the number of rows of information. Part of my macro is to look at column A and depending on what each cell contains place a letter in the coreesponding cell column G. The formula is below but all that is happening is that it fixes cellA1 but doesn't look at the other entries in column A =LOOKUP(A:A,{"bull","cow","steer","weaner"},{"M"," F","S","U"}) What am I doing wrong thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a macro
Thanks for providing more insight into the problem, and you are almost there,
but you need to use absolute references, or better to use a defined name table in an out of the way place like using another sheet, so you don't have to refer to it as sheet2!$A$1:$B$4 Place your table data in A1:B4 of the other sheet, then with the range A1:B4 selected type "type_table" without the quotes into the name box at the left of the formula bar. Or use Insert, name, define to add, change, delete. The use of A:A to represent the cell in column A on the same row, is too hard to figure out where it will work in Excel and where it won't work, suggest avoiding that =VLOOKUP(A1,type_table,2,FALSE) False indicates unsorted table, but the reason for using is to require an exact match; and if not an exact match you will get #N/A! if not in table More information on VLOOKUP Worksheet Function and examples in http://www.mvps.org/dmcritchie/excel/vlookup.htm The LOOKUP function will return the exact hit or to a previous entry (the largest value that is less than or equal to lookup_value) android would return #N/A! since it is before first entry Bull male would return F (for Female) because it is between "female" and "stud" in the table zebra would return U (for weaner) because it is after last entry "weaner" in table. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "R..VENKATARAMAN" wrote in message ... macro may not be required A1 to B4 contains bull m cow f steer s weiner u in G1 type =VLOOKUP(A1,A1:B4,2,FALSE) and <enter and copy G1 to g2 g3 and g4 perhaps better solutiion is avialable. "Anthony" wrote in message ... I use a macro to change information in csv files before importing into a l;ivestock program. The csv files always contains the same columns of info but vary in the number of rows of information. Part of my macro is to look at column A and depending on what each cell contains place a letter in the coreesponding cell column G. The formula is below but all that is happening is that it fixes cellA1 but doesn't look at the other entries in column A =LOOKUP(A:A,{"bull","cow","steer","weaner"},{"M"," F","S","U"}) What am I doing wrong thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |