VBA Code for Text to Column function

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Part of the code below copies a column to a separate tab (column P) to perform text to column process (Town -CenterStreet-20) for (columns P, Q & R) I do this to separate out the Town name with the "-" as a delimited separator, the code then copies only the town column and pastes it back into the working file.
The VBA is stopping to due the columns Q & R having data from the last time this process was run for the previous month. How do I alter the code to say Yes to the box question that appears to overwrite columns Q & R?


VBA Code:
Range("O1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Selection.Copy
    Sheets("Original report ").Select
    ActiveWindow.ScrollColumn = 1
    Columns("J:J").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Town Location"
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
350
Office Version
  1. 2016
Platform
  1. Windows
Hi Fvisons

add this before the code you shared:
VBA Code:
q_overwriteQR = MsgBox("Do you want to overwrite data in columns Q and R?", vbYesNo)
if q_overwriteQR = vbno then
    Exit Sub 'might be "Goto somebookmark"
    Else
    Range("Q:R").Clear
    'Range("Q2:R10000").clear 'if not full columns need to be cleared, adjust to your needs
End if
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Runtime error 1004 No data was selected to parse?
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
move the code and received error 1004 "to do this, all merged cells need to be the same size
 

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
350
Office Version
  1. 2016
Platform
  1. Windows
Hi,

since i dont have any info/visual on your data/structure or rest of your code
This has to work (modified the .clear to clearcontent, but there should be any merged cell since text-to-columns not working that way as im aware)2
VBA Code:
q_overwriteQR = MsgBox("Do you want to overwrite data in columns Q and R?", vbYesNo)
if q_overwriteQR = vbno then
    Exit Sub 
else
Range("Q:R").ClearContents
End if 

Range("O1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Selection.Copy
    Sheets("Original report ").Select
    ActiveWindow.ScrollColumn = 1
    Columns("J:J").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Town Location"
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
Did the original data, before you have done the Text to Columns, come from sheet "Original Report" column J?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,888
Messages
5,574,842
Members
412,620
Latest member
sharma7s
Top