Searching for text and displaying on another sheet...

**JRD** · 01-03-2005, 10:18 PM

I have a workbook that I use for building estimating. It has a number of sheets and the main estimating sheet is called "Estimate". I then have continuation sheets (the first is named "Continuation Pg" and subsequent ones (added as needed) are called "Continuation(2)" "Continuation(3)", etc.

I need to search the "Estimate" and all "Continuation" sheets for a text string, e.g., "Total Building Damages:" and when it is found, it will be in Col A of one of those sheets. I then need to display the value in Col F of that row in another sheet (named "Report"), in E21. Further, I need to display the value in Col G of the "Estimate" sheet (same row) in G21 of the "Report" worksheet. And so on for other values.

Since there should be only one occurrence of this text string in the workbook, it would be OK to search the entire workbook for this string.

Can anyone help me get started? I'm not all that familiar with VBA, but I'm getting lots of good info from the newsgroup and have bought a couple of books.
Hopefully, I'll start to pick it up.

TIA,

Jim

**gocush** · 01-04-2005, 12:47 AM

sub FindString()
dim MyString as String
dim sh as worksheet
dim cell as range

On Error Resume Next
MyString ="Total Building Damages:"
For each sh in worksheets
Set cell = Range("A:A").Find(What:=MyString)
Next sh

Msgbox "Your string was found in: " & sh.Name & cell.address

'To get the offset into your report sheet:

Sheets("Report").Range("Whaterver")=cell.offset(0,6) '6 columns to the right

end sub

Hope this helps you get started