Offset and Formula 1

[PCX]

Greetings,

I use the macro 4.0 language and am just learning VBA. There is a function in the macro language that I use a lot and am having difficulty using in VBA.

This is what I would use in the macro language:

=FORMULA("TextFromActiveSheet",OFFSET("DefinedNameonAnotherSheet",0))

I seem to get confused when trying to paste things from the activesheet to another sheet without actually activating that sheet.

Thanks

 

[SkipVought]

Hi,
Here's how the Copy VBA might work...

Worksheets("SourceSheet").Range("SourceCell").Copy _
        Destination:=Worksheets("TargetSheet").Range("TargetCell")

Now for Offset...
I might have a cell with a Named Range called "TargetCell". If I wanted to reference each cell adjacent to "TargetCell", then I could do something like this...

Sub CellsAround()
    Dim i, j
    With Range("TargetCell")
        For i = -1 To 1
            For j = -1 To 1
                If i = 0 And j = 0 Then
                
                Else
                    .Offset(i, j).Interior.ColorIndex = 3
                End If
            Next
        Next
    End With
End Sub

Hope this hepls :-0 Skip,
metzgsk@voughtaircraft.com