formula question
Posted by Celeste on January 14, 2002 3:58 PM
i have a column that has a pretty long IF formula in it. my formula reads as follows:
=IF(ISNUMBER(MATCH(T78,MDPRODUCTCODE,0)),(VLOOKUP(T78,MDPRODUCTMARKUP,4,0)),"ZZZ")
what i need to do, is change the "ZZZ" portion of the formula to say ,R78 (equal cell R78). here's my dilemma, i have a column of over 1000 lines of this formula in it. i want the formula to equal column R of the corresponding line. i'm wondering if there is a way, using the find/replace feature, that i can say to find "ZZZ" and replace it with the value in R on the corresponding line. i've done this before (many moons ago) on another spreadsheet program and was able to put in there some kind of little formula (i think it had an anstrick) so i wouldn't have to change each individual formula. filling down is not an option because there are many breaks and borders that would take even more time to put back.
any and all help would be appreciated
Posted by Russell Hauf on January 14, 2002 4:06 PM
Just change the ZZZ to RXX on the first line of your formula and then pull it down (XX being the number of the line you are on).
Hope this helps,
Russell
Posted by Aladin Akyurek on January 14, 2002 4:12 PM
Celeste --
It seems you want
"R"&ROW(T78)
to be plugged in to replace "ZZZ" in the formula.
You can prefix it with the sheet name if needed.
Right?
Aladin
========
Posted by Jacob on January 14, 2002 4:13 PM
Hi
We can do this with VBA
Assuming your data is in col A (modify as needed)
Option Explicit
Sub FixIt()
Dim x As Integer
Dim LastRow As Integer
Application.ScreenUpdating = False
LastRow = Range("A65536").End(xlUp).Row
For x = 1 To LastRow
Range("A" & x).Select
Selection.Replace What:="""zzz""", Replacement:="R" & x, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next x
End Sub
HTH
Jacob
Posted by Celeste on January 14, 2002 4:27 PM
looks like i can't explain things clearly, let's try again :-)
what i want to do is have the "ZZZ" portion of the formula read R74 on line 74, then the next row to say R75 on row 75, etc. so, if the formula cannot find the values it is looking for w/in the names that i have there, then i want it to equal the value in R on the corresponding row that the formula is in. if it is on row 100, then equal R100 if it can't find the value, etc. BUT, i cannot fill down, and i do not know code, so is there a way to put into the formula (using the find/replace feature) for it to say equal the value in R on the same row the formula is on???
Posted by Juno on January 14, 2002 4:52 PM
Re: looks like i can't explain things clearly, let's try again :-)
Do a find/replace (look in formulas) :-
Find : "ZZZ"
Rplace : Indirect("T", & Row())
Posted by Juno on January 14, 2002 4:54 PM
Correction ....
Correction :-
The Replace should be Indirect("R", & Row())
