arrays - Working with multiple subs in VBA -
i cant seem code display date stock @ waltech exceeded searched price. (the dates listed in column , prices listed in column b) right code giving me problem message box. can me?
sub records() dim searchprice currency 'get price call other sub searchprice = inputbox("enter price") call recordhigh1 end sub sub recordhigh1() dim stocks() string dim dt() string dim nstock integer dim integer 'capture stocks , dates , put them in 2 seperate arrays wsdata.range("a2") nstock = range(.offset(1, 0), .end(xldown)).rows.count redim stocks(1 nstock) redim dt(1 nstock) = 1 nstock stocks(i) = .offset(i, 0).value dt(i) = .offset(i, 1).value next end 'loop through arrays find date stock exceeds searchprice range("a2") = 1 nstocks if .offset(i, 1).value > searchprice msgbox "the first date waltech stock price exceeded " & stocks(i).value & " " & dt(i).value else msgbox "waltech stock has not exceeded price" end if next end end sub
you must pass searchpass variable between subs
in sub records type:
call recordhigh1(searchprice) in sub recordhigh1 type:
sub recordhigh1(searchprice currency) other sub has many other flaws, both of syntactic , logical type
here follows version less possible modifications initial code:
sub records() dim searchprice currency 'get price call other sub searchprice = inputbox("enter price") call recordhigh1(searchprice)'<~~ pass sub variable price search end sub sub recordhigh1(searchprice currency)'<~~ have sub accept parameter of currency type make comparisons dim stocks() string dim dt() string dim nstock long '<~~ better use long type instead of integer dim long '<~~ better use long type instead of integer 'capture stocks , dates , put them in 2 seperate arrays 'with wsdata.range("a2")'<~~ wsdata not defined. or public variable activesheet.range("a3") '<~~ start "a3" if data begin there nstock = .range(.cells, .end(xldown)).rows.count redim stocks(1 nstock) redim dt(1 nstock) = 1 nstock stocks(i) = .offset(i - 1, 0).value '<~~ use i-1 offset range first cell dt(i) = .offset(i - 1, 1).value '<~~ use i-1 offset range first cell next end 'loop through arrays find date stock exceeds searchprice dim priceexceeded boolean activesheet.range("a2") = 1 nstock if .offset(i, 1).value > searchprice '<~~ @ first occurrence of price higher 1 passed limit... priceexceeded = true '<~~ ...then mark found it... exit '<~~ ... end exit loop end if next end if priceexceeded '<~~ if occurrence of price higher 1 passed has been marked... msgbox "the first date waltech stock price exceeded " & searchprice & " " & stocks(i) & " " & dt(i) '<~~ ...then else msgbox "waltech stock has not exceeded" & searchprice '<~~ ...otherwise there wasn't end if end sub here's more concise , optimized (and commented) version
sub records() dim searchprice currency 'get price call other sub searchprice = inputbox("enter price") call recordhigh1(searchprice) '<~~ pass sub variable price search end sub sub recordhigh1(searchprice currency) '<~~ have sub accept parameter of currency type make comparisons dim stocks variant, dt variant '<~~ declare arrays variant exploit possibility of filling them ranges dim long '<~~ better use long type instead of integer 'capture stocks , dates , put them in 2 seperate arrays 'with wsdata.range("a2")'<~~ wsdata not defined. or public variable activesheet.range("a3") '<~~ start "a3" if data begin there stocks = application.transpose(.range(.cells, .end(xldown))) '<~~ fill stocks array in single statement dt = application.transpose(.range(.cells, .end(xldown)).offset(, 1)) '<~~ fill dt array in single statement end 'loop through arrays find date stock exceeds searchprice dim priceexceeded boolean = 1 ubound(stocks) if dt(i) > searchprice '<~~ @ first occurrence of price higher 1 passed limit... priceexceeded = true '<~~ ...then mark found it... exit '<~~ ... end exit loop end if next if priceexceeded '<~~ if occurrence of price higher 1 passed has been marked... msgbox "the first date waltech stock price exceeded " & searchprice & " " & format(stocks(i), "dd/mm/yyyy") & " " & dt(i) '<~~ ...then it. since dates numbers, must format them appear in date format else msgbox "waltech stock has not exceeded" & searchprice '<~~ ...otherwise there wasn't end if end sub 