Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tricky search and replace
I need to search for a particular string in a very large spreadsheet, and
when I find it I need to replace it with the contents of, or a reference to, the cell 3 to the right and 2 above it. For example... Let's say the string is 'XXXX'. Lets say the 1st instance of 'XXXX' is in B10 and the 2nd is in B24. So what I want is for B10 to equal "=E8" (or the contents of E8) and B24 to equal "=E22" (or the contents of E22). This is for a one off manipulation of a large file (a1:bg56856). Any ideas...? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tricky search and replace
hmmm... nearly got it - this *should* work in theory, but it keeps giving an
error... =CELL("contents",ADDRESS(ROW()-2,5,4)) if I just put =ADDRESS(ROW()-2,5,4) then it returns a valid cell reference as text. if I put =CELL("contents",[validcellreference]) then that also works as expected. But when I put the 2 together it gives a formula error... and that's assuming it let's me replace the text with a formula like this... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tricky search and replace
John,
Try the macro below. HTH, Bernie MS Excel MVP Sub FindValues() Dim c As Range ' The cell found with what you want Dim d As Range ' All the cells found with what you want Dim myFindString As String Dim firstAddress As String myFindString = "XXXXX" With Cells Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole) If Not c Is Nothing Then Set d = c firstAddress = c.Address Else: MsgBox "Not Found" End End If Set c = .FindNext(c) If Not c Is Nothing And c.Address < firstAddress Then Do Set d = Union(d, c) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With 'Then put a formula in all those cells.... d.FormulaR1C1 = "=R[-2]C[3]" End Sub "john" wrote in message ... I need to search for a particular string in a very large spreadsheet, and when I find it I need to replace it with the contents of, or a reference to, the cell 3 to the right and 2 above it. For example... Let's say the string is 'XXXX'. Lets say the 1st instance of 'XXXX' is in B10 and the 2nd is in B24. So what I want is for B10 to equal "=E8" (or the contents of E8) and B24 to equal "=E22" (or the contents of E22). This is for a one off manipulation of a large file (a1:bg56856). Any ideas...? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tricky search and replace
Hi John,
replace XXXX with this =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,3) -- Allllen "john" wrote: hmmm... nearly got it - this *should* work in theory, but it keeps giving an error... =CELL("contents",ADDRESS(ROW()-2,5,4)) if I just put =ADDRESS(ROW()-2,5,4) then it returns a valid cell reference as text. if I put =CELL("contents",[validcellreference]) then that also works as expected. But when I put the 2 together it gives a formula error... and that's assuming it let's me replace the text with a formula like this... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tricky search and replace
replace XXXX with this
=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,3) ok, that woirks on an individual basis, but search and replace doesn't like it. looks like I'll have to go the macro route... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tricky search and replace
works fine for me, even with a few values.
what problem do you get with find and replace? -- Allllen "john" wrote: replace XXXX with this =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,3) ok, that woirks on an individual basis, but search and replace doesn't like it. looks like I'll have to go the macro route... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tricky search and replace
works fine for me, even with a few values.
what problem do you get with find and replace? oh yeah, so it does. When I said 'find' it found it, but when I pressed 'replace' it said it couldn't find it...(?) But if I select the whole xls first then it works - thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and replace chunks of html code | Excel Discussion (Misc queries) | |||
Unicode and Search and Replace | New Users to Excel | |||
Search & Replace to insert alt+ |
Excel Discussion (Misc queries) | |||
Search and replace | Excel Worksheet Functions | |||
GLOBAL search and replace | New Users to Excel |