Stripping Line Breaks

[formbyg]

VBA newbie looking for assistance, for Database generated Cable Schedule report
Multiple property data is populated off to the R/H side of the final displayed report in Defined Name Columns
For every instance that there is a Cable Item Tag in Column 'A' (defined name, col_ARep_CabItemTag = $A:$A) do the following;-


If Cable originates or terminates at a Circuit, return Power Distribution Board (PDB) Item Tag. (This part works OK)
If Cable originates from Transformer Component (secondary), return Transformer Item Tag. (This part works OK)
The 'To Side Item Descriptions' part is what is giving me trouble. (This part is not working)


Public Sub EndReport()
Dim lTotalsLastRow As Long
Dim lCurrentRow As Long
Dim lCopyRow As Long
Dim ws As Worksheet
Dim iSheetNumber As Integer
Dim sFirstRowTag As String
Dim sLastRowTag As String
Dim rRange As Range
Dim sCleaning As String
Set ws = Sheets("Sheet1")
iSheetNumber = 1
lCurrentRow = 1
With ws
lTotlsLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For lCopyRow = 9 To lTotalsLastRow '(Start Copy from Row 9 to last non-empty Row)
'From Side Item Tags
If (.Cells(lCopyRow, [col_From_EESubClass].Column) = "Circuit" And .Cells(lCopyRow, [col_From_PDB].Column) <> "") Then
.Cells(lCopyRow, [col_ARep_FromItemTag].Column) = .Cells(lCopyRow, [col_From_PDB].Column)
Else
.Cells(lCopyRow, [col_ARep_FromItemTag].Column) = .Cells(lCopyRow, [col_From_ItemTag].Column)
If (.Cells(lCopyRow, [col_From_EESubClass].Column) = "Transformer Component" And .Cells(lCopyRow, [col_From_TransformerItemTag].Column) <> "") Then
.Cells(lCopyRow, [col_ARep_FromItemTag].Column) = .Cells(lCopyRow, [col_From_TransformerItemTag].Column)
End If
End If
'To Side Item Tags
If (.Cells(lCopyRow, [col_To_EESubClass].Column) = "Circuit" And .Cells(lCopyRow, [col_To_PDB].Column) <> "") Then
.Cells(lCopyRow, [col_ARep_ToItemTag].Column) = .Cells(lCopyRow, [col_To_PDB].Column)
Else
.Cells(lCopyRow, [col_ARep_ToItemTag].Column) = .Cells(lCopyRow, [col_To_ItemTag].Column)
End If
'To Side Item Descriptions (Not working)
If (.Cells(lCopyRow, [col_To_EESubClass].Column) = "Motor" And .Cells(lCopyRow, [col_To_ItemDesc].Column) <> "") Then
.Cells(lCopyRow, [col_ARep_ToItemDesc].Column) = .Cells(lCopyRow, [col_To_ItemDesc].Column)
sCleaning = .Cells(lCopyRow + 1, [col_To_ItemDesc].Column)
sCleaning = Replace(sCleaning, Chr(10), Chr(44), 1, -1, vbBinaryCompare)
sCleaning = .Cells(lCopyRow + 1, [col_ARep_ToItemDesc].Column) '(Strip out Line Breaks From Descriptions)
End If
Next
End With
End Sub

[snb]

You not posting a sample file is giving me trouble.....

[Paul_Hossler]

The 'To Side Item Descriptions' part is what is giving me trouble. (This part is not working)


Any more detail?

'Giving me trouble' I can understand, but 'not working' is a little fuzzy

[p45cal]

should:

sCleaning = .Cells(lCopyRow + 1, [col_ARep_ToItemDesc].Column)
be:

.Cells(lCopyRow + 1, [col_ARep_ToItemDesc].Column)=sCleaning
?

[formbyg]

Thanks for your response and willingness to help, see attached file
17595

[formbyg]

I have tried both iterations, are they the same from a code perspective
or is there some fundamental difference in the statements I'm missing ?
Is it the case with VBA, that (1+2=3) is fundamentally different to (3=1+2)
I am trying to get a handle on how to structure the commands based on Selection & Action
Appreciate you have taken the time to reply

[formbyg]

Thanks for the reply
By "Not Working" I mean that the code is not doing what I am trying to archive.
That being, IF electrical equipment Sub Class is Motor, then strip out the line breaks from the Motor Description (AU:AU)
and copy it to the 'To Item Description' column in the Cable Schedule (E:E)
Trying to adapt/modify a chunk of code from another report that used a different method to define the Range.
Am working on the basis that the Range (For lCopyRow = 9 To lTotalsLastRow) is valid for the 'To Side Item Descriptions
But as can be seen in the report, the line breaks aren't being stripped & the 'To Descriptions' are not being populated in the Cable Schedule
Intent is to get this working for Motors then include some OR function to include Panels, Transformers, then replicate for 'From Descriptions'

[p45cal]

The sample file contains no cells whatsoever that contain "Motor". Useful.
I'll stick with my suggestion in post #4 here.

[formbyg]

I had to revise the order in which actions occurred
Firstly to strip out the line breaks unconditionally
Then do the copy actions.

Appreciate the feedback, sometimes even a suggestion can trigger a different mind set to the approach


Public Sub EndReport()


Dim lTotalsLastRow As Long
Dim lCurrentRow As Long
Dim lCopyRow As Long
Dim ws As Worksheet
Dim iSheetNumber As Integer
Dim sFirstRowTag As String
Dim sLastRowTag As String
Dim rRange As Range
Dim sCleaning As String


Set ws = Sheets("Sheet1")
iSheetNumber = 1
lCurrentRow = 1


With ws

lTotalsLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

For lCopyRow = 9 To lTotalsLastRow 'Start Copy from Row 9 to last non-empty Row, Step 1 = Every Row (GJF 8-Nov-2016)


'From Side Item Tags
If (.Cells(lCopyRow, [col_From_EESubClass].Column) = "Circuit" And .Cells(lCopyRow, [col_From_PDB].Column) <> "") Then
.Cells(lCopyRow, [col_ARep_FromItemTag].Column) = .Cells(lCopyRow, [col_From_PDB].Column)

Else
.Cells(lCopyRow, [col_ARep_FromItemTag].Column) = .Cells(lCopyRow, [col_From_ItemTag].Column)

If (.Cells(lCopyRow, [col_From_EESubClass].Column) = "Transformer Component" And .Cells(lCopyRow, [col_From_TransformerItemTag].Column) <> "") Then
.Cells(lCopyRow, [col_ARep_FromItemTag].Column) = .Cells(lCopyRow, [col_From_TransformerItemTag].Column)


End If
End If

'To Side Item Tags
If (.Cells(lCopyRow, [col_To_EESubClass].Column) = "Circuit" And .Cells(lCopyRow, [col_To_PDB].Column) <> "") Then
.Cells(lCopyRow, [col_ARep_ToItemTag].Column) = .Cells(lCopyRow, [col_To_PDB].Column)

Else
.Cells(lCopyRow, [col_ARep_ToItemTag].Column) = .Cells(lCopyRow, [col_To_ItemTag].Column)

End If

'From Side Item Descriptions
If (.Cells(lCopyRow, [col_From_EESubClass].Column) <> "") Then
.Cells(lCopyRow + 1, [col_From_ItemDesc].Column) = sCleaning
sCleaning = Replace(sCleaning, Chr(10), Chr(32), 1, -1, vbBinaryCompare) 'Strip out Line Breaks From Descriptions GJF 7-Nov-2016

If (.Cells(lCopyRow, [col_From_EESubClass].Column) = "Circuit") Then
.Cells(lCopyRow, [col_ARep_FromItemDesc].Column) = .Cells(lCopyRow, [col_From_PDB_Desc].Column)

Else
.Cells(lCopyRow, [col_ARep_FromItemDesc].Column) = .Cells(lCopyRow, [col_From_ItemDesc].Column)

If (.Cells(lCopyRow, [col_From_EESubClass].Column) = "Transformer Component") Then
.Cells(lCopyRow, [col_ARep_FromItemDesc].Column) = .Cells(lCopyRow, [col_From_TransformerDesc].Column)

End If

End If

End If

'To Side Item Descriptions
If (.Cells(lCopyRow, [col_To_EESubClass].Column) <> "") Then
.Cells(lCopyRow + 1, [col_To_ItemDesc].Column) = sCleaning
sCleaning = Replace(sCleaning, Chr(10), Chr(32), 1, -1, vbBinaryCompare) 'Strip out Line Breaks From Descriptions GJF 7-Nov-2016

If (.Cells(lCopyRow, [col_To_EESubClass].Column) = "Circuit") Then
.Cells(lCopyRow, [col_ARep_ToItemDesc].Column) = .Cells(lCopyRow, [col_To_PDB_Desc].Column)

Else
.Cells(lCopyRow, [col_ARep_ToItemDesc].Column) = .Cells(lCopyRow, [col_To_ItemDesc].Column)

End If

End If

Next

End With


End Sub

[p45cal]

Deleted post (I can't work out what you're trying to do).

[snb]

also removed