CODEWITHSUNDEEP
excelvba
JSlocombe95
JSlocombe95

Reputation: 1

Replace formula number values with number plus 1

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

Upvotes: 0

Views: 225

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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.

Upvotes: 1

Related Questions