To compare and return the string value Closed

Question

Hello,
Someone help me out.
I have same names that are distinguished by their length based on the numbers preceded by them in a column , the strings may be either of length 12 or 15 [Eg:"Column A") in the sheet. All the names are stored in a variable [Eg: "text" variable]
 This is the scenario am trying. For example consider following strings, 
1) Apple A 1245 (Length:12) and 
2) Apple A 1234567 (Length:15). 
Here "Apple A 1234567" is considered as one full string. I have to check for the length of the two strings and return the string value based on the string length. 

if length of string = 12, return the value "Apple A 1245" into a cell in the sheet
if length of string = 15, return the value "Apple A 1234567" into a cell in the sheet.

lik wise, it should continue for other cells also in the column and return the string value

venkat1926 · Answer

quote
eturn the value "Apple A 1245" into a cell......"
unquote
in which cell?

saran · Answer

strings are stored in sheet1. return the value after comparing into 'Column A" of  "Sheet2"
Thanks

venkat1926 · Answer

still I am confused .  pl gives the first few rows of sheet 2

venkat1926 · Answer

still nto clear
I am rephrasing it 
fruit names are there in A2 down in column A in sheet 1
if the length of the furit name is 12 OR 15 that row in sheet should be copied 
to the first blank row in sheet 2.
(what happens if it is 1 to 11,13 ,14,16 and above nothing is to be done )
if this is ok 

try this macro

Sub test()
Dim rng As Range, c As Range
With Worksheets("sheet1")
Set rng = Range(.Range("a2"), .Range("A2").End(xlDown))
For Each c In rng
If Len(c) = 12 Or Len(c) = 15 Then
c.EntireRow.Copy
End If
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Next c
End With
End Sub

venkat1926 · Answer

if you have only lenghts of 12 and 15 why not just copy the whole sheet. 

of do you want elngth 12 in one sheet (sheet 2) and 15 in another sheet sheet 3.

venkat1926 · Answer

it is not that simple. the macro is modified. the modifie macro is given below 

Sub tes1()
Dim rng As Range, c As Range
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c In rng
If Len(c) = 12 Then
c.EntireRow.Copy

With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
ElseIf Len(c) = 15 Then
c.EntireRow.Copy

With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End If
Next c
End With

End Sub

To compare and return the string value

6 responses

Similar discussions

Subscribe To Our Newsletter!