How can I set the cell reference of a variable

[groovygarden]

Hi

I haven't used VBA much, but have managed to muddle together a small macro. However, I'm stuck on one thing and I'm not having much luck googling it, as not really sure what words to search for...

My macro starts:

Private Sub Worksheet_Change(ByVal Target As range)

Now, I need to create a new variable, SiteName, which will refer to a cell on my worksheet. The row of SiteName must be the same as Target, whilst the column will always be A. EG: if Target is E4, then SiteName must be A4, if Target is G6, then SiteName must be A6 and so on.

How do I declare and set the cell address of SiteName?

If I then need to use the value of the cell SiteName refers to, can I just do something like:

IF SiteName = "Building" THEN...

?

Many thanks


PS: I've tried various versions of this:

Dim SiteName As Range
   
   Range(SiteName).Row = Range(Target).Row

 

[PHV]

A starting point:
Dim SiteName As Range
Set SiteName = ActiveSheet.Cells(Target.Row, 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rofeu]

Hi,

something like this:

Dim SiteName as Range
set SiteName = Range(Target.Row,1)

If SiteName.Value = "Building" Then

End If

Set SiteName = Nothing

Cheers,

Roel

 

[groovygarden]

Yay! Thank you both for the quick replies! Have spent ages today trying to work this out!

Much appreciated!

 

[groovygarden]

I'm stuck again!

I've created 2 variables, ChangeStart and ChangeEnd, and have set their cell addresses.

Dim ChangeStart As range
Dim ChangeEnd As range
Set ChangeStart = ActiveSheet.Cells(Target.Row, 5)
Set ChangeEnd = ActiveSheet.Cells(Target.Row, 10)

I need to take the range of cells running from ChangeStart to ChangeEnd and write the word "yes" in all those which aren't blank.

Dim Full As range
   For Each Full In range(ChangeStart.cells:ChangeEnd.cells)
   If Not IsEmpty(Full) Then Full = "yes"
   Next Full

This code doesn't work because of the range(ChangeStart.cells:ChangeEnd.cells) bit. If I write, say, range("e3:k3") it works fine... What am I doing wrong?

 

[SkipVought]

range(ChangeStart, ChangeEnd)

Skip,

_[red][/red]

 

[groovygarden]

Perfect! Thanks so much. Can leave the office happy now!