PDA

View Full Version : Trying to write code in a shorter way



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

Bob Phillips
11-09-2010, 08:52 AM
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

With Cells(k, 1)

If Not IsError(Application.Match(.Value2, Cells(2, 11).Resize(9), 0)) Then
Cells(k, 11).Value2 = Cells(19, 8).Value2
Else
Cells(k, 11) = Application.WorksheetFunction.SumIf(ws4.Range("C15:C10000"), .Value2, ws4.Range("AO15:AO10000"))
End If
End With
Next k

ioncila
11-09-2010, 09:13 AM
As always, your help is great.
Thank you very much

Ioncila

mdmackillop
11-09-2010, 01:49 PM
Please remember to mark your threads Solved.