VBA Code for Text to Column function

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
191
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"
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
move the code and received error 1004 "to do this, all merged cells need to be the same size
 
Upvote 0
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"
 
Upvote 0
Did the original data, before you have done the Text to Columns, come from sheet "Original Report" column J?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top