{"id":551,"date":"2019-02-21T21:34:38","date_gmt":"2019-02-21T21:34:38","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=551"},"modified":"2019-02-21T21:38:39","modified_gmt":"2019-02-21T21:38:39","slug":"how-to-repeat-tasks-with-your-vba-code","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/02\/21\/how-to-repeat-tasks-with-your-vba-code\/","title":{"rendered":"How To Repeat Tasks With Your VBA Code"},"content":{"rendered":"

This post is actually an answer to a question on a youtube video<\/p>\n

“I am facing one problem during working with excel, how do I solve below….. Serial number in row:1,2,3,4………… Required no of repit :2,1,3,2………… Answer will be : 1,1,2,3,3,3,4,4…….. How do I solve this problem??? Where one and another dejit will repit continuously.. Please help to find a way to solve this problem. Thank you.\ufeff”<\/p><\/blockquote>\n

Here is the setup:<\/p>\n

\"\"<\/a><\/p>\n

For this we are going to use a for loop to loop the cells and another for loop to insert the results.<\/strong><\/p>\n

Paste this code into a module, and we’ll call it from a button on the worksheet.<\/p>\n

Sub Button1_Click()\r\n    Dim intValue As Integer\r\n    Dim intRowsToEvaluate As Integer\r\n        \r\n    Dim intRepeat As Integer\r\n    \r\n    Dim intResultRow As Integer\r\n    \r\n    intRowsToEvaluate = 20\r\n    \r\n    Dim intRow As Integer\r\n    \r\n    For intRow = 2 To intRowsToEvaluate\r\n        'get the value and the number of times to repeat the value.\r\n        intValue = Range(\"A\" & intRow)\r\n        intRepeat = Range(\"B\" & intRow)\r\n        \r\n        'get the last result row used\r\n        lngLastRow = FindLastRow(\"C\")\r\n        lngLastRow = lngLastRow + 1\r\n        \r\n        'enter the results\r\n        For intResultRow = 1 To intRepeat\r\n            Range(\"C\" & lngLastRow) = intValue\r\n            lngLastRow = lngLastRow + 1\r\n        Next\r\n            \r\n    Next\r\n    \r\nEnd Sub\r\n\r\nFunction FindLastRow(WhichColumn As String) As Long\r\n\r\n    Dim lngLastRow As Long\r\n    \r\n    'move to the last row on the worksheet and find the last used cell.\r\n    \r\n    With ActiveSheet\r\n        lngLastRow = ActiveSheet.Cells(1048576, WhichColumn).End(xlUp).Row\r\n    End With\r\n\r\n    FindLastRow = lngLastRow\r\n\r\nEnd Function\r\n<\/pre>\n
\"\"<\/a><\/p>\n
****************************************************<\/p>\n\n\n
\n
<\/div>\n