LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc,microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default Question on this conversion code switching between r1c1 to A1 format tia sal2

Greets All

I found this code on the web but I'm having problems getting it to work when I type in '=R1C1Converter(R1C2) I get an error
message and I don't know why?

Tia sal2


Function R1C1converter(Address As String, Optional R1C1_output As Integer, Optional RefCell As Range) As String
'Converts input address to either A1 or R1C1 style reference relative to RefCell
'If R1C1_output is xlR1C1, then result is R1C1 style reference.
'If R1C1_output is xlA1 (or missing), then return A1 style reference.
'If RefCell is missing, then the address is relative to the active cell
'If there is an error in conversion, the function returns the input Address string
Dim x As Variant
If RefCell Is Nothing Then Set RefCell = ActiveCell
If R1C1_output = xlR1C1 Then
x = Application.ConvertFormula(Address, xlA1, xlR1C1, , RefCell) 'Convert A1 to R1C1
Else
x = Application.ConvertFormula(Address, xlR1C1, xlA1, , RefCell) 'Convert R1C1 to A1
End If
If IsError(x) Then
R1C1converter = Address
Else

'If input address is A1 reference and A1 is requested output, then Application.ConvertFormula
'surrounds the address in single quotes.
If Right(x, 1) = "'" Then
R1C1converter = Mid(x, 2, Len(x) - 2)
Else
R1C1converter = x
End If
End If
End Function
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Question on this conversion code switching between r1c1 to A1 format tia sal2 temp Excel Discussion (Misc queries) 2 September 14th 07 08:58 PM
Text always switching to date format Brian Haberman Excel Discussion (Misc queries) 7 April 27th 07 03:24 PM
Question About Accessing Web Info Using R1C1 The Moose Excel Worksheet Functions 0 December 27th 06 02:10 AM
4+ Zip Code Conversion PalletHead Excel Discussion (Misc queries) 1 May 9th 06 10:35 PM
R1C1 format using a variable? jim37055 Excel Discussion (Misc queries) 3 October 4th 05 05:47 PM


All times are GMT +1. The time now is 10:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"