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.

 

[Norie]

TJ

What code are you using to autofill?

Can you post it?

 

[tjharlan]

Hi Norie,

The code I am using to autofill is:

Range("BB2:BD2").Select
Selection.AutoFill Destination:=Range("BB3:BD" & rows)

I'm not to the point of verifying if that code works. But, even if I stop the macro after it inserts the formula, and then do a manual formula drag, the cell reference does not change (i.e, n2, n2, n2 rather than n2, n3, n4).

Thanks,
T.J.

 

[Norie]

What is the actual formula you want to enter?

Can you post more of the code?

 

[tjharlan]

Here is the entire formula that I'm trying to enter. It's setting validation to one cell:

Range("BC2").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

I want to enter the "n2" without any quotation marks so that the formula can be copied and dragged down so that the value changes to n3, n4, etc.

Thanks.

 

[Norie]

So is the actual formula you want is this:

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

Do you want to replace each space with an underscore or vice versa?

Can you explain exactly what you are trying to acheive?

 

[tjharlan]

Norie,

Thanks for your response. The formula works fine if I insert it as into cell O2:

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

The problem is, I want to subsequently drag the formula down to O3, O4, O5, etc., the formula still contains N2, rather than N3, N4, and N5.

Thus, it's really not necessary to get into what my indirect/substitute formula is trying to achieve as this is working fine. It's just the way that the formula is inserted into the cell.

Thanks for the help. It's greatly appreciated.

 

[Norie]

Are you sure your formula is working fine?

When I try to use it in either a cell or for data validation I get errors.

If you want the N2 to change don't have it in quotes.

 

[tjharlan]

Hi Norie,

Yes, the formula works fine when I use the ""N2""... if I remove the quotes, I get a runtime error 1004 - Application-defined or object-defined error.

 

[Norie]

I mean in the actual worksheet.

When you run your code and then goto the cells that you are setting the validation what does the formula look like?

Is n2 in quotes?

Try this

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

Chr(34) = "