inputbox help

  • Thread starter Thread starter lothario
  • Start date Start date
L

lothario

Hi,

Look at the attached screenshot.
Can you show me the inputbox code that:

a. Asks for a "Model" and a "Year".

b. Selects all the rows that contain that model. (Note: *contains*
the text)

c. And from those selected rows it further sub-selects rows where
the "Year" is greater than or equal to the "FROM Year" AND
the "Year" is less than or equal to the "To Year".

d. Finally, it copies this short list (entire rows) to the "results
sheet
starting at cell B3. The "results" sheet is in the same workbook.

So if you entered "ks" for the Model and "94" for the Year then yo
would get the result:

M3 , ks5 , 91 , 94 , 0 , , ALP1608 , 79.46 , ...
M3 , ks5 , 91 , 95 , 0 , , AF1632 , 199 , ...
M3 , ks5 , 91 , 95 , 0 , , AH1634 , 312.63 , ...
M3 , ks5 , 91 , 95 , 0 , , ALP1646 , 44.58 , ...
M3 , ks5 , 91 , 95 , 0 , , ALP1647 , 44.58 , ...


Thanks
 
The screenshot did not attach so here is the data:

-----------------------------------------------------------------------
MANUFACTURER , Model , FROM YEAR , To Year , On Hand ,
QUANTITY , PART NUMBER , LIST Price , DESCRIPTION
M3 , ww9 , 91 , 95 , 0 , , SIL7343 , 190.59 , …
M3 , pq3 , 86 , 90 , 0 , , SIL7341 , 0 , …
M3 , ks5 , 91 , 93 , 0 , , A16393 , 299.99 , …
M3 , ks5 , 91 , 94 , 0 , , ALP1608 , 79.46 , …
M3 , ks5 , 91 , 95 , 0 , , AF1632 , 199 , …
M3 , ks5 , 91 , 95 , 0 , , AH1634 , 312.63 , …
M3 , ks5 , 91 , 95 , 0 , , ALP1646 , 44.58 , …
M3 , ks5 , 91 , 95 , 0 , , ALP1647 , 44.58 , …
M5 , uye23 , 92 , 96 , 0 , , MHP286 , 62.72 , …
M5 , uye23 , 92 , 98 , 0 , , MLP2623 , 59.8 , …
M5 , uye23 , 92 , 98 , 2 , , MH282 , 121.78 , …
M5 , uye23 , 92 , 98 , 0 , , MLP2732 , 23.58 , …
M5 , diq82 , 84 , 91 , 0 , , MF230 , 50.14 , …
M5 , diq82 , 84 , 91 , 0 , , MF231 , 50.14 , …
M5 , diq82 , 84 , 91 , 0 , , MH235 , 150.68 , …
M5 , 784tvm , 89 , 95 , 0 , , MGS2575 , 94.72 , …
M6 , rhnb3 , 91 , 96 , 0 , , KHP5173 , 76.88 , …
M6 , rhnb3 , 91 , 96 , 0 , , KLP5101 , 43.48 , …
M6 , rhnb3 , 91 , 96 , 0 , , KLP51011 , 43.48 , …
M8 , lmn9 , 89 , 90 , 0 , , GR5679 , 72.14 , …
M9 , bcra45 , 85 , 94 , 0 , , SIL2058 , 195.63 , …
M9 , gqw90 , 95 , 01 , 5 , , VF6352 , 46.12 , …
M9 , gqw90 , 95 , 01 , 5 , , VF6353 , 46.12 , …
M9 , gqw90 , 95 , 01 , 2 , , VH63561 , 83.02 , …
M9 , gqw90 , 95 , 01 , 5 , , VGR63572 , 72.77 , …
M9 , gqw90 , 95 , 01 , 0 , , VLP6362 , 13.05 , …
M9 , gqw90 , 95 , 01 , 0 , , VLP6363 , 13.05 , …
 
Have you looked at built in capabilities that can do this such as Autofilter
and Advanced filter under the Data menu or creating a Pivot Table report
under the data menu as well.

--
Regards,
Tom Ogilvy

lothario said:
Hi,

Look at the attached screenshot.
Can you show me the inputbox code that:

a. Asks for a "Model" and a "Year".

b. Selects all the rows that contain that model. (Note: *contains*
the text)

c. And from those selected rows it further sub-selects rows where
the "Year" is greater than or equal to the "FROM Year" AND
the "Year" is less than or equal to the "To Year".

d. Finally, it copies this short list (entire rows) to the "results"
sheet
starting at cell B3. The "results" sheet is in the same workbook.

So if you entered "ks" for the Model and "94" for the Year then you
would get the result:

M3 , ks5 , 91 , 94 , 0 , , ALP1608 , 79.46 , ...
M3 , ks5 , 91 , 95 , 0 , , AF1632 , 199 , ...
M3 , ks5 , 91 , 95 , 0 , , AH1634 , 312.63 , ...
M3 , ks5 , 91 , 95 , 0 , , ALP1646 , 44.58 , ...
M3 , ks5 , 91 , 95 , 0 , , ALP1647 , 44.58 , ...


Thanks.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
I understand that autofilter and advanced filter is relevant here.

I cannot use autofilter or advanced filter because
this spreadsheet is used about 50 times a day and it is very
inconvenient to click thru several menu items to do the
same singular task. It becomes a lot easier if I incorporate VBA
code behind a button and have the user click the
button just once to trigger multiple successive events.
As you can see I need multiple criteria to be satisfied.
So VBA code would be the fastest. (I think.)

I appreciate your help.
 
Advanced filter and Autofilter can both be implemented in code.

--
Regards,
Tom Ogilvy

lothario said:
I understand that autofilter and advanced filter is relevant here.

I cannot use autofilter or advanced filter because
this spreadsheet is used about 50 times a day and it is very
inconvenient to click thru several menu items to do the
same singular task. It becomes a lot easier if I incorporate VBA
code behind a button and have the user click the
button just once to trigger multiple successive events.
As you can see I need multiple criteria to be satisfied.
So VBA code would be the fastest. (I think.)

I appreciate your help.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top