Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional replacements with VBA
Howdy all,
I've got two spreadsheets, and old and new style, each with the same sheets. I've written a (fair) bit of code to take all the data from teh old style and dump it in the new style (just by copying over formulas from cell to cell). Problem is that cell references get messed up in the copying (I dont use VBA copy or anything, it uses Cell.Formula values). I also have a list of source/destination sheets and cells [the columns of this sheet are like [dest sheet | dest cell | source sheet | source cell] I have written a macro to go through cells in each sheet to change cell references from the source cell to the destination cell (eg in the source a cell has the formula '=((3761+262-40.5-386.6)/1000)-G8-G13-G14' and the destination cell should have the formula '=((3761+262-40.5-386.6)/1000)-G7-G12-G13'). The problem is that calculations in the new style spreadsheet also get updated. I have defined non-calculated cells by black text, and calculated text by red text (the above example is in black). Is there a way to get the replace to work ONLY on cells with red text? Included below is the code for the replacements Sub FixLinks() Dim SearchRange As Range Dim LastCell As String Dim curSheet As String LastCell = DetermineLastCell(ActiveSheet).Address(False, False) Set SearchRange = Range("A1:" & LastCell) curSheet = ActiveSheet.Name For i = 3 To DetermineLastCell(ThisWorkbook.Worksheets(2)).Row If curSheet = ThisWorkbook.Worksheets(2).Cells(i, 1).Value Then SearchRange.Replace What:=ThisWorkbook.Worksheets(2).Cells(i, 4).Value, Replacement:=ThisWorkbook.Worksheets(2).Cells(i, 2) End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine 2 VBA Replacements | Excel Discussion (Misc queries) | |||
Stop excel from reporting replacements | Excel Discussion (Misc queries) | |||
Regular expressions for replacements in Excel? | Excel Programming | |||
HowTo Count number of replacements | Excel Programming | |||
HowTo Count number of replacements | Excel Programming |