How to compare a cell value to an offset cell value?

Hi,

I'm trying to update/add some data in row 18, but if the selected cell
to update is in a col. where the current week no. (A35) doesn't match
the week no. in the cell 12 rows above then the update should be
disallowed since the update is being done more than once, which I
don't want done.

I can figure out the flow I need, but not how to do the comparison.

Here's my thoughts, but how do I get the comparison to work?

Sub Update()
Range("C18").Select
Selection.End(xlToRight).Select
Selection.Copy
Selection.Offset(0, 1).Select

If Range(A35).Value = Selection.Offset(-12, 0).Value Then

ActiveSheet.Paste
Application.CutCopyMode = False
Else
Msg = "Only the current week may be updated!"
Exit Sub
End If
End Sub

Thanks for your time!

Norm

Hello Norm,

I have annotated your code to see what you are doing. It would help to know what and how the data in rows are arranged. Please look this over and repost what your code with some more details about your data. Perhaps this post will help you discover what you need to change.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

This seems to comply with what you say

Dim rng As Range
Set rng = Range("C18").End(xlToRight)
rng.Copy

If Range(A35).Value = rng.Offset(-12, 1).Value Then
ActiveSheet.Paste
Application.CutCopyMode = False
Else
Msg = "Only the current week may be updated!"
Exit Sub
End If

but apart from being tidier, it is the same as yours.

What doesn't work in yours?

--
HTH

Bob Phillips

"Father Guido" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I'm trying to update/add some data in row 18, but if the selected cell
> to update is in a col. where the current week no. (A35) doesn't match
> the week no. in the cell 12 rows above then the update should be
> disallowed since the update is being done more than once, which I
> don't want done.
>
> I can figure out the flow I need, but not how to do the comparison.
>
> Here's my thoughts, but how do I get the comparison to work?
>
> Sub Update()
> Range("C18").Select
> Selection.End(xlToRight).Select
> Selection.Copy
> Selection.Offset(0, 1).Select
>
> If Range(A35).Value = Selection.Offset(-12, 0).Value Then
>
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Else
> Msg = "Only the current week may be updated!"
> Exit Sub
> End If
> End Sub
>
> Thanks for your time!
>
> Norm
>

On Thu, 19 May 2005 10:14:48 +0100, "Bob Phillips"
<[email protected]> wrote:

>This seems to comply with what you say
>
>Dim rng As Range
> Set rng = Range("C18").End(xlToRight)
> rng.Copy
>
> If Range(A35).Value = rng.Offset(-12, 1).Value Then
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Else
> Msg = "Only the current week may be updated!"
> Exit Sub
> End If
>
>but apart from being tidier, it is the same as yours.
>
>What doesn't work in yours?
________________________________________________________
ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
Hi Bob,

Thanks for replying. I cut and pasted your code into my macro and got
the same error. When it runs the following line
___________________________________________
If Range(A35).Value = rng.Offset(-12, 1).Value Then
ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
I get the following error message
_________________________________
Run-time error '1004'
Method 'Range' of Object '_Global' failed
ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
I'm running XL 2002 SP-1 on Windows 2000 SP-3 if that helps at all.

My complete macro now looks like this.
______________________________________________
Sub Update()
Dim rng As Range
Set rng = Range("C18").End(xlToRight)
rng.Copy

If Range(A35).Value = rng.Offset(-12, 1).Value Then
ActiveSheet.Paste
Application.CutCopyMode = False
Else
Msg = "Only the current week may be updated!"
Exit Sub
End If
End Sub
ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
If you have any more ideas I'd love to hear them.

Thanks again!

Norm

Unless A35 is a variable (very doubtful), I think that this should be:

If Range("A35").Value = rng.Offset(-12, 1).Value Then



Father Guido wrote:
>
> On Thu, 19 May 2005 10:14:48 +0100, "Bob Phillips"
> <[email protected]> wrote:
>
> >This seems to comply with what you say
> >
> >Dim rng As Range
> > Set rng = Range("C18").End(xlToRight)
> > rng.Copy
> >
> > If Range(A35).Value = rng.Offset(-12, 1).Value Then
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> > Else
> > Msg = "Only the current week may be updated!"
> > Exit Sub
> > End If
> >
> >but apart from being tidier, it is the same as yours.
> >
> >What doesn't work in yours?
> ________________________________________________________
> ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> Hi Bob,
>
> Thanks for replying. I cut and pasted your code into my macro and got
> the same error. When it runs the following line
> ___________________________________________
> If Range(A35).Value = rng.Offset(-12, 1).Value Then
> ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> I get the following error message
> _________________________________
> Run-time error '1004'
> Method 'Range' of Object '_Global' failed
> ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> I'm running XL 2002 SP-1 on Windows 2000 SP-3 if that helps at all.
>
> My complete macro now looks like this.
> ______________________________________________
> Sub Update()
> Dim rng As Range
> Set rng = Range("C18").End(xlToRight)
> rng.Copy
>
> If Range(A35).Value = rng.Offset(-12, 1).Value Then
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Else
> Msg = "Only the current week may be updated!"
> Exit Sub
> End If
> End Sub
> ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> If you have any more ideas I'd love to hear them.
>
> Thanks again!
>
> Norm

--

Dave Peterson

On Thu, 19 May 2005 12:53:15 -0500, Dave Peterson
<[email protected]> wrote:

>Unless A35 is a variable (very doubtful), I think that this should be:
>
>If Range("A35").Value = rng.Offset(-12, 1).Value Then
>

Good eye ump -- perfect solution. Thanks Dave!!!

Norm

my eyes are going ........................

--
HTH

Bob Phillips

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Unless A35 is a variable (very doubtful), I think that this should be:
>
> If Range("A35").Value = rng.Offset(-12, 1).Value Then
>
>
>
> Father Guido wrote:
> >
> > On Thu, 19 May 2005 10:14:48 +0100, "Bob Phillips"
> > <[email protected]> wrote:
> >
> > >This seems to comply with what you say
> > >
> > >Dim rng As Range
> > > Set rng = Range("C18").End(xlToRight)
> > > rng.Copy
> > >
> > > If Range(A35).Value = rng.Offset(-12, 1).Value Then
> > > ActiveSheet.Paste
> > > Application.CutCopyMode = False
> > > Else
> > > Msg = "Only the current week may be updated!"
> > > Exit Sub
> > > End If
> > >
> > >but apart from being tidier, it is the same as yours.
> > >
> > >What doesn't work in yours?
> > ________________________________________________________
> > ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> > Hi Bob,
> >
> > Thanks for replying. I cut and pasted your code into my macro and got
> > the same error. When it runs the following line
> > ___________________________________________
> > If Range(A35).Value = rng.Offset(-12, 1).Value Then
> > ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> > I get the following error message
> > _________________________________
> > Run-time error '1004'
> > Method 'Range' of Object '_Global' failed
> > ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> > I'm running XL 2002 SP-1 on Windows 2000 SP-3 if that helps at all.
> >
> > My complete macro now looks like this.
> > ______________________________________________
> > Sub Update()
> > Dim rng As Range
> > Set rng = Range("C18").End(xlToRight)
> > rng.Copy
> >
> > If Range(A35).Value = rng.Offset(-12, 1).Value Then
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> > Else
> > Msg = "Only the current week may be updated!"
> > Exit Sub
> > End If
> > End Sub
> > ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
> > If you have any more ideas I'd love to hear them.
> >
> > Thanks again!
> >
> > Norm
>
> --
>
> Dave Peterson