Posts by OutPutter

Re: Center Active Cell's Contents Across Active Column and One Column Over

Thanks Rob. That works perfectly.

I'm trying to take the date in the active cell and center it across two columns starting with the current column and going to the right one column. Here's all I have so far.

ActiveCell.FormulaR1C1 = "=NOW()"

Thanks in advance...
Jim

I want to simply this formula (resides in Sheet1) without using range names (users are not Excel savy).

Current formula:
=Sheet2!A1+Sheet2!B2+Sheet2!C3+Sheet2!D4

Theoretical simplification that didn't work:
=Sheet2!(A1+B2+C3+D4)

Thanks in advance.

Jim
(Not feeling very Excel savy either)

Re: Detect Outline Level in VBA

Hello Joebbshop,

Many thanks for the help. I've attached a small sample of the data. It doesn't have a row marked Grand Total because it is not generated through the standard Excel Sub Total menu. I tested the following to see if I could set the detail level on just the rows that were not summarized but it failed.

Sub test()
Rows(ActiveCell).ShowDetail = False
End Sub

I'm thinking that it may save time to identify the uncollapsed rows and then summarize them instead of doing all the data at once.

Thanks again for the help.

Jim

Re: Sort Columns Pulldown Menu

When I tried to sort your list using Excel's "Data/Sort/Options/Sort Left To Right" functionality, I received a message that some of the cells are merged cells. Reformat the merged cells and it should work.

Jim

I have spreadsheets that have subtotaled data. In order to quickly select the total rows I will collapse the data so that only the total rows are visible and then select the visible cells only. Since the data can be extensive, it may take several seconds to collapse the data.

I can perform the selection of visible cells in VBA and I can collapse the data in VBA but I want to avoid the wait if possible.

Is there a way to test in VBA if the data is collapsed so I can avoid the wait if it is?

Thanks in advance for any light you can shed on this question.

Jim

How do I construct an If-Then statement that checks the active sheet's outline level?

Re: Using Indirect in a Macro

Still looking for a solution to this thread.

Thanks,
Jim

Re: Using Indirect in a Macro

Here is an example worksheet. I want a macro that will plug in the data validation in the yellow highlighted cells using a relative reference so it will work in any cell that I want.

Thanks,
Jim

Re: Using Indirect in a Macro (no solution)

Didn't work. The macro still crashes

Is there a way to use the INDIRECT function in a macro to set a data validation rule? The following code hangs. Please help..

[vba]
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDIRECT(ActiveCell.Offset(-1,0)),0,0,COUNTA(INDIRECT(ActiveCell.Offset(-1,0)))/2,1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
[/vba]

Thanks in advance

Re: Data Validation of a Dependent List in VBA

Still looking for ideas.

The macro still hangs. Any other ideas?

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDIRECT(ActiveCell.Offset(-1,0)),0,0,COUNTA(INDIRECT(ActiveCell.Offset(-1,0)))/2,1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
16 Hours Ago 14:45

Re: Data Validation of a Dependent List in VBA

The macro still hangs. Any other ideas?

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDIRECT(ActiveCell.Offset(-1,0)),0,0,COUNTA(INDIRECT(ActiveCell.Offset(-1,0)))/2,1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

The following code has a reference to cell "G20". I want to replace the reference to cell "G20" with an expresion that will read the cell to the left of the selected cell. That way when I execute the code the validation will automatically check the contents of the cell to the left for the list. Any ideas?

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDIRECT(G20),0,0,COUNTA(INDIRECT(G20))/2,1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With