VBA with EXCEL

[dgwilliams]

Is it possible to set up a varible in VBA thats contents is the contents of a cell range in an excel spreadsheet. Then can i display the contents of this variable in another cell of the spreadsheet.

If so can someone give me the syntax needed to declare a variable in VBA and to assign a cell range as its contents. Also can someone give me the syntax to print the contents of the variable.

If anymore information is needed and not made my self clear just ask

thanks in advance

 

[SQLBI]

I've no idea what type your variable is or what your sheets are named, but if you change the code below to fit your workbook, it should work ok.

Dim Var1 AS Variant

Sheet1.Activate

Var1 = ActiveSheet.Cells(row, cloumn).Value

Sheet2.Activate

ActiveSheet.Cells(row, cloumn).Value = Var1


Hope this helps.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[SQLBI]

To print the contents of the variable to the "Immediate" window in the VBE, use;

Debug.Print Var1

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[dgwilliams]

Thanks thats great i want to print the contents of the variable to a cell and make it a hyperlink is that possible if so how?

 

[SQLBI]

This is a sample .Add method of the Hyperlink Object.
If you use my code above to assign the value and then amend the code below accordingly it should work.


With ActiveWorkSheet
.Hyperlinks.Add Anchor:=.Range("a5&quot, _
Address:="

http://example.microsoft.com",

_
ScreenTip:="Microsoft Web Site", _
TextToDisplay:="Microsoft"
End With


Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[SQLBI]

With ActiveSheet... Not With ActiveWorkSheet



Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk