Thursday, June 3, 2010

Insert n rows after each row in EXCEL

image

image

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 2
For r = r To 1 Step -1
Worksheets(4).Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

split a word with

Have you ever needed to split a word with certain character, yes I do, Mostly with _ or -

here you go, an excel macro

Function split1(text) 

split1 = Split(text, "_")(0)

End Function

Function split2(text)

split1 = Split(text, "_")(1)

End Function

Wednesday, June 2, 2010

INDEX and MATCH in EXCEL

image

So, I needed to replace a number and its related value. So easy, the formula is

=INDEX($I$2:$I$7,MATCH(B2,$H$2:$H$7,0))