Hello,
Being unfamiliar with VBA, this should be a fairly easy solution. I am on a work computer and would rather not install an HTML maker so I will try to give as detailed a description as possible.
I am using Microsoft Excel 2007 and the person that this spreadsheet is for also uses Excel 2007.
Basically what I have so far is this:
Now, in range D2:O2, I have a data validation set up so that you can select one of twelve months. What the macro I have now does is automatically sets it for the calender year.
Below this range, in the range D3:O19, I have set up an HLOOKUP formula to pull values from another worksheet, so that for example if January is entered in Cell D2, D3:D19 will show numbers, with each row corresponding to a category of expenditure.
Now what I am wanting to do with this macro is, after the macro fills in D2:O2 automatically, I want it to check through each column in row 3 through row 19, and sum the range, and clear the corresponding cell in row 2 if the total sum of the range is equal to zero.
For example, say I run the macro and only January and February have been filled in on the other sheet. January would be in cell D2, and subsequently D3:D19 would be summed and determined to be greater than zero and therefore are left alone. Same for February but in the E column. However, once the macro reaches the F column, it should sum F3:F4 and realize that the sum of the values in this range is zero and thus delete the value from F2, and so forth.
I'm sure there is an easy way to do this but again I am unfamiliar with VBA and I am looking for an easy solution.
Being unfamiliar with VBA, this should be a fairly easy solution. I am on a work computer and would rather not install an HTML maker so I will try to give as detailed a description as possible.
I am using Microsoft Excel 2007 and the person that this spreadsheet is for also uses Excel 2007.
Basically what I have so far is this:
Code:
Sub autodetectreset()
'
' autodetectreset Macro
' Should auto detect months with values and input them into the table.
'
'
Range("D2").Select
ActiveCell.FormulaR1C1 = "January"
Range("E2").Select
ActiveCell.FormulaR1C1 = "February"
Range("F2").Select
ActiveCell.FormulaR1C1 = "March"
Range("G2").Select
ActiveCell.FormulaR1C1 = "April"
Range("H2").Select
ActiveCell.FormulaR1C1 = "May"
Range("I2").Select
ActiveCell.FormulaR1C1 = "June"
Range("J2").Select
ActiveCell.FormulaR1C1 = "July"
Range("K2").Select
ActiveCell.FormulaR1C1 = "August"
Range("L2").Select
ActiveCell.FormulaR1C1 = "September"
Range("M2").Select
ActiveCell.FormulaR1C1 = "October"
Range("N2").Select
ActiveCell.FormulaR1C1 = "November"
Range("O2").Select
ActiveCell.FormulaR1C1 = "December"
End Sub
Now, in range D2:O2, I have a data validation set up so that you can select one of twelve months. What the macro I have now does is automatically sets it for the calender year.
Below this range, in the range D3:O19, I have set up an HLOOKUP formula to pull values from another worksheet, so that for example if January is entered in Cell D2, D3:D19 will show numbers, with each row corresponding to a category of expenditure.
Now what I am wanting to do with this macro is, after the macro fills in D2:O2 automatically, I want it to check through each column in row 3 through row 19, and sum the range, and clear the corresponding cell in row 2 if the total sum of the range is equal to zero.
For example, say I run the macro and only January and February have been filled in on the other sheet. January would be in cell D2, and subsequently D3:D19 would be summed and determined to be greater than zero and therefore are left alone. Same for February but in the E column. However, once the macro reaches the F column, it should sum F3:F4 and realize that the sum of the values in this range is zero and thus delete the value from F2, and so forth.
I'm sure there is an easy way to do this but again I am unfamiliar with VBA and I am looking for an easy solution.