SELECT PART OF A TEXT IN A CELL
 

Hello,

I have this macro which extracts only the rows that contains "VT" in a
specific column, copy those rows and paste them transposed to a different
worksheet. What I want to do now is to select rows using a different column
as criteria (the column before that) and the macro should search the rows
that contains "LA" as the initial part of the text in the cell, regardless of
what letters follow the initial "LA...", keeping all other instructions the
way they are.
So my main question is: what is the correct syntax to refer to a part of a
cell content, regardless of what is followed after that part specified.

Here is the macro:

Sub CopyTransposeVT()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim n As Integer
Dim wkSource As Worksheet
Dim wkDestination As Worksheet


Set wkOrigem = Sheets("Sheet2") 'Worksheet source'
Set wkDestino = Sheets("VT") 'Workshet to where data will be
copied'
k = 116 'Column in source
worksheet (DL column)'


With wkDestination
.Cells(30, 1) = "DisplayUnit"
.Cells(29, 1) = "ParameterName"
.Cells(31, 1) = "DisplayValue"

End With


With wkOrigem
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If .Cells(i, k) = "VT" Then

wkDestino.Cells(30, wkDestino.Cells(30,
Columns.Count).End(xlToLeft).Column + 1) = .Cells(i, 114)
wkDestino.Cells(29, wkDestino.Cells(29,
Columns.Count).End(xlToLeft).Column + 1) = .Cells(i, 115)
wkDestino.Cells(31, wkDestino.Cells(31,
Columns.Count).End(xlToLeft).Column + 1) = .Cells(i, 125)

End If
Next i
End With

End Sub

Thanks
McData

SELECT PART OF A TEXT IN A CELL
 

Try this...

If .Cells(i, k) Like "LA*" Then

Note that this is a case-sensitive comparison. If you need a
case-insensitive comparison, try this instead...

If .Cells(i, k) Like "[Ll][Aa]*" Then

--
Rick (MVP - Excel)



"McData" wrote in message
...
Hello,

I have this macro which extracts only the rows that contains "VT" in a
specific column, copy those rows and paste them transposed to a different
worksheet. What I want to do now is to select rows using a different
column
as criteria (the column before that) and the macro should search the rows
that contains "LA" as the initial part of the text in the cell, regardless
of
what letters follow the initial "LA...", keeping all other instructions
the
way they are.
So my main question is: what is the correct syntax to refer to a part of a
cell content, regardless of what is followed after that part specified.

Here is the macro:

Sub CopyTransposeVT()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim n As Integer
Dim wkSource As Worksheet
Dim wkDestination As Worksheet


Set wkOrigem = Sheets("Sheet2") 'Worksheet source'
Set wkDestino = Sheets("VT") 'Workshet to where data will be
copied'
k = 116 'Column in source
worksheet (DL column)'


With wkDestination
.Cells(30, 1) = "DisplayUnit"
.Cells(29, 1) = "ParameterName"
.Cells(31, 1) = "DisplayValue"

End With


With wkOrigem
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If .Cells(i, k) = "VT" Then

wkDestino.Cells(30, wkDestino.Cells(30,
Columns.Count).End(xlToLeft).Column + 1) = .Cells(i, 114)
wkDestino.Cells(29, wkDestino.Cells(29,
Columns.Count).End(xlToLeft).Column + 1) = .Cells(i, 115)
wkDestino.Cells(31, wkDestino.Cells(31,
Columns.Count).End(xlToLeft).Column + 1) = .Cells(i, 125)

End If
Next i
End With

End Sub

Thanks
McData