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

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" wrote:


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

Thank you. May I know the meaning of this :

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


JMB wrote:
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" wrote:


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

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" wrote:

Thank you. May I know the meaning of this :

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


JMB wrote:
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" wrote:


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

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.

JMB wrote:
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" wrote:

Thank you. May I know the meaning of this :

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


JMB wrote:
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" wrote:


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

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?


Jentan wrote:
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.

JMB wrote:
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" wrote:

Thank you. May I know the meaning of this :

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


JMB wrote:
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" wrote:


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