View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Alan Beban[_2_] Alan Beban[_2_] is offline
external usenet poster
 
Posts: 783
Default Defining cells and ranges using variables

After Set rng=Range("A1")
rng(9,39) refers to AM9 and
Range(rng(9,39),rng(9,40)) refers to AM9:AN9

Target.Address is a string; the other side of the statement is a range.

Alan Beban

RogerPB wrote:
On Wed, 16 Mar 2005 11:08:47 -0800, Alan Beban
wrote:



One way to avoid the problems with the dots is to use

Set rng=Worksheets("Sheet3").Range("A1")

Then Range(rng(25,25),rng(27,27)) will refer to Y25:AA27 on Sheet3



In fact, I am running the macro whilst in sheet 3......

Anyway, I modified my routine as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim mc As Integer

'If Target.Address = "$I$39" Then.... was the orignal line
'and was replaced by the lines proposed by Alan above.

Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then


'Worksheets("Sheet3").Range(Cells(1, 1), Cells(9, 9))
mc = Target.Value
Set rng = Worksheets("Sheet3").Range(Cells(25, 25), Cells(27, 27))
For Each cell In rng
rng.Interior.ColorIndex = mc
cell.Value = Target.Value
'Exit For
'End If
Next
End If

End Sub



However, replacing the line
'If Target.Address = "$I$39" Then...
by
Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then

led to the error message "Method 'Range' of object '-Worksheet'
failed.

So I am still looking for an expression identifying I39 by two
variables representing the row and the column




Rogerpb