excel - Selecting range based on cells and return the adjacent cell -


greeting, trying find function formula or vba macro solve problem. appreciate support , in matter. have list in column c can see , specific cell between range of data based on range selecting in case numbers in column f. example: select cells between 1000 , 2000 specific cell called “ apple” in g2 return adjacent cell using offsets formula d4 “ red” solution right table data filled based on range specified. thank in advance support. enter image description hered

the code isn't dynamic works if columns table static.

dim row byte, lastrow byte dim currentnumber long dim currenttext string  activeworkbook.sheets(1)     lastrow = .cells(.rows.count, "b").end(xlup).row     row = 2 lastrow         if not (isempty(.cells(row, 2)))             if (isnumeric(.cells(row, 2)))                 currentnumber = .cells(row, 2)             elseif (len(.cells(row, 2) > 0))                 currenttext = .cells(row, 2)                 select case currentnumber                     case 1000                         if strcomp(currenttext, "apple", vbtextcompare) = 0                             .cells(3, 6) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "banana", vbtextcompare) = 0                             .cells(3, 7) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "orange", vbtextcompare) = 0                             .cells(3, 8) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "mobile", vbtextcompare) = 0                             .cells(3, 9) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "car", vbtextcompare) = 0                             .cells(3, 10) = .cells(row, 2).offset(0, 1)                         end if                     case 2000                         if strcomp(currenttext, "apple", vbtextcompare) = 0                             .cells(4, 6) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "banana", vbtextcompare) = 0                             .cells(4, 7) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "orange", vbtextcompare) = 0                             .cells(4, 8) = .cells(row, 2).offset                         elseif strcomp(currenttext, "mobile", vbtextcompare) = 0                             .cells(4, 9) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "car", vbtextcompare) = 0                             .cells(4, 10) = .cells(row, 2).offset(0, 1)                         end if                     case 5000                         if strcomp(currenttext, "apple", vbtextcompare) = 0                             .cells(5, 6) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "banana", vbtextcompare) = 0                             .cells(5, 7) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "orange", vbtextcompare) = 0                             .cells(5, 8) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "mobile", vbtextcompare) = 0                             .cells(5, 9) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "car", vbtextcompare) = 0                             .cells(5, 10) = .cells(row, 2).offset(0, 1)                         end if                     case 7000                         if strcomp(currenttext, "apple", vbtextcompare) = 0                             .cells(6, 6) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "banana", vbtextcompare) = 0                             .cells(6, 7) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "orange", vbtextcompare) = 0                             .cells(6, 8) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "mobile", vbtextcompare) = 0                             .cells(6, 9) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "car", vbtextcompare) = 0                             .cells(6, 10) = .cells(row, 2).offset(0, 1)                         end if                     case 10000                         if strcomp(currenttext, "apple", vbtextcompare) = 0                             .cells(7, 6) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "banana", vbtextcompare) = 0                             .cells(7, 7) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "orange", vbtextcompare) = 0                             .cells(7, 8) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "mobile", vbtextcompare) = 0                             .cells(7, 9) = .cells(row, 2).offset(0, 1)                         elseif strcomp(currenttext, "car", vbtextcompare) = 0                             .cells(7, 10) = .cells(row, 2).offset(0, 1)                         end if                 end select             end if         end if     next row end

Comments

Post a Comment

Popular posts from this blog

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more