macro help on mid
 

I am trying to select rows that only include the totals...the row includes an
acct #, a sub and the word Total...so i am using mid to find the word total
in each row, then put the acct number in a separate sheet. For example here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy 11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula

macro help on mid
 

If you have a lot of these AND total is part of the string in col D then use
this to get
11002 103.88
11003 194.79

Sub copytotalrowinfo()
dr = 2
For i = 2 To Cells(Rows.Count, "d").End(xlUp).Row
If InStr(Cells(i, "d"), "Total") Then
With Sheets("destinationsheetname")
.Cells(dr, 1) = Left(Cells(i, "d"), _
InStr(Cells(i, "d"), "-") - 1)
.Cells(dr, 2) = Cells(i, "L")
End With
dr = dr + 1
End If
Next i

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
I am trying to select rows that only include the totals...the row includes
an
acct #, a sub and the word Total...so i am using mid to find the word
total
in each row, then put the acct number in a separate sheet. For example
here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy
11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula

macro help on mid
 

If you want to try with functions then try this one

use helping column and place formula & drag it down.
Then filter with "Total"

=IF(ISERROR(FIND("total",D1)),"","Total")



On Jun 6, 8:38*pm, pm wrote:
I am trying to select rows that only include the totals...the row includes an
acct #, a sub and the word Total...so i am using mid to find the word total
in each row, then put the acct number in a separate sheet. *For example here
are 3 rows from one of my sheets:
column D * * * * * * * * * column L
11002-01256 * * * * * * * 41.11
11002-01256 * * * * * * * 62.76
11002-00707 Total * * *103.88

So i only want to select the last row, since it is the total and copy 11002
to the new sheet; AND i want to include the amount on my new sheet. *

Set colDRange = ws.Range("D1:" & _
* * * *ws.Range("D" & Rows.Count).End(xlUp).Address)
* * * For Each anyColDCell In colDRange
* * * * If Mid(anyColDCell, 13, 5) = "Total" Then
* * * * * *newFormula = "=MID('" & ws.Name & _
* * * * * *"'!R[3]C[3],1,5)"
* * * * *echoSheet.Range("A" & Rows.Count). _
* * * * * *End(xlUp).Offset(1, 0).FormulaR1C1 _
* * * * * *= newFormula

macro help on mid
 

"Don Guillett" wrote:

If you have a lot of these AND total is part of the string in col D then use
this to get
11002 103.88
11003 194.79

Sub copytotalrowinfo()
dr = 2
For i = 2 To Cells(Rows.Count, "d").End(xlUp).Row
If InStr(Cells(i, "d"), "Total") Then
With Sheets("destinationsheetname")
.Cells(dr, 1) = Left(Cells(i, "d"), _
InStr(Cells(i, "d"), "-") - 1)
.Cells(dr, 2) = Cells(i, "L")
End With
dr = dr + 1
End If
Next i

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
I am trying to select rows that only include the totals...the row includes
an
acct #, a sub and the word Total...so i am using mid to find the word
total
in each row, then put the acct number in a separate sheet. For example
here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy
11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula



Hi Don,

I have 5 sheets to read.....the total amount is not included in the string
in column D. the amount is in a separate column (L).

macro help on mid
 

When replying to me, please do so at the TOP. Your original post was not
clear and you still don't tell us where "Total" is (col E perhaps?) or where
you want it to go????. If desired, send your file to my address below with
this msg and I'll take a look.

I have 5 sheets to read.....the total amount is not included in the string
in column D. the amount is in a separate column (L).

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...


"Don Guillett" wrote:

If you have a lot of these AND total is part of the string in col D then
use
this to get
11002 103.88
11003 194.79

Sub copytotalrowinfo()
dr = 2
For i = 2 To Cells(Rows.Count, "d").End(xlUp).Row
If InStr(Cells(i, "d"), "Total") Then
With Sheets("destinationsheetname")
.Cells(dr, 1) = Left(Cells(i, "d"), _
InStr(Cells(i, "d"), "-") - 1)
.Cells(dr, 2) = Cells(i, "L")
End With
dr = dr + 1
End If
Next i

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
I am trying to select rows that only include the totals...the row
includes
an
acct #, a sub and the word Total...so i am using mid to find the word
total
in each row, then put the acct number in a separate sheet. For example
here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy
11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula



Hi Don,

I have 5 sheets to read.....the total amount is not included in the string
in column D. the amount is in a separate column (L).