vba formulas

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
TJ

What code are you using to autofill?

Can you post it?
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
What is the actual formula you want to enter?

Can you post more of the code?
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 8, 2004
Messages
63

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 8, 2004
Messages
63
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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) = "
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top