excel vba - How to loop through a range while already looping through another range -

-

good day

i'm trying loop through range , while in range need loop through another.

although work, it'll give idea of i'm trying achieve:

    ' select cell m5, *first line of data*.     range("m5").select     ' set loop stop when empty cell reached.     until isempty(activecell)         strreg = activecell.value         dtedate = activecell.offset(0, 1).value         lngrate = activecell.offset(0, 2).value         range("g5").select         until blnfound or isempty(activecell)             if activecell.value = strreg                 if activecell.offset(0, -3) <= dtedate , activecell.offset(0, -2) >= dtedate                      blnfound = true                      '... add more logic here                 end if             end if             ' step down 1 row present location.             activecell.offset(1, 0).select         loop         ' step down 1 row present location.         activecell.offset(1, 0).select     loop

thanks taking time.

rob

here's quick implementation of loop starting off file looks this:

start

option explicit sub loopthroughvehicles()  dim mysheet worksheet dim costrange range, invoicerange range, _     foundrange range, vehicle range dim lastrow long, costvehiclecol long, _     invoicevehiclecol long dim costdate date, startdate date, _     enddate date  'assign variables , ranges easy reference costvehiclecol = 13 'column m invoicevehiclecol = 7 'column g set mysheet = thisworkbook.worksheets("sheet1") mysheet     lastrow = .range("m" & .rows.count).end(xlup).row end set costrange = range(mysheet.cells(lastrow, costvehiclecol), mysheet.cells(5, costvehiclecol)) mysheet     lastrow = .range("g" & .rows.count).end(xlup).row end set invoicerange = range(mysheet.cells(lastrow, invoicevehiclecol), mysheet.cells(5, invoicevehiclecol))  'search each vehicle each vehicle in costrange     set foundrange = invoicerange.find(what:=vehicle.value, lookat:=xlwhole, matchcase:=false)     'if vehicle found, assign dates , compare     if not foundrange nothing         costdate = vehicle.offset(0, 1).value         startdate = foundrange.offset(0, -3).value         enddate = foundrange.offset(0, -2).value         if costdate >= startdate , costdate <= enddate             'do stuff if cost date in date range             msgbox ("cool, " & vehicle.value & " in date range!")         else             'do other stuff if cost date not in date range             msgbox ("uh oh, " & vehicle.value & " not in date range!")         end if     end if next vehicle  end sub

Comments

Post a Comment

Popular posts from this blog

windows - Single EXE to Install Python Standalone Executable for Easy Distribution -

-
i used pyinstaller create standalone exe file pyqt project. did not use single file option because exe created single file option takes long open. therefore, pyinstaller created dist folder contains bunch of files including program.exe file. create setup.exe file place contents of dist folder in program directory , create shortcut in start menu , on desktop. want super simple user. maybe setup.exe files when download program cnet. found inno-setup, looks promising. however, not know if there special considerations because program standalone program create python/pyqt program. thanks! have experience task? there program using task not know about? inno-setup or nsis easiest use. tell them files include , put them , create nice installer you. wrote short tutorial on experiences using innosetup might find helpful: http://www.blog.pythonlibrary.org/2008/08/27/packaging-wxpymail-for-distribution/ note tutorial based around wxpython app wrote, concepts same.
Read more

c# - Access objects in UserControl from MainWindow in WPF -

-
i have simple xaml, how can change text property of textblock in usercontrol1 mainwindow ? <window x:class="refactorxaml.mainwindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:refactorxaml="clr-namespace:refactorxaml" title="mainwindow" height="350" width="525"> <grid> <grid.rowdefinitions> <rowdefinition height="100"></rowdefinition> <rowdefinition height="100"></rowdefinition> </grid.rowdefinitions> <grid> <refactorxaml:usercontrol1></refactorxaml:usercontrol1> </grid> <grid grid.row="1"> <button>change text</button> </grid> </grid> </window> <usercon...
Read more

javascript - How to name a jQuery function to make a browser's back button work? -

-
i'm trying write function ajaxyfy web site, works perfect, except function button. that's have now: function loading() { if (typeof history.pushstate !== "undefined") { var historycount = 0; $('.menu-item, .logo').on('click','a',function(e){ e.preventdefault(); $(this).data('clicked', true); if ($(this).is('.logo a')) { var homepage = $(this).attr('href'); function1(homepage); history.pushstate(null, null, homepage); } else if ($(this).is('.projects a')) { var projects = $(this).attr('href'); function2(projects); history.pushstate(null, null, projects); } else { var pages = $(this).attr('href'); function3(pages); history.pushstate(null, null, pages); ...
Read more