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 June 3rd, 2011, 12:09 PM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Find and Replace using VBA

Hi

This is probably an odd question but its a problem i have been trying to solve for a few days and have i come up with nothing.

I have a workbook, which is created using my macro. The macro places foruma in column D, what i need to do is replace there cell reference's in the forumlas with a value.

SO far i have tried having the macro create a list of the cell references and the values (in column L and M) needed to be replaced with and doing a find a replace however this did not work as it replaced every cell ref it found eve if it was inside the value it had already replaced the cell ref with.

I then tried the same but insead of replacing the cell ref with the value i added a section which replaced the cel ref with a subsitute value (Column I) and then did a second find and replace of the subistitue value with the value. However this again did the same thing as before.

I have added the code i am using to do the find and replace on the D column


Code:
Z = WorksheetFunction.CountA(PPCWBSht.Range("L2:L20000"))

For Y = 1 To 2
    For X = 1 To Z
        Application.StatusBar = Y & " of " & 2 & "  :  Replacing cell ref with the CalcID : " & X & " of " & Z
        
        If Y = 1 Then
            FindRef = Range("M" & 1 + X)    'First loop with substitute values
            ReplRef = Range("I" & 1 + X)
        Else
            FindRef = Range("I" & 1 + X)    'Second loop with InpRef values
            ReplRef = Range("L" & 1 + X)
        End If
        
        Set MyRange = PPCWBSht.Range("D:D")
            MyRange.Replace What:=FindRef, Replacement:=ReplRef, LookAt:=xlPart, SearchOrder:=xlByRows, _
                MatchCase:=True, SearchFormat:=True, ReplaceFormat:=True
    Next X
Next Y
I have run out of ideas, Can anyone help me??

Thanks

jeskit
 
Old June 6th, 2011, 05:47 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Since you are deleting the formula and inserting some text, you are actually overwriting the text.

Hence you can just go ahead and place the text in column D

Here is a sample for you

Code:
 For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
    Cells(i1, 4).Value = "jeskit" & i1
 Next i1
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
jeskit (June 6th, 2011)
 
Old June 6th, 2011, 06:16 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

I probably should have given an example of the values in Column D. The values in column D look similar to this '=G4+H4+I4+J4+K4+L4+M4+N4+O4+P4+Q4+R4

When the loop goes through the cells in column D i want to look at the cell refs and then look for the cell ref in Column M and replace only the cell ref with the value in the corresponding row in Column L.
 
Old June 6th, 2011, 06:38 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Here is a hint

Code:
 For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
    sFormula = Cells(i1, 2).Formula
    sFormula = Replace(sFormula, "D", "E")
    Cells(i1, 2).Formula = sFormula
 Next i1
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
jeskit (June 6th, 2011)
 
Old June 6th, 2011, 06:56 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Just tying to intergrate your code and quick question

should sFormula be string variable?
 
Old June 6th, 2011, 07:01 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Quote:
Originally Posted by jeskit View Post
Hi

Just tying to intergrate your code and quick question

should sFormula be string variable?
Yes

IT should be

Code:
Dim sForumla As String
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old June 6th, 2011, 07:28 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Thanks!

I have tried to intergrate your code but i am not sure what its doing, could you possible explain each part of the code so i can intergrate it correctly please??
 
Old June 6th, 2011, 08:33 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

The following loops from second cell to the last cell containing data

For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row

'Formula from each cell of Column B (B2, B3 etc are stored in this variable

sFormula = Cells(i1, 2).Formula

' References to Column D are replaced with Column E in the formula (D2 gets changed to E2 and D3 to E3 etc
sFormula = Replace(sFormula, "D", "E")

' The formula of each cell B2, B3 is assigned with new freplaced forumla
Cells(i1, 2).Formula = sFormula
Next i1
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
jeskit (June 6th, 2011)
 
Old June 6th, 2011, 08:50 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Thumbs up

Ok I get it thank you!!

Thanks very much for your help





Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace 2 characters at once!! HELP scovitch65 VB.NET 2002/2003 Basics 2 March 18th, 2006 01:40 AM
find and replace code zabsmarty General .NET 0 March 15th, 2006 10:39 PM
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.