Code for meet cond, copy, pasteSpecial, ValuesOnly

I have had some great help from Bernie Deitrick on starting out with a code
that should let me do the below mentioned activity.
I am trying to learn more about macro and codes and have started a project
given to me by my manager ( I will try to get this done for 2006 ).
I have altered the code somewhat, since I decided to change some of the
sheet a little.
But this is what I need the macro to do once it is assigned to a click -
button "send"


Copy row 488 from column D through column AT
Check the value in A1 (has a number ranging from 1 to 365)
Then Find the value from A1
In rows C493 through C857 ( each row in column C contains a number between
1 and 365)
and PasteSpecial ValuesOnly
in the row matching A1 from column D through column AT
this needs to copy blanks as blanks too.
Then clear contets of A151 through A300

The original gave me a run error 91 and highlighted this part of the code:
Worksheets("INPUT").Range("D488:AT488").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
Resize(41, 1).PasteSpecial xlPasteValues

I am running '97

This is the (altered) code

Sub send2()
Dim myFind As Integer

myFind = Worksheets("INPUT").Range("$A$1").Value
Worksheets("INPUT").Range("$C$493:$C$857").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
Resize(41, 1).Copy
Worksheets("INPUT").Range("D488:AT488").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
Resize(41, 1).PasteSpecial xlPasteValues
Worksheets("INPUT").Range("$A$151:$A$300").ClearContents
End Sub

btw... what does the "Resize(41,1)" mean in this code?
and why does the number -4163 show up when I place my cursor over xlValues
where is xlValues getting this number from?

I appreciate any help and/or explainations.
Thank You all.

You said you wanted to copy row 488 to one of the rows in 493:857, but your
code is written to copy from one of the rows in 493:857 to row 488. For
your problem statement:

Sub send2()
Dim myFind As Integer
Dim rng as Range
myFind = Worksheets("INPUT").Range("A1").Value
set rng = Worksheets("INPUT").Range( _
"C493:C857").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)

if not rng is nothing then

Worksheets("INPUT").Range("D488:AT488").copy
rng.Offset(0,1).PasteSpecial xlValues

Worksheets("INPUT").Range("A151:A300").ClearContents
Else
msgbox myFind & " was not found"
end If
End Sub

if you want to copy to 488
Sub send2()
Dim myFind As Integer
Dim rng as Range
myFind = Worksheets("INPUT").Range("$A$1").Value
set rng = Worksheets("INPUT").Range( _
"C493:C857").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)

if not rng is nothing then

rng.offset(0,1).Resize(1,43).copy
Worksheets("INPUT").Range("D488:AT488").PasteSpecial xlValues

Worksheets("INPUT").Range("A151:A300").ClearContents
Else
msgbox myFind & " was not found"
end If
End Sub

---------------
You code is offseting one row instead of one column. It is then resizing 41
rows instead of 43 columns

Range("A1").Resize(1,43) expands the range to 43 columns (and 1 row) as
demo't from the immediate window:

? Range("D1").Resize(1,43).Address
$D$1:$AT$1

xlValues is a constant - but it represents the value -4163. Again, from the
immediate window:

? xlValues
-4163

You could use the -4163 directly, but is it more readable to use xlValues.

--
Regards,
Tom Ogilvy






"ufo_pilot" <[email protected]> wrote in message
news:[email protected]...
> I have had some great help from Bernie Deitrick on starting out with a
code
> that should let me do the below mentioned activity.
> I am trying to learn more about macro and codes and have started a project
> given to me by my manager ( I will try to get this done for 2006 ).
> I have altered the code somewhat, since I decided to change some of the
> sheet a little.
> But this is what I need the macro to do once it is assigned to a click -
> button "send"
>
>
> Copy row 488 from column D through column AT
> Check the value in A1 (has a number ranging from 1 to 365)
> Then Find the value from A1
> In rows C493 through C857 ( each row in column C contains a number
between
> 1 and 365)
> and PasteSpecial ValuesOnly
> in the row matching A1 from column D through column AT
> this needs to copy blanks as blanks too.
> Then clear contets of A151 through A300
>
> The original gave me a run error 91 and highlighted this part of the
code:
> Worksheets("INPUT").Range("D488:AT488").Find(myFind, _
> LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> Resize(41, 1).PasteSpecial xlPasteValues
>
> I am running '97
>
> This is the (altered) code
>
> Sub send2()
> Dim myFind As Integer
>
> myFind = Worksheets("INPUT").Range("$A$1").Value
> Worksheets("INPUT").Range("$C$493:$C$857").Find(myFind, _
> LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> Resize(41, 1).Copy
> Worksheets("INPUT").Range("D488:AT488").Find(myFind, _
> LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> Resize(41, 1).PasteSpecial xlPasteValues
> Worksheets("INPUT").Range("$A$151:$A$300").ClearContents
> End Sub
>
> btw... what does the "Resize(41,1)" mean in this code?
> and why does the number -4163 show up when I place my cursor over xlValues
> where is xlValues getting this number from?
>
> I appreciate any help and/or explainations.
> Thank You all.
>

Tom,
You are my Santa for the season!
Your code did EXACTLY what I needed it to do
Now I can study the code (with the help of your explaination)
and maybe create others step by step.
This project has awakened a spark in me I did not know I had.
Thank you sooooo much.


"Tom Ogilvy" wrote:

> You said you wanted to copy row 488 to one of the rows in 493:857, but your
> code is written to copy from one of the rows in 493:857 to row 488. For
> your problem statement:
>
> Sub send2()
> Dim myFind As Integer
> Dim rng as Range
> myFind = Worksheets("INPUT").Range("A1").Value
> set rng = Worksheets("INPUT").Range( _
> "C493:C857").Find(myFind, _
> LookIn:=xlValues, LookAt:=xlWhole)
>
> if not rng is nothing then
>
> Worksheets("INPUT").Range("D488:AT488").copy
> rng.Offset(0,1).PasteSpecial xlValues
>
> Worksheets("INPUT").Range("A151:A300").ClearContents
> Else
> msgbox myFind & " was not found"
> end If
> End Sub
>
> if you want to copy to 488
> Sub send2()
> Dim myFind As Integer
> Dim rng as Range
> myFind = Worksheets("INPUT").Range("$A$1").Value
> set rng = Worksheets("INPUT").Range( _
> "C493:C857").Find(myFind, _
> LookIn:=xlValues, LookAt:=xlWhole)
>
> if not rng is nothing then
>
> rng.offset(0,1).Resize(1,43).copy
> Worksheets("INPUT").Range("D488:AT488").PasteSpecial xlValues
>
> Worksheets("INPUT").Range("A151:A300").ClearContents
> Else
> msgbox myFind & " was not found"
> end If
> End Sub
>
> ---------------
> You code is offseting one row instead of one column. It is then resizing 41
> rows instead of 43 columns
>
> Range("A1").Resize(1,43) expands the range to 43 columns (and 1 row) as
> demo't from the immediate window:
>
> ? Range("D1").Resize(1,43).Address
> $D$1:$AT$1
>
> xlValues is a constant - but it represents the value -4163. Again, from the
> immediate window:
>
> ? xlValues
> -4163
>
> You could use the -4163 directly, but is it more readable to use xlValues.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
>
>
> "ufo_pilot" <[email protected]> wrote in message
> news:[email protected]...
> > I have had some great help from Bernie Deitrick on starting out with a
> code
> > that should let me do the below mentioned activity.
> > I am trying to learn more about macro and codes and have started a project
> > given to me by my manager ( I will try to get this done for 2006 ).
> > I have altered the code somewhat, since I decided to change some of the
> > sheet a little.
> > But this is what I need the macro to do once it is assigned to a click -
> > button "send"
> >
> >
> > Copy row 488 from column D through column AT
> > Check the value in A1 (has a number ranging from 1 to 365)
> > Then Find the value from A1
> > In rows C493 through C857 ( each row in column C contains a number
> between
> > 1 and 365)
> > and PasteSpecial ValuesOnly
> > in the row matching A1 from column D through column AT
> > this needs to copy blanks as blanks too.
> > Then clear contets of A151 through A300
> >
> > The original gave me a run error 91 and highlighted this part of the
> code:
> > Worksheets("INPUT").Range("D488:AT488").Find(myFind, _
> > LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> > Resize(41, 1).PasteSpecial xlPasteValues
> >
> > I am running '97
> >
> > This is the (altered) code
> >
> > Sub send2()
> > Dim myFind As Integer
> >
> > myFind = Worksheets("INPUT").Range("$A$1").Value
> > Worksheets("INPUT").Range("$C$493:$C$857").Find(myFind, _
> > LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> > Resize(41, 1).Copy
> > Worksheets("INPUT").Range("D488:AT488").Find(myFind, _
> > LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> > Resize(41, 1).PasteSpecial xlPasteValues
> > Worksheets("INPUT").Range("$A$151:$A$300").ClearContents
> > End Sub
> >
> > btw... what does the "Resize(41,1)" mean in this code?
> > and why does the number -4163 show up when I place my cursor over xlValues
> > where is xlValues getting this number from?
> >
> > I appreciate any help and/or explainations.
> > Thank You all.
> >
>
>
>