Number in string manipulation, obtained by a cells().value

[Sintel]

*Background*: writing macro to automatically generate some pages base
on another worksheet. when i copy those pages (50 rows long) th
formulas referring to the previous worksheet get a +50 value, while th
value's are actually a few rows at the most lower.

*Actual problem*: I have the integer variable 'j' with the correct ro
number, I want to replace the row number in a formula from a cell wit
this 'j' value.

cells().value returns formulas that have this form:
=Investmentbudget!A7
=IF(Investmentbudget!K7<>"";Investmentbudget!K7;"")
with only the collumn (A etc.) and row (7 etc.) differing

In both cases i want to replace 7 with the value of 'j'. 7 can also b
25000 for example, so don't assume the number is only one digit long
The collumn can be left untouched.

No need to detect in which cells the change is done, I only need th
actual changing instructions, all the rest is already programmed.

It will be part of this submethod:


Code
-------------------

Private Sub BuildSheet(j)
Dim i As Integer

Worksheets("INV_fiches").Activate
i = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row

Rows("1:50").Copy
ActiveSheet.Paste Destination:=Rows(i)
Application.CutCopyMode = False

Cells(i + 3, 1).Value = ' change row in j
Cells(i + 3, 3).Value = ' change row in j
Cells(i + 3, 4).Value = ' change row in j
Cells(i + 3, 7).Value = ' change row in j
Cells(i + 3, 8).Value = ' change row in j
Cells(i + 3, 9).Value = ' change row in j
Cells(i + 3, 10).Value = ' change row in j
Cells(i + 3, 11).Value = ' change row in j

ActiveSheet.PageSetup.PrintArea = "$A$1:$L$" & i + 49
End Sub

-------------------


Thx beforehand for helping. If this kind of question was asked befor
plz point me to it, i did not find any appropriate results with th
search function

 

[Sintel]

Didn't think of this earlier; the value in the string that needs to b
changed into j can also be guessed, it's "i + 6". This may help i
coding the solution to this problem ^

 

[Tom Ogilvy]

Manually you would use
Edit=>Replace

this will work on all the cells selected, or if only one cell is selected,
all the cells on the sheet.

You can turn on the macro recorder and do it manually to see how this is
implemented in code. Then you can do your calculations to replace the
specific values you are interested in.

 

[Sintel]

Succeeded, used:

Rows(i + 3).Replace What:=Trim(Str(i + 6)), Replacement:=Trim(Str(j))

instead of all those cell instances.

Thx for the help