*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

--
Message posted from http://www.ExcelForum.com