Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding text or part of a text inside the value cell | Excel Programming | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
select text in cell based on text from another cell, paste the text at the begining of a thrid cell, etc... | Excel Programming | |||
select part of a cell value string | Excel Discussion (Misc queries) | |||
Select part of a cell | Excel Worksheet Functions |