Excel VBA - Populate a column on one sheet with values from another sheet based on 3 criteria (complicated IF AND related VBA with wildcards) -

-

i new @ excel vba , despite efforts cannot seem find similar example online use solution issue.

i creating table of data related inventory of automobiles. workbook have set has 2 tabs. first labeled "feeder", , contains table of hardcoded inputs (automobile values). second labeled "sheet1" , contains raw data inventory. sheet 1 requires automobile values in column "i". goal set workbook column labeled "values" within "sheet1", autopopulated click of button value inputs feeder sheet. tricky part (for me) values based on 1) automobile type (i.e. sedan/ pickup/ etc.), 2) color (different colors have different values), , 3) manufacture year. approaching @ first if , statement, thought creating macro more efficient route take.

i have working list, many more automobile types go (400+ total). if @ thsi stage can [hopefully] figure out rest.

your appreciated.

screen shots here: feeder table , sheet1 inventory list

my code:

sub valuefill() dim x integer x = 3 range("a" & rows.count).end(xlup).row if ucase(sheets("sheet1").range("a" & x).text) = "pickup*" , ucase(sheets("sheet1").text("c" & x).value) = "red*"         range("i" & x).formula = application.worksheetfunction.index(sheets("feeder").range("c" & rows.count).end(xlup).row, application.worksheetfunction.match(sheets("sheet1").range("f" & x), sheets("feeder").range("b" & rows.count).end(xlup).row, 0), 1) elseif ucase(sheets("sheet1").range("a" & x).text) = "pickup*" , ucase(sheets("sheet1").text("c" & x).value) = "blue*"         range("i" & x).formula = application.worksheetfunction.index(sheets("feeder").range("d" & rows.count).end(xlup).row, application.worksheetfunction.match(sheets("sheet1").range("f" & x), sheets("feeder").range("b" & rows.count).end(xlup).row, 0), 1) elseif ucase(sheets("sheet1").range("a" & x).text) = "sedan*" , ucase(sheets("sheet1").text("c" & x).value) = "red*"         range("i" & x).formula = application.worksheetfunction.index(sheets("feeder").range("e" & rows.count).end(xlup).row, application.worksheetfunction.match(sheets("sheet1").range("f" & x), sheets("feeder").range("b" & rows.count).end(xlup).row, 0), 1) elseif ucase(sheets("sheet1").range("a" & x).text) = "sedan*" , ucase(sheets("sheet1").text("c" & x).value) = "blue*"         range("i" & x).formula = application.worksheetfunction.index(sheets("feeder").range("f" & rows.count).end(xlup).row, application.worksheetfunction.match(sheets("sheet1").range("f" & x), sheets("feeder").range("b" & rows.count).end(xlup).row, 0), 1)  'i keep "elseif-ing" each combination of auto type , color, index match year...  else: range("i" & x).text = "error"  end if next  end sub

my knee-jerk reaction use 2 different functions, since color , type interdependent: 1 handling year , 1 handling type , color of vehicle. like

sub valuefill()    car = 1 last 'this loop on cars in sheet1      color = type_color(car)      year = get_year(car)      price = sheets("feeder").cells(year, color).value function get_year(car)      'gets year value give car , returns corresponding row        number of year, i.e car year 2009 row 10 in feeder   end function function type_color()    'will first type convert based on color    'gets type of car , returns left column index type          'i.e. type = pickup column index = 3  (column c)     if color not same column value above         offset         'i.e. blue pickup column index + 1 -> 4   end function

Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more