Code for meet cond, copy, pasteSpecial, ValuesOnly

[Guest]

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 Ogilvy]

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

 

[Guest]

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.