Search code examples
excelexcel-2007vba

Excel VBA Loop function for all cells

Hi i have function to extract from polish personal identity number to birth date. But function only works in current cell . How modify code to execute all cells from A2 - end of worksheet.

Worksheet

B2 - cell code

=IF(VALUE(MID(A2;3;2))>20;"20"&LEWY(A2;2);"19"&LEWY(A2;2))&MOD(MID(A2;3;2);20)&MID(A2;5;2)
Solution

  • I think you are trying to copy the formula from B2 all the way down to the cells in column A that have an input. I think this should do what you are looking for. I am assuming it is on Sheet 1, if it is not you can alter the code.

    'Defines the parameter to count the rows being used in column A
    lRow = Sheet1.Range("A2").End(xlDown).Row

'Creates the formula for column B
Sheet1.Range("B2") = "=IF(VALUE(MID(A2;3;2))>20;""20""&LEWY(A2;2);""19""&LEWY(A2;2))&MOD(MID(A2;3;2);20)&MID(A2;5;2)"

'Copies and paste the formulas down to the last rows for column B in Sheet 1
    Sheet1.Range("B2").Copy
    Sheet1.Range("B3:B" & lRow).PasteSpecial Paste:=xlPasteFormulas