Refer to Worksheet Ranges in Code
 

I have been reading the following item:-

http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet ranges from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

That is VB.Net or C# information, not VBA information. It won't work in
Excel itself.

In VBA it would be something like:


Dim ws as Worksheet
Dim rng as Range, rng1 as Range, rng2 as Range

This sample code is searching one range on one sheet for values contained in
another range on another sheet (search rng2 for values in rng1)

Sub testme()

Dim FoundCell As Range
Dim myRng As Range
Dim whatToFind As String
Dim wks As Worksheet
Dim wks2 As Worksheet
Dim rng1 As Range, cell As Range, rng2 As Range
Dim fAddr As String
Dim sStr As String

Set wks2 = Worksheets("Sheet2")
Set rng2 = wks2.Range(wks2.Cells(1, "C"), wks2.Cells(1,
"C").End(xlDown)(2))
Set wks = Worksheets("sheet1")
Set rng1 = wks.Range(wks.Cells(1, 1), wks.Cells(1, 1).End(xlDown))
For Each cell In rng1
sStr = ""
fAddr = ""
whatToFind = cell.Value

Set FoundCell = rng2.Cells.Find(what:=whatToFind, _
after:=rng2(rng2.Count), LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)


If Not FoundCell Is Nothing Then
fAddr = FoundCell.Address
Do
sStr = sStr & FoundCell.Offset(0, -2).Value & ";"
Set FoundCell = rng2.FindNext(FoundCell)
Loop While Not FoundCell.Address = fAddr
cell.Offset(0, 6).Value = Left(sStr, Len(sStr) - 1)
End If
Next cell

End Sub



--
Regards,
Tom Ogilvy


"Andibevan" wrote in message
...
I have been reading the following item:-


http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet ranges
from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

Thanks Tom,

I did think that I was looking at the wrong thing, but as it looks so
similar to VB it was hard to spot. Thanks also for your example code, it
looks helpful.

Ta

Andi

"Tom Ogilvy" wrote in message
...
That is VB.Net or C# information, not VBA information. It won't work in
Excel itself.

In VBA it would be something like:


Dim ws as Worksheet
Dim rng as Range, rng1 as Range, rng2 as Range

This sample code is searching one range on one sheet for values contained
in
another range on another sheet (search rng2 for values in rng1)

Sub testme()

Dim FoundCell As Range
Dim myRng As Range
Dim whatToFind As String
Dim wks As Worksheet
Dim wks2 As Worksheet
Dim rng1 As Range, cell As Range, rng2 As Range
Dim fAddr As String
Dim sStr As String

Set wks2 = Worksheets("Sheet2")
Set rng2 = wks2.Range(wks2.Cells(1, "C"), wks2.Cells(1,
"C").End(xlDown)(2))
Set wks = Worksheets("sheet1")
Set rng1 = wks.Range(wks.Cells(1, 1), wks.Cells(1, 1).End(xlDown))
For Each cell In rng1
sStr = ""
fAddr = ""
whatToFind = cell.Value

Set FoundCell = rng2.Cells.Find(what:=whatToFind, _
after:=rng2(rng2.Count), LookIn:=xlValues, lookat:=xlPart,
_
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)


If Not FoundCell Is Nothing Then
fAddr = FoundCell.Address
Do
sStr = sStr & FoundCell.Offset(0, -2).Value & ";"
Set FoundCell = rng2.FindNext(FoundCell)
Loop While Not FoundCell.Address = fAddr
cell.Offset(0, 6).Value = Left(sStr, Len(sStr) - 1)
End If
Next cell

End Sub



--
Regards,
Tom Ogilvy


"Andibevan" wrote in
message
...
I have been reading the following item:-



http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet ranges
from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

Andi,

I have looked at teh article, and I cannot see anything remotely like what
you have posted.

To your question.

Dim ws As Worksheet

ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
I have been reading the following item:-


http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet ranges
from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

I think Bob meant

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
Andi,

I have looked at teh article, and I cannot see anything remotely like what
you have posted.

To your question.

Dim ws As Worksheet

ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in
message
...
I have been reading the following item:-



http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet ranges
from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

Bob,

Sorry about that, I actually meant:-

http://msdn.microsoft.com/library/de...DirectCast.asp

Tom has now informed me that I was looking at stuff to do with VB.net -
finding out something as simple as what program language the document I was
reading related too seemed to be very tricky!

Thanks

Andy

"Bob Phillips" wrote in message
...
Andi,

I have looked at teh article, and I cannot see anything remotely like what
you have posted.

To your question.

Dim ws As Worksheet

ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in
message
...
I have been reading the following item:-



http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet ranges
from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

Thanks for the catch Tim.

Bob


"Tom Ogilvy" wrote in message
...
I think Bob meant

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
Andi,

I have looked at teh article, and I cannot see anything remotely like
what
you have posted.

To your question.

Dim ws As Worksheet

ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in
message
...
I have been reading the following item:-




http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the
VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in
or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet
ranges
from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

At the top of the article:

MSDN Home MSDN Library Development Tools and Languages Visual
Studio .NET Visual Basic and Visual C# Reference Visual Basic
Language Visual Basic Language and Run-Time Reference Keywords A-E
Keywords

See the "Visual Studio .Net"

When microsoft talks about VB they now generally mean VB.Net.

--
Regards,
Tom Ogilvy

"Andibevan" wrote in message
...
Bob,

Sorry about that, I actually meant:-


http://msdn.microsoft.com/library/de...DirectCast.asp

Tom has now informed me that I was looking at stuff to do with VB.net -
finding out something as simple as what program language the document I
was
reading related too seemed to be very tricky!

Thanks

Andy

"Bob Phillips" wrote in message
...
Andi,

I have looked at teh article, and I cannot see anything remotely like
what
you have posted.

To your question.

Dim ws As Worksheet

ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in
message
...
I have been reading the following item:-




http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the
VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an add-in
or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet
ranges
from
code but am getting stuck.

Thanks

Andi

Refer to Worksheet Ranges in Code
 

Thanks Tom - as always, I am very grateful for your assistance.


"Tom Ogilvy" wrote in message
...
At the top of the article:

MSDN Home MSDN Library Development Tools and Languages Visual
Studio .NET Visual Basic and Visual C# Reference Visual Basic
Language Visual Basic Language and Run-Time Reference Keywords
A-E
Keywords

See the "Visual Studio .Net"

When microsoft talks about VB they now generally mean VB.Net.

--
Regards,
Tom Ogilvy

"Andibevan" wrote in
message
...
Bob,

Sorry about that, I actually meant:-



http://msdn.microsoft.com/library/de...DirectCast.asp

Tom has now informed me that I was looking at stuff to do with VB.net -
finding out something as simple as what program language the document I
was
reading related too seemed to be very tricky!

Thanks

Andy

"Bob Phillips" wrote in message
...
Andi,

I have looked at teh article, and I cannot see anything remotely like
what
you have posted.

To your question.

Dim ws As Worksheet

ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in
message
...
I have been reading the following item:-





http://msdn.microsoft.com/library/de...heetranges.asp

and it uses the following code:-

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

but when I try and use it, the first two lines show up in red in the
VBA
editor.

Is there a reason for this? I am using Excel 2000, is there an
add-in
or
something I need to use the "DirectCast"?

Basically I am after a few pointers on how to refer to worksheet
ranges
from
code but am getting stuck.

Thanks

Andi