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