Cell Referencing

[Tuff]

When writing a macro is it possible to use the find function to set your cell reference.

IE. To find the cells that need to be selected, I need to use the find function, as the cell position I need changes from G23 to G35 to G189 etc etc everytime.

Once I find the cell I simply need to select from that cell to the top of the spreadsheet.

Is this possible?

 

[andrew299]

If you know what column your cell is in use

ActiveCell.Select
cc = ActiveCell.Address
Range("g1", cc).Select

andrew299 It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[Tuff]

Thanks Andrew,

That worked a treat. I came up with a similar idea, but I didn't know how to put that in VB code.

Much appreciated.

 

[SkipVought]

Hi,

Most of the time when working in code, I find that it is much easier to use all numeric references rather than alpha-numeric.

Go is your data were in G1:G25, it would be...

    col = Range("G:G").Column
    Set rng = Range(Cells(1, col), Cells(25, col))

Then I can access all the cells in that range...

dim r as range
for each r in rng
  with r
    ThrValue = .Value
    TheRow = .Row
    TheInteriorColorindex = .Interior.Colorindex
'... and on and on... 
  end with
next

Hope this helsp
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[pyromaniac]

This Code might help

ActiveCell.Select
cc = ActiveCell.Address
Range("g1", cc).Select