Absolute references aren't absolute when pasting into Table

rinconpaul · Sep 20th 2017

I was hoping this wouldn't happen?
The problem is I have formulas in row 13 that looks at a range in the Table below. Every 5 secs the contents of row 15 are pasted to row 18 of a Table and the Table expands down a row to accomodate. Take cell O13 for example. When the Table was blank the COUNTIFS formula referred to ranges O$18:O18,K$18:K18. As the Table is populated despite the '$" absolute reference, the range moves down instead of expanding. At present in the screen capture there has been 32 5sec pastes carried out, and the range is now O$50:O50,K$50:K50 instead of O$18:O50,K$18:K50.

Is there a way round this? Cheers

TheGlovner · Sep 20th 2017

Re: Absolute references aren't absolute when pasting into Table

Can't download the sheet due to being at work.

What is creating the reference? Is that happening within some VBA code? If so post the bit of code that creates the string and then passes it to the cell. That's no doubt where your issue is.

My suspicion would be that the code tracks the row it's on as a variable and then creates a string for the reference something like:

Code

strRefString = "O$" & lngRowNumber & ":O" & lngRowNumber & ",K$" & lngRowNumber & ":K" & & lngRowNumber

When it should be something like:

Code

strRefString = "O$" & lngStartRow & ":O" & lngRowNumber & ",K$" & lngStartRow & ":K" & & lngRowNumber

rinconpaul · Sep 20th 2017

Re: Absolute references aren't absolute when pasting into Table

Thanks TheGlovner. Sorry for the late reply, it's been beddybyes time :sleep:

Recently this was published on the same issue: https://excel.tips.net/T002876…ferences_in_Formulas.html

I'll explore his solutions. This is my code called every 5 secs:

Code

Sub Record_data()
'
' Record_data Macro
'
Sheets("Data").Select
    Sheets("Data").Rows("15:15").Select
    Sheets("Data").Range("B15").Activate
    Selection.Copy
    Sheets("Data").Rows("17:17").Select
    Sheets("Data").Range("B17").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Rows("17:17").Select
    Sheets("Data").Range("B17").Activate
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
End Sub

Display More

Maybe there is a code work round too?

rinconpaul · Sep 20th 2017

Re: Absolute references aren't absolute when pasting into Table

I tried giving the ranges a Defined Name and deleted the Table, but NO, the range still moves down with every update....a real pain!

rinconpaul · Sep 20th 2017

Re: Absolute references aren't absolute when pasting into Table

Following Allen Wyatt's advice, INDIRECT seems to solve my problem. The formula in O13 becomes =COUNTIFS(INDIRECT("O18"):O100,O15,INDIRECT("K18"):K100,">0")
:eureka:

Absolute references aren't absolute when pasting into Table

Participate now!

Share