macro help on mid

[pm]

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

 

[muddan madhu]

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")

 

[pm]

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
(e-mail address removed)

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).

 

[Don Guillett]

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
(e-mail address removed)

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
(e-mail address removed)

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).