Text to column issue?

Unicode

Board Regular
Joined
Apr 9, 2019
Messages
58
My VBA script works just okay, when I run macro script I have DATE on column "C", TOTALS on Column "D", Datasource lands on SHEET2, however, Date Column and Totals on column D SHEET2 do not list, neither Dollar $ sign symbol unless I manually got to DATA-> Text to column. If I record macro just for Text To Column it all works, I would like to run my Macro with text to column process with no errors.


My script:


Sub CopyPasteSheet2()



Dim lr As Long
Dim i As Long


Dim inputdtae As Date
Dim rng As Range




lr = Cells(Rows.Count, 1).End(xlUp).Row


For i = lr - 1 To 2 Step -1
If Cells(i, "B") = "LLC" Then
Cells(i, "B").EntireRow.Delete
End If
Next i






Dim LastRow As Long
Dim Sheet2 As Worksheet
Dim Results As Worksheet







Set Results = Sheets("Sheet2")
LastRow = Results.Cells(Results.Rows.Count, "Z").End(xlUp).Row


Range("A15:A400").Copy
Results.Range("A" & LastRow + 2).PasteSpecial xlValues
Range("B15:B400").Copy
Results.Range("B" & LastRow + 2).PasteSpecial xlValues
Range("J15:J400").Copy
Results.Range("C" & LastRow + 2).PasteSpecial xlValues
Range("R15:R400").Copy
Results.Range("D" & LastRow + 2).PasteSpecial xlValues




Application.DataEntryMode = False


End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I don't see where you are trying to do any text to column action in your Macro. It looks to me as if you just doing some copy and paste. Perhaps, instead of showing us a macro that does not achieve your specific results, you should explain simply what you want to do. Maybe we will have a better solution for you than trying to re-engineer what is not working.

I don't understand your statement about $ signs as there is no indication of same in your code. Please clarify as we cannot see your screen nor read your mind.
 
Upvote 0
Hey there. Hello

My sheet one text to column looks like this, then these dates with numbers are landing on SHEET2.


ActiveWindow.SmallScroll Down:=11


Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("R:R").Select
Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True








I don't see where you are trying to do any text to column action in your Macro. It looks to me as if you just doing some copy and paste. Perhaps, instead of showing us a macro that does not achieve your specific results, you should explain simply what you want to do. Maybe we will have a better solution for you than trying to re-engineer what is not working.

I don't understand your statement about $ signs as there is no indication of same in your code. Please clarify as we cannot see your screen nor read your mind.
 
Upvote 0
The following would be from Sheet1:





ABDGJKMNOQR
UnitResidentUnit AddressStatus DateLast PayDateLast PayAmountCurrentOver 30DaysOver 60DaysOver 90DaysBalance
020501 Col Benn Leasing510 Ocean Dr #20512/2/20174/2/20192,941.96-7.94-7.94

















Hey there. Hello

My sheet one text to column looks like this, then these dates with numbers are landing on SHEET2.


ActiveWindow.SmallScroll Down:=11


Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("R:R").Select
Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
 
Last edited:
Upvote 0
My VBA Script:


Sub CopyPasteSheet2DELINQUENT()




Dim lr As Long
Dim i As Long


Dim inputdtae As Date
Dim rng As Range




lr = Cells(Rows.Count, 1).End(xlUp).Row


For i = lr - 1 To 2 Step -1
If Cells(i, "B") = "LLC" Then
Cells(i, "B").EntireRow.Delete
End If
Next i






Dim LastRow As Long
Dim NEWFORMAT3 As Worksheet
Dim Results As Worksheet







Set Results = Sheets("NEWFORMAT3")
LastRow = Results.Cells(Results.Rows.Count, "Z").End(xlUp).Row


Range("A15:A400").Copy
Results.Range("A" & LastRow + 2).PasteSpecial xlValues
Range("B15:B400").Copy
Results.Range("B" & LastRow + 2).PasteSpecial xlValues
Range("J15:J400").Copy
Results.Range("C" & LastRow + 2).PasteSpecial xlValues
Range("R15:R400").Copy
Results.Range("D" & LastRow + 2).PasteSpecial xlValues




Application.DataEntryMode = False



ActiveWindow.SmallScroll Down:=11




Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("R:R").Select
Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True


End Sub






















The following would be from Sheet1:





ABDGJKMNOQR
UnitResidentUnit AddressStatus DateLast PayDateLast PayAmountCurrentOver 30DaysOver 60DaysOver 90DaysBalance
020501 Col Benn Leasing510 Ocean Dr #20512/2/20174/2/20192,941.96-7.94-7.94
 
Upvote 0
I've read through your code and still do not understand what you want. Please explain as if we were standing in line to get coffee. Do not use Excel terms, but business terms using the column headings so that I understand what you are attempting to do. It is not clear to me.
 
Upvote 0
Here it goes, I would like sheet2 to be able to display data "Dates" on "C2" and work properly with formulas on Sheet2 Column "E", and Dollar "$" sign to appear on D2, from data Sheet1.



Current formula on Sheet2 cell "E2": =IF(TODAY()>C2,TODAY()-C2,0)




I've read through your code and still do not understand what you want. Please explain as if we were standing in line to get coffee. Do not use Excel terms, but business terms using the column headings so that I understand what you are attempting to do. It is not clear to me.
 
Last edited:
Upvote 0
Here it goes, I would like sheet2 to be able to display data "Dates" on "C2" and work properly with formulas on Sheet2 Column "E", and Dollar "$" sign to appear on D2, from data Sheet1. Currently this is not possible, due to the reason that Sheet1 data is being exported from a legacy system, after the export data displays on Sheet1, however, all DATES, Dollar Signs are not active, meaning Excel does not allow for me to select column, then FORMAT Date, or Currency, Accounting. So when data is copied and pasted on SHEET2, DATES and Amounts display with no active Dates format, or Currency, Accounting.




Example: Last pay day has to be set on Sheet2 from cell "J2" on SHEET1, then the business report on SHEET2 has a formula that will grab the date on "C2" and let me know how many days are outstanding from account last pay.




Current formula on Sheet2 cell "E2": =IF(TODAY()>C2,TODAY()-C2,0)
 
Upvote 0
The only way DATE with AMOUNT displays correctly on SHEET2 is by selecting each column individually on Sheet2 column "C", then after going into excel choosing DATA TAB -> "Text to Column", then DATES with column row activate, if I do the same for column "D" the Dollar sign $ per each row going down on both Column "C" and "D" display correctly.



Here it goes, I would like sheet2 to be able to display data "Dates" on "C2" and work properly with formulas on Sheet2 Column "E", and Dollar "$" sign to appear on D2, from data Sheet1. Currently this is not possible, due to the reason that Sheet1 data is being exported from a legacy system, after the export data displays on Sheet1, however, all DATES, Dollar Signs are not active, meaning Excel does not allow for me to select column, then FORMAT Date, or Currency, Accounting. So when data is copied and pasted on SHEET2, DATES and Amounts display with no active Dates format, or Currency, Accounting.




Example: Last pay day has to be set on Sheet2 from cell "J2" on SHEET1, then the business report on SHEET2 has a formula that will grab the date on "C2" and let me know how many days are outstanding from account last pay.




Current formula on Sheet2 cell "E2": =IF(TODAY()>C2,TODAY()-C2,0)
 
Last edited:
Upvote 0
I want to run the Text to Columns in a macro every time the source file is updated.





The only way DATE with AMOUNT displays correctly on SHEET2 is by selecting each column individually on Sheet2 column "C", then after going into excel choosing DATA TAB -> "Text to Column", then DATES with column row activate, if I do the same for column "D" the Dollar sign $ per each row going down on both Column "C" and "D" display correctly.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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