To compare and return the string value

Closed
saran - Aug 24, 2009 at 04:01 AM
 saran - Aug 31, 2009 at 01:10 AM
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

6 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 24, 2009 at 09:20 PM
quote
eturn the value "Apple A 1245" into a cell......"
unquote
in which cell?
0
String values are stored in sheet1 . Once comparison is done, it has to return the string values based on their length to "ColumnA" of "Sheet2".
thanks
0
strings are stored in sheet1. return the value after comparing into 'Column A" of "Sheet2"
Thanks
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 25, 2009 at 05:27 AM
still I am confused . pl gives the first few rows of sheet 2
0
Hi venkat,

Sheet 1:
Description Amount
Apple A 123 $100.00
Apple A 12345 $200.00

After comparing strings in sheet1, if length=12 means, return only those strings to sheet2 or viceversa
Sheet 2:
Description Amount



Let me know if you still have questions.Thanks!!!
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 26, 2009 at 09:54 PM
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
0
Venkat,
am having strings of length 12 and 15 only.. so that wouldn't be a problem.
will try out your code
0

Didn't find the answer you are looking for?

Ask a question
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 27, 2009 at 05:39 AM
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.
0
yes... will do that. in case if i require length of 12 and 15 in seperate sheets, i have to change the sheet names. Right? correct me if am wrong
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 27, 2009 at 08:53 PM
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
0
thanks
0