0

My goal is to take a number that looks like this original number: 123456789 and make it look like this Goal: 123-456-789-000

My problem: VBA has and issue with these two formulas: 1 - =G2 & "-000" & 2- REPLACE(REPLACE(D2,4,0," - "),8,0," - "))

Then I take that range of data(there are 1000s of rows like this) cut and copy it over the original numbers as values***haven't got this far yet but bonus for any who helps with this as well.

I'm sure there is a better way of setting this up. 1st goal is to get it to work 2nd goal is to touch it up in a more presentable/efficient way

Here is my code:

'APN fixing for dashes data select from original numbers
Range("G2").Select
Selection.Value =  "REPLACE(REPLACE(D2,4,0," - "),8,0," - "))" 'Put replace formula here ""
Selection.AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)




    'APN fixing for adding -000 selects from the above macro to add this to it
Range("H2").Select
Selection.Value =  " =G2 & "-000""  'Put the add text formula here ""
Selection.AutoFill Destination:=Range("H2:H" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

   'Copy list of APNs to another range of cell.

bonus this code I haven't made yet, but any help would be great here to*

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
JQTs
  • 142
  • 2
  • 11
  • You need to double up the inner quotes in the formula. And you're missing the `=` in formula 2. And why not use `.Formula` instead of `.Value`, since you're writing an actual formula. – BigBen Apr 21 '21 at 19:02
  • Thank you. I added the quote and the equal sign so it look like this: Selection.Formula = "=REPLACE(REPLACE(D2,4,0,"" - ""),8,0,"" - ""))" and now I get this error : Application-defined or object-defined error. I am wondering why my vlookups formulas work but not this now – JQTs Apr 21 '21 at 22:35
  • You have an extra parenthesis: `Range("G2").Formula = "=REPLACE(REPLACE(D2,4,0,"" - ""),8,0,"" - "")` (no need to `Select`) – BigBen Apr 21 '21 at 23:36
  • Got it! had to many ) in there. Typos am I right?! – JQTs Apr 21 '21 at 23:38

0 Answers0