Range and RefEdit errors....

[Michael Nisgore]

Was having a real difficult time trying to get the RefEdit function of
my form to work, but just figured it out... and want to share it for
anyone with the same problem in the future...

Was having problems where a form and associated functions that were
previously working were now giving me errors in the degugger and
wouldn't run. As I have made several modifications to the project I
figured that a change I had made somewhere else had caused the
problem.
'*************************************************************
Dim UniversalSet As Range
Dim SubsetSet1 As Range
Dim SubsetSet2 As Range
Dim DestinationAddress As Range

Set UniversalSet = Range(RefEdit1.Text)
Set SubsetSet1 = Range(RefEdit2.Text)
Set SubsetSet2 = Range(RefEdit3.Text)
Set DestinationAddress = Range(RefEdit4.Text)
'*************************************************************
While degugging, when I hovered above RefEdit1.text it appeared to
have correctly captured the information "Report!R1C1:R1C47", however I
was getting the error message "'1004' Method 'Range' of object
'_Global' failed". I tried several different ideas from several other
threads, but missed the most obvious, the range object will not work
with R1C1 notation on!!!! To save yourself and your clients a big
headache don't forget to add this handy little snippet of code to ALL
of your RefEdit forms processing:

'*************************************************************
Dim UniversalSet As Range
Dim SubsetSet1 As Range
Dim SubsetSet2 As Range
Dim DestinationAddress As Range
Dim functionState As Integer
Dim offsetRow As Integer
Dim offsetCol As Integer

R1C1Warning_Continue:
Set UniversalSet = Range(RefEdit1.Text)
Set SubsetSet1 = sheets("Report").Range(RefEdit2.Text)
Set SubsetSet2 = sheets("Report").Range(RefEdit3.Text)
Set DestinationAddress = sheets("Report").Range(RefEdit4.Text)

<your code here>

On Error GoTo R1C1Warning
R1C1Warning:
Call MsgBox("This function cannot work with R1C1 reference style
enabled. Press OK to switch to A1 reference style and continue.",
vbOKOnly, "Turn off R1C1 Notation")
Application.ReferenceStyle = xlA1 'switches to A1 so program can
continue
Resume R1C1Warning_Continue
'*************************************************************
Best Regards,
Mike

 

[Tom Ogilvy]

This might be a little less intrusive.

Set UniversalSet = Range(cf(RefEdit1.Text))
Set SubsetSet1 = sheets("Report").Range(cf(RefEdit2.Text))
Set SubsetSet2 = sheets("Report").Range(cf(RefEdit3.Text))
Set DestinationAddress = sheets("Report").Range(cf(RefEdit4.Text))



Public Function cf(sStr As String)
If Application.Application.ReferenceStyle = xlA1 Then
cf = sStr
Else
cf = Application.ConvertFormula( _
sStr, xlR1C1, xlA1, xlRelative)
End Function