VBA to add 200 rows to every formula

[tonywatsonhelp]

Hi everyone,
I'm super stuck,

I've just created a sheet with lots of links to another sheet,
a lot of these links are in megered cells and but i want to copy the sheet and do the same links but 200 rows lower
however if i change the row numbers and pull down i get loads of problems such as it taking the next row as the 4merged rows etc.
lets just say i have a problem!

so here what i need,

i choose a range
and run the vba and it adds 200 rows to every formula in the range

so "='Questions Raw'!G2" fpr example becomes "='Questions Raw'!G202"

please help if you can

Thanks

Tony

 

[offthelip]

The best way of doing that is to get rid of the merged cells. Using merged cells creates all sorts of problems as you have discovered. Since they are usually often used just to get the display looking right you can usually achieve the same display results by formatting i.e using "centre accross selection" Using merged cells with vba is also a problem , so to write vba to do what you want is not that easy.

 

[tonywatsonhelp]

Offthelip, thank you for your input but that was about as useful as a chocolate teapot, if you cant help please stay quiet as I'm sure there are some people more experienced then you that can provide the help i need, i could sit here all day looking at post on this site saying "You should have done it differently" but we live in the real world were we are stuck with choices that have been made, the reason i used the words "Lets just say i have a problem" is because i know / now realise i messed up, but i cant fix an entire page with hindsight! I need real help. I don't mean to be harsh but you are not helping telling me things i already know! I made it clear I had a problem and need help, I don't think its beyond the skills of some of the amazing talents on here to devise a solution to my problem. I just hope that having to reply to your unhelpful comment does not impair my ability to be helped by those that can.

 

[jasonb75]

I'm sure there are some people more experienced then you

Based on what evidence?

@offthelip has suggested a way to fix the problem that you have, which is most likely the result of them being more experienced that you. In my opinion, if you knew better than them then you wouldn't be asking for help in the first place.

I looked at your post yesterday and chose not to reply, based on the fact that what you are asking for is quite unrealistic (if even possible in the grand scheme of things), similar questions have been asked before and success has been minimal. As a rule of thumb, you would need to forget that the current formulas exist and create new ones based on logical patterns in the layout of the sheet.

It might be possible to use the worksheet FORMULATEXT function to extract the formula and create an offset, but only with simple formulas such as the single example provided. With anything more than a single cell reference, it will not work.

I don't think its beyond the skills of some of the amazing talents on here to devise a solution to my problem.

The solution is better planning of your sheet layout, as suggested in post 2. Anything else is just an attempt to polish something that will never shine.

 

[tonywatsonhelp]

Hi jasonb75,
Thank you for your input,

 

[lrobbo314]

Give this a shot.

Sub TWOHUNDRED()
Dim r As Range:     Set r = Selection
Dim RX As Object:   Set RX = CreateObject("VBScript.RegExp")
Dim c As Range
Dim RO As Integer
Dim FT As String

With RX
    .Pattern = "(!\w+)(\d+)"
    
    For Each c In r
        If c.Formula <> "" Then
            FT = c.Formula
            Set matches = .Execute(FT)
            RO = matches(0).submatches(1) + 200
            FT = .Replace(FT, matches(0).submatches(0) & RO)
            c.Formula = FT
        End If
    Next c
End With
End Sub

 

[tonywatsonhelp]

Thank you Irobbo314,
this is great Thanks
Tony