VBA frustration

[Guest]

Can someone tell me why this doesn't work? Thanks!

Sub Test()
a = Worksheets("Sheet1").Range("A7")

b = Cells.Find("Total", , xlValues).Address

Worksheets("Sheet1").Range(a, b).Select
End Sub

 

[Frank Kabel]

Hi
what are you trying to do?
- a contains the vALUE from cell A7 (whatever is in this cell
- b contains the address of the found cell

 

[Guest]

Frank,
Once again you saved the day. I think you answered my question. I need to
use the Set keyword. I'm trying to select the area between A1 and where the
value of the found cell is equal to "Total". So, if I say:

Set a=Worksheets("Sheet1").Range("A1")

that should make it work, right?

 

[Myrna Larson]

If you know the address of the starting cell, you can specify it literally,
i.e.

Sub Test()
b = Cells.Find("Total", , xlValues).Address
Worksheets("Sheet1").Range("A1", b).Select
End Sub

or, since the arguments to Range can be range *objects* as well as string
addresses,

Sub Test()
Set b = Cells.Find("Total", , xlValues)
With Worksheets("Sheet1")
.Range(Cells(1), b).Select
End With
End Sub

In the 2nd example, you could also use "A1" instead of Cells(1)

 

[Frank Kabel]

Hi
yes it should