Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to Simplyfied and make the code works.

  Asked By: Chloe    Date: Sep 21    Category: MS Office    Views: 815
  

I need a help in simplifying and make it work for below code :



Range("B89:B105").Select

Selection.Copy

Range("D114").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

:=False, Transpose:=False



If Range("k125") = 1 Then



Range("B89:B105").Select

Selection.Copy

Range("D132").Select

Selection.Insert Shift:=xlToRight

Application.CutCopyMode = False



Range("C89:C105").Select

Application.CutCopyMode = False

Selection.Copy

Range("D114").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

:=False, Transpose:=False



If Range("k125") = 1 Then



Range("C89:C105").Select

Selection.Copy

Range("D132").Select

Selection.Insert Shift:=xlToRight

Application.CutCopyMode = False





The step is I need to copy from column to column range starting from
"B89:B105" Up to 65 column

The range than will be copy to range "D114" ,( where here the range will
be compared with a range of my choice, a definite range at "C114",then I
have a formula that if range "K125" is equal to "1" then copy the range
that meet this criteria to "D132"



Can somebody help me, I try to record it but somehow it fails

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Robin Hayes     Answered On: Sep 21

OK... from what I can tell,
it looks like you're wanting to
check a cell (K125) and see if it has
a value of (1) then, you're copying
17 rows of data from 65 columns (columns "B" thru "??").

(BTW, your "If (Range("K125") = 1) then" doesn't have
and End If)

what I don't understand is what you're doing
with it.
You're first pasting the VALUEs in D114-D130, but never
doing anything with it!

Then you're inserting the original formulas
into D132-D148 and shifting the columns to the right.

So, what you end up with, is cells D132-BD148
having the exact same formulas as cells B89-BA105.

Well, if you can figure that out,
I can help  you with the loop...
Provided you know what you're doing, I would use:

Since you said 65 columns, starting  with "B":
'-----------------------------------------------------
For I = 2 to 66
Range(cells(89,I),Cells(105,I)).Select
Selection.Copy
Range("D114").Select
Selection.PasteSpecial Paste:=xlPasteValues,_
Operation:=xlNone,Skipblanks:=False,_
Transpose:=false
if (Range("K125") = 1) then
Range(Cells(89,I),Cells(105,I).Select
Selection.Copy
Range("D132").Select
Selection.Insert Shift:=xlToRight
end if
Next I
'----------------------------------------------------
Now, the problem with this is, I have no idea
what you mean by:
"Where here the range  will be compared with a range
of my choice" ?? how are you going to compare a range?

"a definite range at "C114"" ??

From what I can tell, you have 65 columns of data in 17 rows.
"somehow" you have a list that identifies which rows to copy.
but I can't tell how.

Hopefully, this loop example will give you an idea
on how to automate it...

If you can help me understand what you need, maybe I can
be more helpful.

 
Answer #2    Answered By: Ibtihaj Akhtar     Answered On: Sep 21

Your code  already do the work  , while what I meant by definite range  is
actually where I copy  just a single range one by one from
Range(cells(89,I),Cells(105,I) to C114 ,then make  a comparison with D114
, from here anything that meet the criteria  in K125 will be copy to D132



So copying the range to D114 , and if meet the criteria in K125 is done
,



is there a way also that a definite range can be automated too ?



It should work like : every range (89,i)to(105,i) will be compared to
all range before starting  with another.

 
Didn't find what you were looking for? Find more on How to Simplyfied and make the code works. Or get search suggestion and latest updates.




Tagged: