# Posts by GoCavs

Re: Code For Multiple Conditions

Thank you for the quick response! I do not think that will work due to the fact that Column G are values (ratings: 1 - 5) that will be input by the user and will not be able to use formulas. Sorry that this is so complicated..any other suggestions?

Re: Code For Multiple Conditions

Quote from junho lee

Hi GoCavs,
Nice to meet you.
S2=CHOOSE(HLOOKUP(\$L\$17,{"Between",">=","<=";1,2,3},2,0),(G2>=\$M\$17)*(G2<=\$O\$17),(G2>=\$M\$17),(G2<=\$M\$17))*((\$L\$19="N")+(\$L\$19="Y")*(L2=0))
M2=IF(S2=1,\$L\$12/SUM(\$S\$2:\$S\$11),0)
Regards,Junho

Junho,

I have another ? for you about the formula. What iabout when (\$L\$19="Y") and (\$L\$17="Select")..meaning I want the formula to only evaluate when Column L = 0. See attached file (compensation example2), tab Ex6

Re: Code For Multiple Conditions

Quote from junho lee

Hi GoCavs,
Nice to meet you.
S2=CHOOSE(HLOOKUP(\$L\$17,{"Between",">=","<=";1,2,3},2,0),(G2>=\$M\$17)*(G2<=\$O\$17),(G2>=\$M\$17),(G2<=\$M\$17))*((\$L\$19="N")+(\$L\$19="Y")*(L2=0))
M2=IF(S2=1,\$L\$12/SUM(\$S\$2:\$S\$11),0)
Regards,Junho

Junho...thank you! This is a real nice formula. I think I can definitely use this even though I was trying to use VB via a command button. Thanks again!!

Re: Code For Multiple Conditions

Quote from Dave Hawley

Tell me in 1 concise sentence what you want.

Sorry Dave, I will try to explain better. I am looking for a button that when selected will look at 2 seperate user-defined conditions to spread a specific number value (L12)proportionately throughout a column. If l12 = 4, then I would like to spread the inverse value (-4.0) according to the conditions in Column N.

Conditions:

1) user defines a >=, <= or Between criteria for Column G in attached example
2) user defines a (Y/N) values to determin it they want to include rows that contain Column L = 0. If "N" is in cell L19, this condtion is ignored.

I know this is more than one sentence but hopefully it helps.

I am new to using VB and was looking for assisting in writing code for a macro button. I have worksheet where I would like to apply a number value proportionately throughout a column bases on user-defined conditions (i.e. >=, <=, Between) from other colunmns. See example below and attached worksheet with further detail.

Col A Col B Col G Col L Col M
01380 Employee 1 5.0 5.5 (0.8)
00304 Employee 2 4.0 - (0.8)
01901 Employee 3 3.8 (1.0) (0.8)
01729 Employee 4 3.0 - (0.8)
01482 Employee 5 3.0 (1.1) (0.8)
00299 Employee 6 2.5 - -
00069 Employee 7 2.5 0.5 -
01783 Employee 8 1.5 - -
02076 Employee 9 1.0 - -
01934 Employee 10 0.5 - -
TOTAL 4.0 -4.0

In the above example, I would like to have the TOTAL value of Column L (4.0) be inversely spread throughout column M based on criteria selected by a user in a section of the spreadsheet.

Criteria:

Column G >= 3 AND all values in Column L = 0; Spread -4.0 (Inverse of cell L12 = 4) in column M for records that match criteria.

I would like to have a command button apply the logic of the conditional criteria set for Columns G & L and spread proportionately ithe value from n Column M.

I added examples of the different criteria and the layout in the file attached. I hope this is something that is not too complex. Any help is very much appreciated.

I am looking to get a formula that count the number of rows that match some
criteria. The sample table is listed below. I would to have a formula
calculate the # of rows in which values in column B (>=3 and <=3.9) along with values in
Column C = 0

ID RATING AMOUNT (formula match)
00304 4.0 (5.0)
00299 2.5 0 x
00069 2.5 2.0
01380 4.0 0 x
01729 3.0 0 x
01783 2.5 0 x
01934 2.0 (1.0)
01901 3.8 (1.0) x
01482 3.0 (1.0) x
02076 2.5 (1.0)

COUNT 3 4 6

B>=3, B<=3.9 = 3 rows
C=0 = 4 rows
Overall = 6 total rows

But if you combine both criteria, there are 6 rows that match. I 'm having a
difficult time figuring our how to write the formula for this. Any
suggestions will be much appreciated.

Re: Have ComboBox Appear On Cell Selection &amp; Double Click

Thank you all for your feedback. My reasoning for using Combobox rather than just using the standard data validation was for the advantages that it provides (font size can be set, more than 8 rows can be displayed, AutoComplete can be enabled). These make it easier from a user perspective. I am going to attach 2 examples that could better explain what I was trying to achieve.

The file DataValComboboxSheet.zip contains combobox that uses Named Ranges on another sheet for the validation list (which I want). But it takes a double-click to activate the combobox.

The other file DataValComboboxClick.zip contains a combobox that automatically activated when you click in a cell with validation. But this uses validation lists that are not not Named Ranges which are located on the same worksheet.

What I was hoping to get was a combobox which I can uses Named Ranges on seperate worksheets along with the ability to have the combobox appear automatically with a single click of the cell with validation.

Hope this better explains my initial question.

I am looking for assisitance in combining the functionality of two seperate combo box code sets. The first combo box code set allows the combo box to automaticially appear when a user Clicks on a cell that contains a data validation list. The second set of code will have a combo box appear when a user Double-clicks on a cell that contains a data validation list. And the code allows for Named Ranges on a seperate worksheet.

I would like to have the functionality of having the combo box to appear when a user Clicks on a cell and not having to Double-click. Also, I would like to have the option to use Named Ranges on a seperate worksheet ("Validation Lists").

I would appreciate any assistance that can be given. Thank you! The code for both sets is listed below.

Data Validation -- Combo box -- Click

Data Validation -- Combo box using Named Ranges

I currently have the code below for a button that will unhide last 175 rows in my worksheet. I now would like to have it hide the rows that are blank in in the range (C7:BL206) looking from the bottom up. For example, if there were values in any of the cells (Columns C up to BL only) in row 190, the macro would only hide rows 191 thru 206. Any suggestions??

Code
1. Private Sub CommandButton2_Click()
3. Range("A32:A206").EntireRow.Hidden = False
5. ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
6. , AllowFormattingColumns:=True, _
7. AllowFormattingRows:=True, AllowFiltering:=True, _
8. AllowUsingPivotTables:=True
9. End Sub

How would I add a YesNo MsgBox to my existing macro that I have set up through a commancd button? I would like the user prompt to ask the following ? ---> "Would you like to move the data sheet forward 1 week?". Then if "Yes" is selected, run the following macro. If "No", end the subroutine. Thanks..pretty new to VBA so I appreciate the help!

Code
1. Private Sub CommandButton6_Click()
2. Range("M7:BL156").Select
3. Selection.Copy
4. Range("L7").Select
5. ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
6. IconFileName:=False
7. Range("I4").Select
8. ActiveSheet.Range("L6").Value = Range("L6").Value + 7
9. End Sub
• ## Automatically Fill Range On Same Row When Any Cell In Specific Range Of Cells Change

Re: Automatically Fill Range On Same Row When Any Cell In Specific Range Of Cells Change

Sorry, I just figured it out Col L = 12. Need more coffee.

Quote from GoCavs

Thank you both for your help. This is great! How would I adjust this formula if I added more columns to left of Column L and now my data ranges goes to ("N6:BN155") from ("L6:BL155")? If i just change the range in the Set isect command line, it still updates back to Column L on my worksheet. I'm sure it's prob something simple I'm not doing.

• ## Automatically Fill Range On Same Row When Any Cell In Specific Range Of Cells Change

Re: Automatically Fill Range On Same Row When Any Cell In Specific Range Of Cells Change

Thank you both for your help. This is great! How would I adjust this formula if I added more columns to left of Column L and now my data ranges goes to ("N6:BN155") from ("L6:BL155")? If i just change the range in the Set isect command line, it still updates back to Column L on my worksheet. I'm sure it's prob something simple I'm not doing.

Re: Event Code -cell Value To Adjust Date Range

Quote from Dave Hawley

Fix the the fact you have painted yourself into a corner, rather than catering to a bad design. Excel is a Spreadsheet Application and when you cannot simply use formulas...you will be constantly catering to the bad design.

Excel Best Practices

Re: Event Code -cell Value To Adjust Date Range

Thanks Tony! One other ?. I already have another Private Sub Worksheet_Change in the sheet's module. And when I added your code in as well, I receive a "Compile Error: Ambigious Name Detected: Worksheet_Change" msg. Can you have multiple sets of codes in the sheet's module or did I do something wrong. Here is an example of what I have:

I have a range (M6:BM6) which consists of week end dates = every Friday (M6 =
5/8/09; BM=5/7/10). I would like to have the range adjusted based on when a
cell (B3) is updated. For example, currently B3=5/5/09 and M6=5/8/09 (week
end date of B3). If B3 was updated to 5/12, I would like to have the range
automatically updated to M6=5/15/09, N6=5/22/09....BM=5/14/10. I could do
this with excel formulas in the sheet but would like to macro code adjust the
values in the range rather than formulas. These ranges are used in other macros and works better with values rather than formulas.

Any suggestions?? Could this be accomplished by a Worksheet Event?

• ## Automatically Fill Range On Same Row When Any Cell In Specific Range Of Cells Change

I have a dataset on worksheet (L7:BL156). I am looking for a way for a user to input a value in cell in the range and then have a macro copy and paste (as values) that value in all of the cells to the left in the range. For example, if the value of 100 is input in cell P9, then 100will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Also, it doesn't matter if there is value already present in a cell to the left of cell being input. It would just override that value.

I have some code (below) that just about works except for one thing. When you input a value into Column L in the range, it copies and pastes the value into the same row in Column K. I would like the value to ONLY stay in Col L when input and updated out the specified range. Any help would be appreciated.