0

I am trying to create a script in excel to duplicate sheets, rename with cell value from active sheet and then replace the formula values with the values from the row below on the main sheet labelled 'Master Working'. For the last part, I'm trying to amend the below code recorded with a macro such that instead of '19' to '20', the existing number in the formula becomes N and replaced with N+1. The formulas for each cell in the range are a simple lookup like the below from main tab.

='Master Working'!B19

Range("D5:D8").Select
Selection.Replace What:="19", Replacement:="20", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("B14:M14").Select
Selection.Replace What:="19", Replacement:="20", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
JSlocombe95
  • 1
  • The quick solution, not ideal, is to add a variable, `dim n as long: n = 19`, and inject it into the `Replace()` as needed. The more appropriate solution would involve knowing the range of replacements and most likely changing the method from replacing to another option. – Cyril Dec 20 '21 at 15:59
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Dec 20 '21 at 16:08
  • I have included in the start of my loop for the whole copy, rename, replace values a number variable which is input at the start of the script so that it will always be replacing the same range of cells for n = 19 up to n + m where m is the manually input number of times to repeat. Not sure if that makes a big difference to the script. – JSlocombe95 Dec 20 '21 at 16:16

1 Answers1

1

First read your value to replace into a variable N:

Dim N As Long  'read N value from master worksheet
N = ThisWorkbook.Worksheet("Master Working").Range("B19")

Then Replace using that variable N to replace it with N + 1.

ActiveSheet.Range("D5:D8").Replace What:=CStr(N), Replacement:=CStr(N + 1), LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
ActiveSheet.Range("B14:M14").Replace What:=CStr(N), Replacement:=CStr(N + 1), LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Note that I changed LookAt:=xlPart to LookAt:=xlWhole otherwies it will replace in 105193 the 19 by 20 like 105203 which is probably not what you want.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    OP is replacing parts of formulas, so would `xlWhole` work here? – Tim Williams Dec 20 '21 at 16:45
  • @TimWilliams OK I didn't get that I thought he wanted to replace the result value of a formula but then he needs to be careful still, because if there is eg a `119` somewhere in the formula this will change to `120` too not only the `19` to `20`. The replacement has to be chosen wisely then. – Pᴇʜ Dec 20 '21 at 20:55