Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 20th, 2011, 09:17 AM
Friend of Wrox
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Excel VBA find and replace


I really need some help with this!!

I have the code below which loops though the D column, which is filled with forumlas. What this code does is looks at each of the formulas and finds the cell references within the forumla in the M column and replaces it with the value the correpsonding row in column L.

This code does work however i have noticed for a couple of the formulas it finds a cell reference inside a cell refernce for example it finds G4 inside G46.

Can any help explain why it is doing that for a only a few of the cell refernces?

Dim tempStr, OperatorPosition As String
Dim RefArray, ArrayElement As Variant
Dim FormulaColumn As Range
Dim Formula As Range
Dim LastRefRow, LastFromulaRow, Ndx1, RefPosition, FoundPosition As Long

Application.StatusBar = "Populating calculations column"
'Code to replace cell value
    LastRefRow = Cells(Rows.Count, "M").End(xlUp).Row
    LastFromulaRow = Cells(Rows.Count, "D").End(xlUp).Row
    'load the references and replacement values into an array
    RefArray = Range("L2:M" & LastRefRow).Value
    Set FormulaColumn = Range("D2:D" & LastFromulaRow)
    'this loop scans a formula looking for each of the references in column M
    For Each Formula In FormulaColumn
        tempStr = Formula.Text
        For Ndx1 = 1 To UBound(RefArray)
            'for each reference value in the formula
            RefPosition = InStr(tempStr, RefArray(Ndx1, 2))
            If RefPosition > 0 Then 'If the reference string is found
                For Each ArrayElement In Array("*", "/", "+", "-", ")") 'Then find the position of the operator to the right.
                    OperatorPosition = InStr(RefPosition, tempStr, ArrayElement)
                    'If statement to find the position of the operator
                    If OperatorPosition - RefPosition = Len(RefArray(Ndx1, 2)) _
                        Or Len(tempStr) - RefPosition = Len(RefArray(Ndx1, 2)) - 1 Then
                        tempStr = Replace(tempStr, RefArray(Ndx1, 2), RefArray(Ndx1, 1))
                        'If the string is in the middle then the operator gives the length of the string being looked for
                        ' If its not then exit the For loop as its not needed
                        Exit For
                    End If
                Next ArrayElement
            End If
Thank you


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace using VBA jeskit Excel VBA 8 June 6th, 2011 08:50 AM
Find and Replace 2 characters at once!! HELP scovitch65 VB.NET 2002/2003 Basics 2 March 18th, 2006 01:40 AM
Find and replace? Stuart Stalker SQL Server 2000 8 October 13th, 2005 02:49 AM
Create a find and a find and replace in VB.NET snowy0 VB How-To 0 January 26th, 2004 07:03 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.