figment
11-19-2007, 12:48 PM
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.
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.