Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2017, 01:19 AM
Filip88 Filip88 is offline Referencing to cells by FormulaR1C1 Windows 10 Referencing to cells by FormulaR1C1 Office 2016
Novice
Referencing to cells by FormulaR1C1
 
Join Date: Jan 2017
Posts: 12
Filip88 is on a distinguished road
Default Referencing to cells by FormulaR1C1

Hello,



I am trying to program a code with FormulaR1C1, referencing to cell that would be moved when I scale down the formula.

In cells C2 and D2, I am having data, added in variables row and cell. in variable C6, I am having FormulaR1C1 with variables row and cell (including data from C2 and D2).

In C6, I have formula referencing to cell A4, when I want to scale down cell C6 -> C7 - > C8 with this formula, I would like to scale down also the referenced cell A4 -> A5 -> A6.

- cell C6 refers to A4
- cell C7 refers to A5
- cell C8 refers to cell A6

The problem is that the cell is always fixed in the same row, where formula is, so cell C6 refers to A6, cell C7 refers to A7 etc.

My code:

Code:
Sub MakroTest1()
 
row = D2
coll = [C2]
 
Worksheets("List1").Range("C6").FormulaR1C1 = "=R" & row & "C" & coll & ""
 
End Sub
Would you have any suggestion how to move referenced cell with this formulaR1C1? I have attached the excel file and print screen.

I am really lost. Thank you

Filip
Attached Images
File Type: jpg image.jpg (166.9 KB, 11 views)
Attached Files
File Type: xlsm Macro_test.xlsm (16.8 KB, 8 views)
Reply With Quote
  #2  
Old 01-20-2017, 04:47 PM
NoSparks NoSparks is offline Referencing to cells by FormulaR1C1 Windows 7 64bit Referencing to cells by FormulaR1C1 Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

Quote:
Would you have any suggestion how to move referenced cell with this formulaR1C1?
No. But if you're willing to use a 'normal' formula, this macro along with the UDF, will put a drag-able formula into C6.
Code:
Sub MacroTest2()
    With Worksheets("List1")
        ro = Range("d2").Value
        col = Range("c2").Value
        .Range("C6").Formula = "=$" & ColumnLetter(CLng(col)) & ro
    End With
End Sub
Code:
Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long, c As Byte, s As String
    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function
Reply With Quote
  #3  
Old 01-21-2017, 07:51 AM
Filip88 Filip88 is offline Referencing to cells by FormulaR1C1 Windows 10 Referencing to cells by FormulaR1C1 Office 2016
Novice
Referencing to cells by FormulaR1C1
 
Join Date: Jan 2017
Posts: 12
Filip88 is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
No. But if you're willing to use a 'normal' formula, this macro along with the UDF, will put a drag-able formula into C6.
I tried your UDF and it works, thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formating all cells in an array based on adjacent cells deejay Excel 6 12-20-2016 12:00 PM
Referencing to cells by FormulaR1C1 Linking cells: can sub-cells move with their master-cell when sorting? kw01 Excel 1 06-30-2015 05:02 PM
Copying text range of cells to different cells adds an extra line jpb103 Word VBA 2 07-23-2014 12:22 PM
Naming Cells for formula referencing lynchbro Excel 6 06-26-2014 07:45 AM
Referencing darksupernova Word 4 11-08-2012 03:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft