Find and Replace Excel VBA - replace values???[RESOLVED]

[Mitch_s_s]

Using Office 2007, but writing code for excel 2003 in work.

I am trying to replace any cell with the #N/A error, with a unique number
so i will find the first N/A then replace it with 001, then the next would be 002

the problem i am having is when i attempt to find and replace, it isnt allowing me to replace values...
is this new to 2007? when i use find, i can select values, just doesnt show in the drop down for replace.

hope this had made sense, i tend to go off on a tangent lol

[Mitch_s_s]

looks like i'm going to have to loop through the range of cells, which is gonna take a while
was looking to use excels built in function.

if anyone has any ideas, please let me know.

i was thinking for-next loop
if cell.value = "#N/A" then cell.value = unique_ref
next cell etc?

what you think?

[Siddharth Rout]

Yes

you can use replace values but before that you need to convert #N/A to text format.

Lets say the data is from B1:B7 then try this...

Private Sub CommandButton1_Click()
 
Dim count As Integer
 
'paste values
Range("B1:B7").Copy
Range("B1:B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
'replace "#N/A" with "replword"
Cells.Replace What:="#N/A", Replacement:="replword", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
'unique number
count = 1
 
For i = 1 To 7
If Trim(Range("b" & i).Value) = "replword" Then
    Range("b" & i).Value = count
    count = count + 1
End If
Next i
End Sub

[Mitch_s_s]

cheers man, i knew you'd know!!!

will check it out, and post back!!

nice one!

[Mitch_s_s]

ended up with this
can i trim this down, or does it have to find it first, i select cell I1 each time, i try to keep away from selectin cells, but this would miss the first instance if i didnt (wouldnt it :s)

Columns("I:I").Find(What:="#N/A", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Replace What:="#N/A", Replacement:=unique_ref, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

[Siddharth Rout]

Hi Mitch

Like I said earlier, you need to convert #N/A to text and then replace it.

Also, how will the unique ref change automatically with your code?

That is the reason why you will have to use a loop like i suggested above and increase the value of the unique reference...

You can also use Application.Screenupdating = False in the begining of the code to speed up things... and at the end of the code set it to 'true' again...

Hope this helps...

[Mitch_s_s]

gutted, after all the messing round they dont need it any more haha!

at least i learnt somethin!

tar skids!

[Mitch_s_s]

oh and i already had the loop sorted
it would add 1 each time it replaced

[unknowninator]

Hi,

I have a somewhat similar problem, and I am fairly new to VB scripting.
Although I do have a coding background.

Anyways, the problem at hand is:-

I have a column of floor codes, with entries like:-

4th floor
3rd floor
5th floor
29th floor

etc... now, i need to write a script like the stock find and replace provided in excel which replaces every instance of 4th with Fourth and similarly 3rd with Third etc....

Can anyone please help me with it??

I believe, it is fairly easy if someone knows scripting, since all that needs to be done is just specify a range and lots of if cases or using the in between function etc.

Please help me out.

Many Thanks.
Andy

[Siddharth Rout]

>>>Although I do have a coding background.

Say What? I will give you the code but let's do one thing. You show me what you tried and then we will take it form there?

BTW, welcome to the forums Andy

Sid

[DiViDeD]

OK, very different approach here, but if the #N/A is a formula result, why not trap it at source? I tend to wrap any functions which might return an error in a condition (eg IF(ISNA(function),Alternative Result,function). This can also be useful when wanting to categorise a big chunk of data, for example when you have 3 or 4 lists of account numbers with categories, but you know that there will still be account numbers not on any list. A nicely nested ISNA VLOOKUP function allows you to categorise everything and put anything which is totally unfound into 'Other' or 'corporate', type 9999 or whatever.
I'm a lazy programmer. If I find myself having to clear errors after the fact, I'd rather find a way of stopping them happening in the first place.

[DTrahan]

I would love to stop the errors on my worksheets, BUT the errors occur when macro to get the data is run, it is retrieving data from a database. Example of formula in several cells is {=PISampDat($AY$8,AF$1,$AF$2,$AG$1,0,)} If there is no data to retrieve, other cells that have formula's to calculate the data retrieved from these cells end up with #N/A, then macro which places this value somewhere else places #N/A in those cells. I'm at a loss, thought this forum could help but it didn't. Wish I knew how to post a question???