ExcelBanter - refedit error

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - refedit error (https://www.excelbanter.com/excel-programming/390083-refedit-error.html)

With this code I obtain a 'Method of 'Range' object '_Global' failed error:

Dim Addr As String
Dim SelRange As Range

Addr = frmImport.RefEdit1.Text

Set SelRange = Range(Addr)

This code is in a Button select event on a userform.

Your variable "addr" does not evaluate to a cell location.
Just guessing, but maybe if you dropped the .Text from the
variable assignment, it might work.

"Steve" wrote:

With this code I obtain a 'Method of 'Range' object '_Global' failed error:

Dim Addr As String
Dim SelRange As Range

Addr = frmImport.RefEdit1.Text

Set SelRange = Range(Addr)

This code is in a Button select event on a userform.

After reading your post again, I'm not so sure that my first suggestion would
work either. Anyhow, you need to use a cell reference to do what you are
trying with the Range(?).

"Steve" wrote:

With this code I obtain a 'Method of 'Range' object '_Global' failed error:

Dim Addr As String
Dim SelRange As Range

Addr = frmImport.RefEdit1.Text

Set SelRange = Range(Addr)

This code is in a Button select event on a userform.

The code worked ok for me.

If I selected a range, then I got the address in the msgbox.

If I didn't select a range, then I got the error message.

Maybe it's time to describe exactly what you did.

Steve wrote:

The problem isn't with the Addr it's with the SelRange. Now, it doens't
matter whether or not I select a range the SelRange variable is still equal
to nothing.

"Dave Peterson" wrote:

I'd do some checking:

Option Explicit
Private Sub CommandButton1_Click()
Dim Addr As String
Dim SelRange As Range

Addr = Me.RefEdit1.Text

Set SelRange = Nothing
On Error Resume Next
Set SelRange = Range(Addr)
On Error GoTo 0

If SelRange Is Nothing Then
MsgBox "Please select a valid range"
Exit Sub
End If

'do the real work
MsgBox SelRange.Address(external:=True)
End Sub

The me. stuff refers to the thing that owns the code--in this case, the
userform. If I use Me., I don't have to worry about the name of the userform.

Steve wrote:

With this code I obtain a 'Method of 'Range' object '_Global' failed error:

Dim Addr As String
Dim SelRange As Range

Addr = frmImport.RefEdit1.Text

Set SelRange = Range(Addr)

This code is in a Button select event on a userform.

--

Dave Peterson

--

Dave Peterson

"Steve" wrote in message
...
With this code I obtain a 'Method of 'Range' object '_Global' failed
error:

Dim Addr As String
Dim SelRange As Range

Addr = frmImport.RefEdit1.Text

Set SelRange = Range(Addr)

This code is in a Button select event on a userform.

Hi Steve,
the RefEdit only works if your sheet is in the $A style not in the R1C1
style

you have two choises :

1) Change the style to R1C1 by VBA (and setting it back if that was what the
user wants)
2)Issue a warning to the user that he must set the R1C1

Tools = Options = General = Check R1C1

Here is an example for the VBA solution that works for me :

========================

Option Explicit
Public Change As Boolean
Sub HeatMap()
'
' Gys de Jongh Feb-2006
' This Routine Constructs a HeatMap
' on the Selected Range in Excel
'
'First Check the reference style of this user
'The Refedit Box only works in the xlA1 style
'Store it in the Public variable Change to set
'it back for the user after the Routine exits
'
Change = False
If Application.ReferenceStyle = xlR1C1 Then
Change = True
Application.ReferenceStyle = xlA1
End If
'
' Choose Color Serie 1 and Highest = Red
' as the Defaults
'
UserForm1.OptionButton1.Value = True
UserForm1.OptionButton3.Value = True
UserForm1.RefEdit1 = ""
UserForm1.RefEdit1.SetFocus
UserForm1.Show
End Sub
==========================================

Option Explicit
Private Sub CommandButton1_Click()
'
'
' Gys de Jongh Feb-2006
' This Routine Constructs a HeatMap
' on the Selected Range in Excel
'
' This is an extension of the Routine
' RangeColors
'
' The value of the cells determines
' their color via two possible CodeTables
' The user can choose to make the highest
' value Red or Blue
'
Dim I As Integer
Dim N As Integer
Dim MyColorCode As Range
Dim NR As Integer
Dim NC As Integer
Dim R As Integer
Dim C As Integer
Dim D As Integer
Dim Z As Single
Dim Min As Single
Dim Max As Single
'
' The CodeTabels contain the color indexes
' for incremental values
'
Dim CodeTabel(12) As Integer
Dim CodeTabel1(12) As Integer
Dim CodeTabel2(12) As Integer
'
CodeTabel1(1) = 11
CodeTabel1(2) = 5
CodeTabel1(3) = 41
CodeTabel1(4) = 37
CodeTabel1(5) = 8
CodeTabel1(6) = 34
CodeTabel1(7) = 19
CodeTabel1(8) = 27
CodeTabel1(9) = 44
CodeTabel1(10) = 45
CodeTabel1(11) = 46
CodeTabel1(12) = 3
'
CodeTabel2(1) = 11
CodeTabel2(2) = 5
CodeTabel2(3) = 41
CodeTabel2(4) = 37
CodeTabel2(5) = 34
CodeTabel2(6) = 2
CodeTabel2(7) = 19
CodeTabel2(8) = 27
CodeTabel2(9) = 44
CodeTabel2(10) = 45
CodeTabel2(11) = 46
CodeTabel2(12) = 3
'
For I = 1 To 12
CodeTabel(I) = CodeTabel1(I)
Next I
If OptionButton2.Value Then
For I = 1 To 12
CodeTabel(I) = CodeTabel2(I)
Next I
End If
On Error Resume Next
Set MyColorCode = Range(RefEdit1)
NR = MyColorCode.Rows.Count
NC = MyColorCode.Columns.Count
'
Z = MyColorCode.Cells(1, 1)
Min = Z
Max = Z
'
For R = 1 To NR
For C = 1 To NC
Z = MyColorCode.Cells(R, C)
If Z Max Then
Max = Z
End If
If Z < Min Then
Min = Z
End If
Next C
Next R
'
For R = 1 To NR
For C = 1 To NC
Z = MyColorCode.Cells(R, C)
N = Int((Z - Min) / (Max - Min) * 11 + 1.5)
D = N
If OptionButton4 Then
D = 13 - N
End If
MyColorCode.Cells(R, C).Interior.ColorIndex = CodeTabel(D)
Next C
Next R
'
'
End Sub

Private Sub CommandButton2_Click()
If Change Then
Application.ReferenceStyle = xlR1C1
End If
UserForm1.Hide
End Sub

=================================================

If you need the sheet mail me directly

hth
Gys

Yes, it was that I had excel in the R1C1 mode. Thanks for all the help, now
it works just fine.

Steve

"GysdeJongh" wrote:

"Steve" wrote in message
...
With this code I obtain a 'Method of 'Range' object '_Global' failed
error:

Dim Addr As String
Dim SelRange As Range

Addr = frmImport.RefEdit1.Text

Set SelRange = Range(Addr)

This code is in a Button select event on a userform.

Hi Steve,
the RefEdit only works if your sheet is in the $A style not in the R1C1
style

you have two choises :

1) Change the style to R1C1 by VBA (and setting it back if that was what the
user wants)
2)Issue a warning to the user that he must set the R1C1

Tools = Options = General = Check R1C1

Here is an example for the VBA solution that works for me :

========================

Option Explicit
Public Change As Boolean
Sub HeatMap()
'
' Gys de Jongh Feb-2006
' This Routine Constructs a HeatMap
' on the Selected Range in Excel
'
'First Check the reference style of this user
'The Refedit Box only works in the xlA1 style
'Store it in the Public variable Change to set
'it back for the user after the Routine exits
'
Change = False
If Application.ReferenceStyle = xlR1C1 Then
Change = True
Application.ReferenceStyle = xlA1
End If
'
' Choose Color Serie 1 and Highest = Red
' as the Defaults
'
UserForm1.OptionButton1.Value = True
UserForm1.OptionButton3.Value = True
UserForm1.RefEdit1 = ""
UserForm1.RefEdit1.SetFocus
UserForm1.Show
End Sub
==========================================

Option Explicit
Private Sub CommandButton1_Click()
'
'
' Gys de Jongh Feb-2006
' This Routine Constructs a HeatMap
' on the Selected Range in Excel
'
' This is an extension of the Routine
' RangeColors
'
' The value of the cells determines
' their color via two possible CodeTables
' The user can choose to make the highest
' value Red or Blue
'
Dim I As Integer
Dim N As Integer
Dim MyColorCode As Range
Dim NR As Integer
Dim NC As Integer
Dim R As Integer
Dim C As Integer
Dim D As Integer
Dim Z As Single
Dim Min As Single
Dim Max As Single
'
' The CodeTabels contain the color indexes
' for incremental values
'
Dim CodeTabel(12) As Integer
Dim CodeTabel1(12) As Integer
Dim CodeTabel2(12) As Integer
'
CodeTabel1(1) = 11
CodeTabel1(2) = 5
CodeTabel1(3) = 41
CodeTabel1(4) = 37
CodeTabel1(5) = 8
CodeTabel1(6) = 34
CodeTabel1(7) = 19
CodeTabel1(8) = 27
CodeTabel1(9) = 44
CodeTabel1(10) = 45
CodeTabel1(11) = 46
CodeTabel1(12) = 3
'
CodeTabel2(1) = 11
CodeTabel2(2) = 5
CodeTabel2(3) = 41
CodeTabel2(4) = 37
CodeTabel2(5) = 34
CodeTabel2(6) = 2
CodeTabel2(7) = 19
CodeTabel2(8) = 27
CodeTabel2(9) = 44
CodeTabel2(10) = 45
CodeTabel2(11) = 46
CodeTabel2(12) = 3
'
For I = 1 To 12
CodeTabel(I) = CodeTabel1(I)
Next I
If OptionButton2.Value Then
For I = 1 To 12
CodeTabel(I) = CodeTabel2(I)
Next I
End If
On Error Resume Next
Set MyColorCode = Range(RefEdit1)
NR = MyColorCode.Rows.Count
NC = MyColorCode.Columns.Count
'
Z = MyColorCode.Cells(1, 1)
Min = Z
Max = Z
'
For R = 1 To NR
For C = 1 To NC
Z = MyColorCode.Cells(R, C)
If Z Max Then
Max = Z
End If
If Z < Min Then
Min = Z
End If
Next C
Next R
'
For R = 1 To NR
For C = 1 To NC
Z = MyColorCode.Cells(R, C)
N = Int((Z - Min) / (Max - Min) * 11 + 1.5)
D = N
If OptionButton4 Then
D = 13 - N
End If
MyColorCode.Cells(R, C).Interior.ColorIndex = CodeTabel(D)
Next C
Next R
'
'
End Sub

Private Sub CommandButton2_Click()
If Change Then
Application.ReferenceStyle = xlR1C1
End If
UserForm1.Hide
End Sub

=================================================

If you need the sheet mail me directly

hth
Gys

Good catch.

This seemed to work ok, too:

Option Explicit
Private Sub CommandButton1_Click()
Dim Addr As String
Dim SelRange As Range

Addr = Me.RefEdit1.Text

If Application.ReferenceStyle = xlR1C1 Then
Addr = Application.ConvertFormula(Formula:=Addr, _
fromreferencestyle:=xlR1C1, toreferencestyle:=xlA1)
End If

Set SelRange = Nothing
On Error Resume Next
Set SelRange = Range(Addr)
On Error GoTo 0

If SelRange Is Nothing Then
MsgBox "Please select a valid range"
Exit Sub
End If

'do the real work
MsgBox SelRange.Address(external:=True)
End Sub

GysdeJongh wrote:

"Steve" wrote in message
...
With this code I obtain a 'Method of 'Range' object '_Global' failed
error:

Dim Addr As String
Dim SelRange As Range

Addr = frmImport.RefEdit1.Text

Set SelRange = Range(Addr)

This code is in a Button select event on a userform.

Hi Steve,
the RefEdit only works if your sheet is in the $A style not in the R1C1
style

you have two choises :

1) Change the style to R1C1 by VBA (and setting it back if that was what the
user wants)
2)Issue a warning to the user that he must set the R1C1

Tools = Options = General = Check R1C1

Here is an example for the VBA solution that works for me :

========================

Option Explicit
Public Change As Boolean
Sub HeatMap()
'
' Gys de Jongh Feb-2006
' This Routine Constructs a HeatMap
' on the Selected Range in Excel
'
'First Check the reference style of this user
'The Refedit Box only works in the xlA1 style
'Store it in the Public variable Change to set
'it back for the user after the Routine exits
'
Change = False
If Application.ReferenceStyle = xlR1C1 Then
Change = True
Application.ReferenceStyle = xlA1
End If
'
' Choose Color Serie 1 and Highest = Red
' as the Defaults
'
UserForm1.OptionButton1.Value = True
UserForm1.OptionButton3.Value = True
UserForm1.RefEdit1 = ""
UserForm1.RefEdit1.SetFocus
UserForm1.Show
End Sub
==========================================

Option Explicit
Private Sub CommandButton1_Click()
'
'
' Gys de Jongh Feb-2006
' This Routine Constructs a HeatMap
' on the Selected Range in Excel
'
' This is an extension of the Routine
' RangeColors
'
' The value of the cells determines
' their color via two possible CodeTables
' The user can choose to make the highest
' value Red or Blue
'
Dim I As Integer
Dim N As Integer
Dim MyColorCode As Range
Dim NR As Integer
Dim NC As Integer
Dim R As Integer
Dim C As Integer
Dim D As Integer
Dim Z As Single
Dim Min As Single
Dim Max As Single
'
' The CodeTabels contain the color indexes
' for incremental values
'
Dim CodeTabel(12) As Integer
Dim CodeTabel1(12) As Integer
Dim CodeTabel2(12) As Integer
'
CodeTabel1(1) = 11
CodeTabel1(2) = 5
CodeTabel1(3) = 41
CodeTabel1(4) = 37
CodeTabel1(5) = 8
CodeTabel1(6) = 34
CodeTabel1(7) = 19
CodeTabel1(8) = 27
CodeTabel1(9) = 44
CodeTabel1(10) = 45
CodeTabel1(11) = 46
CodeTabel1(12) = 3
'
CodeTabel2(1) = 11
CodeTabel2(2) = 5
CodeTabel2(3) = 41
CodeTabel2(4) = 37
CodeTabel2(5) = 34
CodeTabel2(6) = 2
CodeTabel2(7) = 19
CodeTabel2(8) = 27
CodeTabel2(9) = 44
CodeTabel2(10) = 45
CodeTabel2(11) = 46
CodeTabel2(12) = 3
'
For I = 1 To 12
CodeTabel(I) = CodeTabel1(I)
Next I
If OptionButton2.Value Then
For I = 1 To 12
CodeTabel(I) = CodeTabel2(I)
Next I
End If
On Error Resume Next
Set MyColorCode = Range(RefEdit1)
NR = MyColorCode.Rows.Count
NC = MyColorCode.Columns.Count
'
Z = MyColorCode.Cells(1, 1)
Min = Z
Max = Z
'
For R = 1 To NR
For C = 1 To NC
Z = MyColorCode.Cells(R, C)
If Z Max Then
Max = Z
End If
If Z < Min Then
Min = Z
End If
Next C
Next R
'
For R = 1 To NR
For C = 1 To NC
Z = MyColorCode.Cells(R, C)
N = Int((Z - Min) / (Max - Min) * 11 + 1.5)
D = N
If OptionButton4 Then
D = 13 - N
End If
MyColorCode.Cells(R, C).Interior.ColorIndex = CodeTabel(D)
Next C
Next R
'
'
End Sub

Private Sub CommandButton2_Click()
If Change Then
Application.ReferenceStyle = xlR1C1
End If
UserForm1.Hide
End Sub

=================================================

If you need the sheet mail me directly

hth
Gys

--

Dave Peterson