ioncila
11-09-2010, 08:44 AM
Hi
I have the following code in a small macro.
I need help, if possible, to write it in a shorter and more functional way, specially the bold part.
Many thanks in advance.
...
Dim rng1 As Range, rng2 As Range
Dim i As Integer, k As Integer
Set rng1 = Range("I2:J10")
Set rng2 = Range("27:106")
For k = 27 To 106
If Cells(k, 9).Value > 0 Then
Cells(k, 10).Value = Application.WorksheetFunction.VLookup(Cells(k, 9), rng1, 2, False)
End If
If Cells(2, 11) = Cells(k, 1) Or Cells(3, 11) = Cells(k, 1) Or Cells(4, 11) = Cells(k, 1) _
Or Cells(5, 11) = Cells(k, 1) Or Cells(6, 11) = Cells(k, 1) Or Cells(7, 11) = Cells(k, 1) _
Or Cells(8, 11) = Cells(k, 1) Or Cells(9, 11) = Cells(k, 1) Or Cells(10, 11) = Cells(k, 1) Then
Cells(k, 11) = Cells(19, 8)
Else
Cells(k, 11) = Application.WorksheetFunction.SumIf(WS4.Range("C15:C10000"), Cells(k, 1), WS4.Range("AO15:AO10000"))
End If
Next k
...
Ioncila
I have the following code in a small macro.
I need help, if possible, to write it in a shorter and more functional way, specially the bold part.
Many thanks in advance.
...
Dim rng1 As Range, rng2 As Range
Dim i As Integer, k As Integer
Set rng1 = Range("I2:J10")
Set rng2 = Range("27:106")
For k = 27 To 106
If Cells(k, 9).Value > 0 Then
Cells(k, 10).Value = Application.WorksheetFunction.VLookup(Cells(k, 9), rng1, 2, False)
End If
If Cells(2, 11) = Cells(k, 1) Or Cells(3, 11) = Cells(k, 1) Or Cells(4, 11) = Cells(k, 1) _
Or Cells(5, 11) = Cells(k, 1) Or Cells(6, 11) = Cells(k, 1) Or Cells(7, 11) = Cells(k, 1) _
Or Cells(8, 11) = Cells(k, 1) Or Cells(9, 11) = Cells(k, 1) Or Cells(10, 11) = Cells(k, 1) Then
Cells(k, 11) = Cells(19, 8)
Else
Cells(k, 11) = Application.WorksheetFunction.SumIf(WS4.Range("C15:C10000"), Cells(k, 1), WS4.Range("AO15:AO10000"))
End If
Next k
...
Ioncila