PDA

View Full Version : Tab Error on CSV File



sooty8
12-02-2010, 05:28 AM
Hi All

I'm using the code below to send the following 4161,AR10627 - there are many lines & rows of data however they all follow the same pattern as above displayed in red. The data is pasted into an Email and shortly afterwards I receive the following 4161,AR10627 ,168,280 I require it to be as follows 4161,AR10627,168,280 - I have checked with the supplier and it is not at their end - I think I have tried everything to alter the code below but not had any success any help much appreciated.


Option Explicit
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim myrange As Range
Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 3 To 12

.Cells(3, i).Resize(Lastrow - 2).Value = .Cells(1, i).Value
Next i
End With

Dim RowCount As Long, j As Long
With Sheets("Sites")
RowCount = .Cells(.Rows.Count, "B").End(xlUp).Row - 2
j = 1
For i = 3 To RowCount * 10 + 1 Step RowCount
.Cells(i, "A").Resize(RowCount).Formula = "=TRIM(" & Range("B3").Offset(, j).Address(False, False) & ")&TRIM(B3)"
j = j + 1
Next i

End With


Set myrange = Worksheets("Sites").Range("A3", Range("A3").End(xlDown))
myrange.Select
myrange.Copy
Application.ScreenUpdating = True
End Sub



Regards

Sooty 8

Paul_Hossler
12-02-2010, 05:47 AM
You can try


.Cells(i, "A").Resize(RowCount).Formula = "="'" & TRIM(" & Range("B3").


Where the bold = double quote-single quote-double quote

Paul

sooty8
12-02-2010, 07:15 AM
Hi Paul

Thanks for the reply unfortunately all that is displayed in Column A is as follows

=

Help needed

Regards

Sooty8