Help Please, Worksheet Change

[ilvmgicker]

Sorry for cross post. This group seems to have more activity. Anyway....



Forget the values I have used in the simple example, they are there just to
show you what I need to work.

Using the code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G1")) Is Nothing Then

If Range("G1").Value = Range("B3").Value Then
Range("A3").Value = Range("A1").Value
Range("C3").Value = Range("C1").Value
Range("E3:F3").Value = Range("E1:F3").Value

End If
End If
End Sub

If I enter ton in G1 the values in the cells change.


Before Worksheet Change

A B C D E F G
1 gun pig cat pen too hit
2
3 ton

I enter ton in cell G1

After Worksheet Change

A B C D E F G
1 gun pig cat pen too hit ton
2
3 gun ton cat too hit


With me? This works fine for a small project, but I have many values to
check.
Suppose Range A1:G35 (change If Not Intersect(Target, Range("G1:G35")) Is
Nothing Then) holds all the data I need to 'copy' if there is a match and
Range A71:F105 is where those 'copied' values are placed.
One problem is the code gets very long, repetitive and finally the code gets
too big for EXcel and it won't run.

Is there anyway I can shorten the code? Got an idea it has something to do
with variables but I am new to VBA and am getting really lost.

Thanks in advance





At present I need to check 18 cell values against 23 cell values, this will
increase shortly.

[Bob Phillips]

Does it all get copied in a block, or in parts as in the original example?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ilvmgicker" <[email protected]> wrote in message
news:[email protected]...
> Sorry for cross post. This group seems to have more activity. Anyway....
>
>
>
> Forget the values I have used in the simple example, they are there just
to
> show you what I need to work.
>
> Using the code...
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target, Range("G1")) Is Nothing Then
>
> If Range("G1").Value = Range("B3").Value Then
> Range("A3").Value = Range("A1").Value
> Range("C3").Value = Range("C1").Value
> Range("E3:F3").Value = Range("E1:F3").Value
>
> End If
> End If
> End Sub
>
> If I enter ton in G1 the values in the cells change.
>
>
> Before Worksheet Change
>
> A B C D E F G
> 1 gun pig cat pen too hit
> 2
> 3 ton
>
> I enter ton in cell G1
>
> After Worksheet Change
>
> A B C D E F G
> 1 gun pig cat pen too hit ton
> 2
> 3 gun ton cat too hit
>
>
> With me? This works fine for a small project, but I have many values to
> check.
> Suppose Range A1:G35 (change If Not Intersect(Target, Range("G1:G35")) Is
> Nothing Then) holds all the data I need to 'copy' if there is a match and
> Range A71:F105 is where those 'copied' values are placed.
> One problem is the code gets very long, repetitive and finally the code
gets
> too big for EXcel and it won't run.
>
> Is there anyway I can shorten the code? Got an idea it has something to do
> with variables but I am new to VBA and am getting really lost.
>
> Thanks in advance
>
>
>
>
>
> At present I need to check 18 cell values against 23 cell values, this
will
> increase shortly.
>
>
>
>