Search for a string and if found copy/paste value...

[dingleberry]

This one is tough. I dump a report from our payroll system to excel.
In column A there will sometimes be a string that starts with 6JV/EPFPL.

I need to write somethign that will search for that string and if it finds it, continue looking down the sheet in column F for the next instance it finds the phrase 'Total Hours' and when it finds that copy the value in the same row as 'Total Hours' in column BA to another sheet.

Ideally, it would also copy the original string in column A to Another sheet in column A and copy the value for total hours from BA to column B in the same sheet.

thoughts? and thanks.

 

[Bong]

set rng1=sheet1.usedrange.columns(1)
rw1=rng1.find(<string to find in column A>).row
rw2=sheet1.cells(rw1,6).end(xldown).row
set rng2=sheet1.range(cells(rw1,6),cells(rw2,6))
rw3=rng2.find("Total Hours").row
...etc

_________________
Bob Rashkin

 

[macropod]

Hi dingleberry,

You could do it with a formula like:
=IF(ISERROR(MATCH("6JV/EPFPL",Sheet1!A:A,0)),"",INDEX(Sheet1!BA:BA,MATCH("Total Hours",OFFSET(Sheet1!F1,MATCH("6JV/EPFPL",Sheet1!A:A,0),0,10000),0)+MATCH("6JV/EPFPL",Sheet1!A:A,0)))where 'Sheet1' is the name of the worksheet holding the data and the '10000' is the maximum potential number of data rows on that sheet.

Cheers
Paul Edstein
[MS MVP - Word]

 

[UnsolvedCoding]

Personally I would suggest using the replace function.

This is part of a sub that checks all the tabs in a workbook. Each tab is activated as info is pulled and then this is used to check for characters in the worksheet name that can't be allowed. J is the number of the tab in the workbook.

If InStr(1, ActiveWorkbook.Worksheets(J).Name, " ") > 0 Or _
InStr(1, ActiveWorkbook.Worksheets(J).Name, "'") > 0 Or _
InStr(1, ActiveWorkbook.Worksheets(J).Name, "#") > 0 Or _
InStr(1, ActiveWorkbook.Worksheets(J).Name, "&") > 0 Or _
InStr(1, ActiveWorkbook.Worksheets(J).Name, "@") > 0 Or _
InStr(1, ActiveWorkbook.Worksheets(J).Name, "-") > 0 Or _
InStr(1, ActiveWorkbook.Worksheets(J).Name, ".") > 0 Then
'make changes to tab name
ActiveWorkbook.Worksheets(J).Name = Replace(Replace(Replace(Replace(Replace(Replace(Replace(ActiveWorkbook.Worksheets(J).Name, _
" ", ""), "'", ""), "#", ""), "&", ""), "@", ""), "-", ""), ".", "")

End If

 

[macropod]

Personally I would suggest using the replace function.

How is this related to the OP's task?

Cheers
Paul Edstein
[MS MVP - Word]

 

[UnsolvedCoding]

I was thinking find and replace not find and copy. My bad.

To find and copy I use something similar to this and I realize its not an exact fit for what is desired. I also realize its written in a more bulky format then could be written.

Dim Find_Value as string
Dim i as long ' I is a long to account for new versions of Excel having more rows then will fit into an integer

' Assumes first row is header
i = 2

'last row is assumed to already have been determined

' loop through each cell
For i = 1 to lastrow

Do

' Check for the words total hours and remove all extra spaces
if trim(Range("F"&i).value) = "Total Hours"

' Copy the desired information from column F
Find_Value = Trim(Workbook(Workbookname).Sheets("Sheet1").Range("F"&i).value)

i = i + 1

Exit do

End if

loop



' Paste the info into same row of column BA of the other workbook
Workbooks(Workbookname).Worksheets("Sheet1").Range("BA" & i) = Find_Value

' Paste the info into B of the new location
Workbooks(Workbookname).Worksheets("Sheet1").Range("B" & i)= Find_Value

Find_Value = empty

' Copy the desired information from Column A
Find_Value = Trim(Workbook(Workbookname).Sheets("Sheet1").Range("A"&i).value)

' Paste the info into the other worksheet
Workbook(Workbookname).Sheets("Sheet2").Range("A"&i).value = Find_Value

if trim(Workbook(Workbookname).Sheets("Sheet1").Range("F"&i).value) = "" then exit sub

next i

Of course this will need to be adapted to fit so that the columns don't have empty spaces.