Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying many cells with find/replace without losing unique data. pls help
Hi,
Perhaps it is easier to explain what I am trying to do... IF('New Look Nil Reports'!B43=1,"NIL","NON") Basically the above is how it is at present, checking if a sheet has a one in B43. I need to do the following =+IF(Z7=1," ",IF('New Look Nil Reports'!B43=1,"NIL","NON")) Whereas the Z7 relates to the cell to the left; this will then show a blank (a space actually) in the cell, rather than the NIL or NON label. My problem is that the B43 section is not sequential, so I cannot use a fill; the next line may be B12 or B65. So I will need to find and replace +IF( with +IF(LEFTCELL=1,"",IF( Where LEFTCELL relates to the command/function to check the cell to the left. and I will need to add a close parenthesis at the end..... Otherwise I will have to enter it all manually, and CRTL/SHIFT+ENTER for the curly brackets as it is a array... I am learning slowly, but this eludes me.. Many thanks Ty |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying many cells with find/replace without losing unique data. pls help
Ty,
The difficulty is in balancing the parentheses. The best way is to use VBA. Copy the macro below, place it in a codemodule, select the cells with the formulas to be modified, then run the macro. HTH, Bernie MS Excel MVP Sub FixTysFormulas() Dim myCell As Range Dim myForm As String For Each myCell In Selection myForm = myCell.Formula myForm = Replace(myForm, "=", _ "=IF(Z" & myCell.Row & "1,"" "",", 1, 1) & ")" myCell.Formula = myForm Next myCell End Sub "Ty Williams" wrote in message ... Hi, Perhaps it is easier to explain what I am trying to do... IF('New Look Nil Reports'!B43=1,"NIL","NON") Basically the above is how it is at present, checking if a sheet has a one in B43. I need to do the following =+IF(Z7=1," ",IF('New Look Nil Reports'!B43=1,"NIL","NON")) Whereas the Z7 relates to the cell to the left; this will then show a blank (a space actually) in the cell, rather than the NIL or NON label. My problem is that the B43 section is not sequential, so I cannot use a fill; the next line may be B12 or B65. So I will need to find and replace +IF( with +IF(LEFTCELL=1,"",IF( Where LEFTCELL relates to the command/function to check the cell to the left. and I will need to add a close parenthesis at the end..... Otherwise I will have to enter it all manually, and CRTL/SHIFT+ENTER for the curly brackets as it is a array... I am learning slowly, but this eludes me.. Many thanks Ty |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying many cells with find/replace without losing unique data. pls help
As Homer would say, "D'Oh!"
I'm sorry. I re-read your post, and realized that you probably want: Sub FixTysFormulas() Dim myCell As Range Dim myForm As String For Each myCell In Selection myForm = myCell.Formula myForm = Replace(myForm, "=", _ "=IF(" & myCell(1,0).Address(False,False) & "1,"" "",", 1, 1) & ")" myCell.Formula = myForm Next myCell End Sub Still not sure why you expect to array enter this, though.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ty, The difficulty is in balancing the parentheses. The best way is to use VBA. Copy the macro below, place it in a codemodule, select the cells with the formulas to be modified, then run the macro. HTH, Bernie MS Excel MVP Sub FixTysFormulas() Dim myCell As Range Dim myForm As String For Each myCell In Selection myForm = myCell.Formula myForm = Replace(myForm, "=", _ "=IF(Z" & myCell.Row & "1,"" "",", 1, 1) & ")" myCell.Formula = myForm Next myCell End Sub "Ty Williams" wrote in message ... Hi, Perhaps it is easier to explain what I am trying to do... IF('New Look Nil Reports'!B43=1,"NIL","NON") Basically the above is how it is at present, checking if a sheet has a one in B43. I need to do the following =+IF(Z7=1," ",IF('New Look Nil Reports'!B43=1,"NIL","NON")) Whereas the Z7 relates to the cell to the left; this will then show a blank (a space actually) in the cell, rather than the NIL or NON label. My problem is that the B43 section is not sequential, so I cannot use a fill; the next line may be B12 or B65. So I will need to find and replace +IF( with +IF(LEFTCELL=1,"",IF( Where LEFTCELL relates to the command/function to check the cell to the left. and I will need to add a close parenthesis at the end..... Otherwise I will have to enter it all manually, and CRTL/SHIFT+ENTER for the curly brackets as it is a array... I am learning slowly, but this eludes me.. Many thanks Ty |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying many cells with find/replace without losing unique data. pls help
Thank you thank you thank you!
Apart from the very slight modification of =1 the macro worked a treat! I thought it needed to be entered as an array with the curly brackets {} cos the other cell it relates to is {=COUNT(IF('Data Entry Complaints'!$N$2:$N$799=Array!$A60,IF('Data Entry Complaints'!$B$2:$B$799=4,0)))} which checks an area against a list, and also if in this case the entry was in April... Many thanks again. Ty On Thu, 16 Sep 2004 13:25:39 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: As Homer would say, "D'Oh!" I'm sorry. I re-read your post, and realized that you probably want: Sub FixTysFormulas() Dim myCell As Range Dim myForm As String For Each myCell In Selection myForm = myCell.Formula myForm = Replace(myForm, "=", _ "=IF(" & myCell(1,0).Address(False,False) & "1,"" "",", 1, 1) & ")" myCell.Formula = myForm Next myCell End Sub Still not sure why you expect to array enter this, though.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ty, The difficulty is in balancing the parentheses. The best way is to use VBA. Copy the macro below, place it in a codemodule, select the cells with the formulas to be modified, then run the macro. HTH, Bernie MS Excel MVP Sub FixTysFormulas() Dim myCell As Range Dim myForm As String For Each myCell In Selection myForm = myCell.Formula myForm = Replace(myForm, "=", _ "=IF(Z" & myCell.Row & "1,"" "",", 1, 1) & ")" myCell.Formula = myForm Next myCell End Sub "Ty Williams" wrote in message ... Hi, Perhaps it is easier to explain what I am trying to do... IF('New Look Nil Reports'!B43=1,"NIL","NON") Basically the above is how it is at present, checking if a sheet has a one in B43. I need to do the following =+IF(Z7=1," ",IF('New Look Nil Reports'!B43=1,"NIL","NON")) Whereas the Z7 relates to the cell to the left; this will then show a blank (a space actually) in the cell, rather than the NIL or NON label. My problem is that the B43 section is not sequential, so I cannot use a fill; the next line may be B12 or B65. So I will need to find and replace +IF( with +IF(LEFTCELL=1,"",IF( Where LEFTCELL relates to the command/function to check the cell to the left. and I will need to add a close parenthesis at the end..... Otherwise I will have to enter it all manually, and CRTL/SHIFT+ENTER for the curly brackets as it is a array... I am learning slowly, but this eludes me.. Many thanks Ty |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find/replace data in selected cells only | Excel Worksheet Functions | |||
merging cells and not losing data | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
How to find multiple cells/replace whole cells w/data | Excel Discussion (Misc queries) | |||
Modifying a Formula To display only Unique Values | Excel Worksheet Functions |