vba formulas

[tjharlan]

Hi,

I have a macro that inserts a formula. Once the formula is inserted, I want to use autofill to "copy" the formula down. My problem is that the formula is not copying down correctly (it keeps the "n2" rather than "n3", "n4", etc.).

'code blows up
Formula1:="=INDIRECT(SUBSTITUTE(n2,"" "",""_""))"

'compile error
Formula1:="=INDIRECT(SUBSTITUTE("n2","" "",""_""))"

'works, but, when i copy and autofill formula down, the value stays as "n2"...when I want it to go to "n3"..."n4"..."n5"...etc.
Formula1:="=INDIRECT(SUBSTITUTE(""n2"","" "",""_""))"

Thanks for any help with the proper syntax.

T.J.

 

[tjharlan]

Thanks, Norie.

In the actual worksheet, the N2 was enclosed with quotations. But, with the quotations, dragging the formula down kept the N2, rather than N3, N4, etc.

I tried your formula and was getting the same runtime error as mentioned above.

Nothing working so far...still trying!

Thanks,
T.J.

 

[tjharlan]

I've tried this as well, no luck:

changed

"=INDIRECT(SUBSTITUTE(N2, "" "",""_""))"

to

"=INDIRECT(SUBSTITUTE(N2," & """ "",""_""))"

 

[Norie]

What error are you getting?

If when the formula is evaluated it doesn't make sense to for the validation you would get an error.

That is why I'm asking what the formula is actually meant to do.

You might have an error in the formula rather than the VBA code.

 

[tjharlan]

Workbook has two sheets. One containing the data and one containing various lists with defined names.

In the data sheet, I have three columns with validation.

Column A - dropdown based on named range ("ValList" - in Sheet2)

Column B - dropdown based on named range (depends on value selected in Column A - in Sheet2)

Column C - dropdown based on named range (depends on value selected in Column B - in Sheet2)

So, the user selects a value from the dropdown in Column A. Each of the values in that dropdown have an associated named range in Sheet2. Sometimes, the values in Column A have a space in them, rather than an underscore (all the ranges have underscores). To ensure that this is not the case, the Substitute method is used prior to the Indirect method's execution. So, when the user selects a value from the dropdown in Column A, then the values in Column B are those contained in the range specified in Column A.

Hopefully this makes sense.

Thanks again for your help.

T.J.




***********************************************************

Range("A2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=ValList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Note"
.ErrorTitle = ""
.InputMessage = "Please select a category & then choose an option from brief break detail column."
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Range("B2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(SUBSTITUTE(""N2"","" "",""_""))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Note 2"
.ErrorTitle = ""
.InputMessage = "Please select a category & then choose an option from Action Taken column."
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Range("C2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(SUBSTITUTE(""02"","" "",""_""))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Note"
.ErrorTitle = ""
.InputMessage = "Please select a category & then choose an option from brief break detail column."
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

**********************************************************

 

[tjharlan]

The error I'm getting when the A2 isn't enclosed in quotes (i.e., ""A2"") is:

runtime error 1004 - Application-defined or object-defined error.

 

[tjharlan]

Also, the formulas above should be A2 and B2 rather than N2 and O2 (I changed the cells to make the example easier to understand).

 

[Norie]

runtime error 1004 - Application-defined or object-defined error.

If this is the error you are getting then it indicates, to me at least, that the formula is wrong.

What happens when you try to do the validation manually?

Try going to Data>Validation... and just typing in the formula.

Does that cause any problems?

BTW the SUBSTITUTE in the formula is replacing spaces with underscores. Is that definitely what you want?

 

[tjharlan]

This is a cut and paste from the Source field of the Data Validation input box:

=INDIRECT(SUBSTITUTE("bb2"," ","_")) - this works fine...

If i manually remove the quotes around bb2:

=INDIRECT(SUBSTITUTE(bb2," ","_")) this works fine as well...

Just can't figure out how to get the bb2 into the cell, using my macro, without having quotes around it.

Also - yes, the Substitute formula is correctly replacing spaces with underscores.

Thanks.

 

[Norie]

Try this code:

MsgBox "=INDIRECT(SUBSTITUTE(n2 ," & Chr(34) & " " & Chr(34) & "," & Chr(34) & "_" & Chr(34) & "))"

Does the message box display the formula you want?

 

[tjharlan]

Yes, that message seems to give the correct formula. But, I'm still getting the Run-time error '1004': Application-defined or object-defined error when replacing my original formulas with your formula.