PDA

View Full Version : Need Help: Auto-acept To replace



ZUMBIDO
10-22-2010, 11:48 AM
Hello. First at all I am Argentinian, and I speak and write in Spanish. My apologizes for Gramatical error in your language.
Ok. This is my situation. I am making a macro wich automatically do "TextToColumns" in an excel sheet. I know how to do it, but when I bring that commands from a "Module" to a "Form", start to appear a dialog to "Want to replace [..] the destine cell?" (buttons of Accept / Cancel), just after I give the command.

:help I really want to know if are a way to avoid that message and continue with the "textToColumns" commands. Or how I can make to tell this dialog to proceed with the replacement (I donīt have anything to the right)

This are the data I have about it:
=====================================================
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("B11:B22").Select


Selection.TextToColumns Destination:=Range("B11"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True


Columns("E:E").Select
Selection.Delete Shift:=xlToLeft

Range("C10").Select
ActiveCell.FormulaR1C1 = "Label1" '<-- just some title to the columns
Range("D10").Select
ActiveCell.FormulaR1C1 = "Label2" '<-- just some title to the columns
=====================================================

The dialog supose to appear before " Selection.TextToColumns..." line.

Thank you so much for any help that you can give me.

Note: If need more info, let me know.
Note2: Here are 2 lines of the data that I have to "Encolumn":
38 | Medi_Nor | TECO|
35 | Medi_Sur | TECO|

shrivallabha
10-22-2010, 12:01 PM
Welcome to VBAX,

Add these lines to your code at the beginning to the code

Application.DisplayAlerts = False

And following at the end of the code to return to default settings:
Application.DisplayAlerts = True

See if it helps.

It may be also important to know that Excel raises this message only when the cells have some content in them.

mdmackillop
10-22-2010, 12:17 PM
As you work in code, try to remove the verbiage created by the recorder. Also, remove unnesessary selection. Your code will be much simpler, easier to debug/modify and faster. For your example, this reduces to

Sub hh()

Columns("C:E").Insert

Range("B11:B22").TextToColumns Destination:=Range("B11"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True

Columns("E:E").Delete

Range("C10:D10") = Array("Label1", "Label2") '<-- just some title to the columns

End Sub

ZUMBIDO
10-22-2010, 12:28 PM
Thanks shrivallabha! It works! Thanks for your time and attention, and of course: Knowledge.:thumb

ZUMBIDO
10-22-2010, 12:39 PM
[quote=mdmackillop]As you work in code, try to remove the verbiage created by the recorder. Also, remove unnesessary selection. Your code will be much simpler, easier to debug/modify and faster.

Thanks mdmackillop! I am really surprise how easier it turns with your advices! And so clear.

From now I will pay attention to remove unnecessary lines that create the recorder.:yes