Refer to Worksheet Ranges in Code

[Andibevan]

I have been reading the following item:-

http://msdn.microsoft.com/library/d.../wrtskhowtosearchfortextinworksheetranges.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

 

[Tom Ogilvy]

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

 

[Andibevan]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

I think Bob meant

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value

 

[Andibevan]

Bob,

Sorry about that, I actually meant:-

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vakeyDirectCast.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]

Thanks for the catch Tim.

Bob

I think Bob meant

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)

Msgbox ws.Range("A1").Value


--
Regards,
Tom Ogilvy




http://msdn.microsoft.com/library/d.../wrtskhowtosearchfortextinworksheetranges.asp

 

[Tom Ogilvy]

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.

 

[Andibevan]

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