optimization question

[figment]

I have to blocks of code that both work, but there has got to be a faster or more elegant way of doing the same thing, now I know if its not broke don?t fix it, but its bothering me, for I use this code almost daily and any speed boost would help a lot.

so if you feel like helping I look forward to your response

the first chunk is:

If ((.Range("C" & a) = TEST Or .Range("D" & a) = TEST Or _
.Range("F" & a) = TEST Or .Range("G" & a) = TEST Or .Range("H" & a) = TEST Or _
.Range("I" & a) = TEST Or .Range("J" & a) = TEST Or .Range("K" & a) = TEST Or _
.Range("L" & a) = TEST Or .Range("M" & a) = TEST Or .Range("N" & a) = TEST Or _
.Range("O" & a) = TEST Or .Range("P" & a) = TEST Or _
.Range("C" & a) = TEST2 Or .Range("D" & a) = TEST2 Or .Range("F" & a) = TEST2 Or _
.Range("G" & a) = TEST2 Or .Range("H" & a) = TEST2 Or .Range("I" & a) = TEST2 Or _
.Range("J" & a) = TEST2 Or .Range("K" & a) = TEST2 Or .Range("L" & a) = TEST2 Or _
.Range("M" & a) = TEST2 Or .Range("N" & a) = TEST2 Or .Range("O" & a) = TEST2 Or _
.Range("P" & a) = TEST2) And (.Range("B" & a) <> "")) Then

TEST and TEST2 with be one of the following:
TEST=""; Test2=""
TEST="N/A"; TEST2=""
TEST="N/A";TEST2="N/A"

the second chunk of code:

Sub copying(a, b)
c = "A"
d = "report"
Worksheets(d).Range("B" & b) = Worksheets(c).Range("B" & a)
Worksheets(d).Range("C" & b) = Worksheets(c).Range("C" & a)
Worksheets(d).Range("D" & b) = Worksheets(c).Range("D" & a)
Worksheets(d).Range("E" & b) = Worksheets(c).Range("E" & a)
Worksheets(d).Range("F" & b) = Worksheets(c).Range("F" & a)
Worksheets(d).Range("G" & b) = Worksheets(c).Range("G" & a)
Worksheets(d).Range("H" & b) = Worksheets(c).Range("H" & a)
Worksheets(d).Range("I" & b) = Worksheets(c).Range("I" & a)
Worksheets(d).Range("J" & b) = Worksheets(c).Range("J" & a)
Worksheets(d).Range("K" & b) = Worksheets(c).Range("K" & a)
Worksheets(d).Range("L" & b) = Worksheets(c).Range("L" & a)
Worksheets(d).Range("M" & b) = Worksheets(c).Range("M" & a)
Worksheets(d).Range("N" & b) = Worksheets(c).Range("N" & a)
Worksheets(d).Range("O" & b) = Worksheets(c).Range("O" & a)
Worksheets(d).Range("P" & b) = Worksheets(c).Range("P" & a)
End Sub

I realize I should put a least add a with statement, but I suspect that this can be don in one to two lines, if only I knew the right calls.

Again thanks for you time.

[mikerickson]

For part 2

Worksheets(d).Range("B"&p).Resize(14,1)= Worksheets(c).Range("B"&p).Resize(14,1)

[figment]

For part 2

Worksheets(d).Range("B"&p).Resize(14,1)= Worksheets(c).Range("B"&p).Resize(14,1)

thanks for the input.

i tried your code as well as:

Worksheets(d).Range("B" & b & ":P" & b) = Worksheets(c).Range("B" & a & ":P" & a)

but both returned nothing. just lots and lots of empty cells

[Bob Phillips]

If .Range("C" & a) = Test Or .Range("D" & a) = Test Or _
(Not IsError(Application.Match(Test, .Range("F" & a).Resize(, 10), 0))) Or _
.Range("C" & a) = Test2 Or .Range("D" & a) = Test2 Or _
(Not IsError(Application.Match(Test2, .Range("F" & a).Resize(, 10), 0))) And _
(.Range("B" & a) <> "") Then

[figment]

Xld, vary interesting idea, it took me a bit to wrap my brain around what you were doing, but alas it didn't work, I would say the problem is with the inputs I am using. For it returned True for all check when given a TEST = "" and it returned False for all check when TEST = "N/A". But thanks for the idea; I will have to remember this for future projects.

To help along anybody else who would like to take a crack at this I am attaching an example workbook of the workbook in which I use this code.

7336

o yes and i have forgotten to mention that i am running office 2003

[Bob Phillips]

Fow about this alternative then



If (.Range("C" & a) = TEST Or .Range("D" & a) = TEST Or _
Application.CountIf(.Range("F" & a).Resize(, 10), TEST) > 0 Or _
.Range("C" & a) = TEST2 Or .Range("D" & a) = TEST2 Or _
Application.CountIf(.Range("F" & a).Resize(, 10), TEST2) > 0) And _
.Range("B" & a) <> "" Then

[figment]

Now that worked perfectly. I always forget about using sheet functions within code. Thanks