Prev: Countif using symbols
Next: cancel a combobox change
From: McData on 7 Apr 2010 12:35
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
From: Rick Rothstein on 7 Apr 2010 13:04
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" <McData(a)discussions.microsoft.com> wrote in message
news:A08F6BE2-3B53-4B57-8E42-083797C8ECFA(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: Countif using symbols
Next: cancel a combobox change