Categorised data into different sheets

[Jentan]

I am trying to categorise different information into different sheets
within the same file.

A B C D E
1 000514329214734 29/06/2006 INEXC 0740 INLAND EXCHANGE COMM

2 000514329214734 29/06/2006 7100 0740 424280606
*
3 000514329214734 29/06/2006 RINEXC 0740 INLAND EXCHANGE COMM

4 000514329214734 29/06/2006 7100 0740 424290606
*
5 000514329214734 29/06/2006 RTNCHQSR 0740 INLAND EXCHANGE COMM

6 000514329214734 29/06/2006 7100 0740 424290606
*
7 000514329214734 29/06/2006 INEXC 0105 INLAND EXCHANGE COMM



I have using the following macro command but somehow or rather it does
not work. I wanted to transfer all the "INEXC", "RINEXC","RTNCHQSR" in
column C to another new sheet named as "Chrg". Is my command wrong?

lastrow = WorksheetFunction.Count(Columns(10))
For i = 1 To lastrow

If Cells(i, 3) = "INEXC" Or Cells(i, 3) = "RINEXC" _
Or Cells(i, 3) = "RTNCHQSR" Or Cells(i, 3) = "RTNCHQSC" _
Then
Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1
End If

Appreciate for assistance

 

[JMB]

This seemed to work for me. Count returns a count of numeric data in column
10, so it may not be the same number as the last row. Also, you need to
initial j, otherwise I am pretty sure VBA will assume the initial value to be
0, which is an invalid row reference. Backup before trying.

Sub test()
lastrow = Cells(Rows.Count, 10).End(xlUp).Row
j = 1

For i = 1 To lastrow
If Cells(i, 3) = "INEXC" Or Cells(i, 3) = "RINEXC" _
Or Cells(i, 3) = "RTNCHQSR" Or Cells(i, 3) = "RTNCHQSC" _
Then
Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1
End If
Next i
End Sub

 

[Jentan]

Thank you. May I know the meaning of this :

Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1

 

[JMB]

The macro has two counter variables. i keeps track of the current row number
of the source worksheet while j keeps track of the current row number of the
destination worksheet.

Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
If the specified criteria in the If statement is met, the above line will
cut that entire row and paste it into the "Chrg" worksheet. The syntax is
Source.Cut Destination where source and destination are range references.

j=j+1 just increments the counter variable to identify the row number that
the next line of data should go into on your destination worksheet.

 

[Jentan]

Thank you very much JMB.
I have another issues here.

INEXC
7100
INEXC
7100

The above have a lot of space after each word. I actually wanted to
copy those with the word "INEXC" over to another tab. However, i can't.
I guess it is due to spacing after the word. Can I use the TRIM command
and may I know how?

Thank you.

 

[Jentan]

In addition to the above, I have tried this formula to TRIM all the
data in column "D".
Sub trim()
For i = 1 To lastrow

Sheets("Database").Activate
Cells(i, 4) = "=trim(d)"
Next

End Sub

This doesn't work. Is my TRIM formula wrong?

 

[JMB]

You can incorporate the Trim function into the VBA code. VBA's Trim function
removes both leading and trailing spaces (there is also RTrim and LTrim which
will remove only trailing or leading spaces). I would also use Ucase to
convert the data to uppercase for comparison purposes. Case is not usually
an issue for text comparison, but it could be in under some circumstances.
If you need VBA to remove leading, trailing, and extra spaces in the middle
of the string data, you can access the Excel Trim function with
Application.Trim(" your data here ")


Sub test()
Dim strTemp As String
Dim lastrow As Long
Dim j As Long

lastrow = Cells(Rows.Count, 10).End(xlUp).Row
j = 1

For i = 1 To lastrow
strTemp = Ucase(Trim(Cells(i, 3).Value))
If strTemp = "INEXC" Or strTemp = "RINEXC" _
Or strTemp = "RTNCHQSR" Or strTemp = "RTNCHQSC" _
Then
Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1
End If
Next i
End Sub


The above macro will clean the data for purposes of comparison within the
macro, your data in the excel sheet will not change. If you want the data in
the excel sheet cleaned up you could make a small change to this:

Sub test()
Dim strTemp As String
Dim lastrow As Long
Dim j As Long

lastrow = Cells(Rows.Count, 10).End(xlUp).Row
j = 1

For i = 1 To lastrow
Cells(i, 3).Value = Trim(Cells(i, 3).Value)
strTemp = Ucase(Cells(i, 3).Value)
If strTemp = "INEXC" Or strTemp = "RINEXC" _
Or strTemp = "RTNCHQSR" Or strTemp = "RTNCHQSC" _
Then
Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1
End If
Next i
End Sub

And, of course you could use substitute VBA's Trim with LTrim, RTrim, or
Application.Trim (Excel's worksheet version).