This posts shows two things: Its shows how to find a value in a sheet without looping, and instead using the functions .Find and .CountIf, and how to create and add items to a dynamic array. <\/p>\n
The problem I faced was that I had some answers to questions in a worksheet, and some of those answers had to be added as answers to questions in another worksheet. But the users in the original worksheet had in some cases added new questions and answers (rows and columns) to the questions, so I couldn’t just copy and paste it without chekcing the content and making sure the answers were posted along side the correct questions. <\/p>\n
So, the code below first defines the questions I want to find, then finds the questions in the worksheet and saves the answers to a dynamic array. The next step is of course to add the answers to the other workwheet, and to transfer the questions and answers added by the user, but it’s not part of the example. <\/p>\n
\r\n\r\nDim Questions As Variant\r\nDim x As Integer\r\nDim myrow As Integer\r\nDim myColumn As Integer\r\nDim myValue\r\n\r\nDim Answers() As String 'Array of answers\r\nDim lngPosition As Long 'Counting\r\nblDimensioned = False\r\n\r\n'Array of questions\r\nQuestions = Array("Navn", "Ansat i fleksjob - Dato?", "Ansat den", "Bevillingsdato", "Evt. oph\u00f8rsdato", "Kommune", "Tilskudsberettiget l\u00f8nindplacering i SLS ")\r\n\r\n'Go through the array of questions\r\n'We want to find each of them in the worksheet\r\n\r\nFor x = LBound(Questions) To UBound(Questions)\r\n\r\n\r\nDim rowFound, columnFound\r\n\r\n'If the question is not found in the worksheet, then just skip to the next item in the array\r\nIf WorksheetFunction.CountIf(Cells, Questions(x)) = 0 Then\r\nGoTo NotFound\r\nEnd If\r\n\r\n\r\n'Find the x item in the array\r\nCells.Find(What:=(Questions(x)), After:=ActiveCell, LookIn:=xlValues, LookAt:= _\r\n xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _\r\n , SearchFormat:=False).Activate\r\n\r\n'Found: Get the row and the column number of the answe to the question (the next column after the question\r\nmyrow = ActiveCell.row\r\nmyColumn = ActiveCell.Column + 1\r\nmyValue = Cells(myrow, myColumn)\r\n\r\n'We have the answer.\r\n'Add the answer to the the Answer array.\r\n\r\nIf myValue = "" Then myValue = " "\r\n \r\n 'The array is dimensioned in the first loop\r\n If blDimensioned = True Then\r\n \r\n 'The array is extended, so we extend the array\r\n ReDim Preserve Answers(0 To UBound(Answers) + 1) As String\r\n \r\n Else\r\n \r\n 'The array is not dimensined, so we dimension it and flag it as dimensioned.\r\n ReDim Answers(0 To 0) As String\r\n blDimensioned = True\r\n \r\n End If\r\n \r\n 'Add the answer to the last element of the array\r\n Answers(UBound(Answers)) = myValue\r\n\r\n\r\n\r\nNotFound:\r\n\r\nNext x\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"This posts shows two things: Its shows how to find a value in a sheet without looping, and instead using the functions .Find and .CountIf, and how to create and add items to a dynamic array. The problem I faced was that I had some answers to questions in a worksheet, and some of those […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,5],"tags":[],"_links":{"self":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/81"}],"collection":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=81"}],"version-history":[{"count":10,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions"}],"predecessor-version":[{"id":90,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions\/90"}],"wp:attachment":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=81"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=81"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=81"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}