Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #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: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
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
 



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 10:59 PM.


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