3

I am trying to make a string array of keywords. The string array will have a set number of columns (various number of keywords for 3 different fruits: apple, banana, orange). However, the column for each row will be a different size based on how many keywords the user is looking for, for EACH fruit. The user may be looking for 1 keyword for apple, 2 keywords for banana, and 3 keywords for orange. when it's all said and done, I want a string array of 3xC, where C is the largest number of keywords, and the unfilled cells are empty. Here is the code I have so far, but I am unsure of how to dimensionalize the array. 

Public strTemp(0 To 2, 0 To 100) As Variant
Sub GetKeyWords()

Dim numKeyA As Integer
Dim numKeyB As Integer
Dim numKeyO As Integer



'Ask user for integer value of keywords looking for
numKeyA = InputBox("How many keywords would you like to search for the A? (Integer)", "A Integer Value Please")

'Ask user for the specific keywords
For k = 1 To numKeyA

    'Save keywords into strTemp array
    strTemp(0, k - 1) = InputBox("Please enter keyword" & k)

Next

numKeyB = InputBox("How many keywords would you like to search for the B? (Integer)", "B Integer Value Please")

For a = 1 To numKeyB

    'Save keywords into strTemp array
    strTemp(1, a - 1) = InputBox("Please enter keyword" & a)

Next

numKeyC = InputBox("How many keywords would you like to search for the C? (Integer)", "C Integer Value Please")

For b = 1 To numKey777

    'Save keywords into strTemp array
    strTemp(2, b - 1) = InputBox("Please enter keyword" & b)



    maxColumn = WorksheetFunction.Max(numKeyA, numKeyB, numKeyC)
    ReDim strTemp(2, maxColumn)



Next


End Sub

The way the code is right now, I get an error when trying to redim the array at the end to size it based on the largest column. If I don't size the code at the beggining, I get an error "subscript out of range" for strTemp(0, k-1) = InputBox("please enter keyword" & k).

Improve this question

2 Answers 2

Reset to default
1 
Public strTemp() As Variant

Leave out the indices in the declaration. Then you do need to ReDim before you try to put anything in strTemp. If you do all three InputBoxes first, you'll be able to size strTemp and then read in the keywords.

Alternatively, use

ReDim strTemp(2, numKeyA)

after the first InputBox, then say

ReDim Preserve strTemp(2, IIf(numKeyA>numKeyB, numKeyA, numKeyB))

after the second InputBox (the IIf is so you don't shrink the array). Preserve will keep values that are already there. Similarly, after the third InputBox, 

ReDim Preserve strTemp(2, maxColumn)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you! This almost works! When I put all the same amount of keywords for each fruit (3), the first two keywords for the apple disappear so my array looks like this: ['', '', c; a, b, c; a, b, c], do you know why this is? I was wondering what the IIf(numKeyA > numKeyB, numKeyA, numKeyB) did exactly, maybe it has something to do this with line?
@A.Cod Not sure. (1) The IIf() is the same as max(numKeyA,numKeyB). (2) In the line For b = 1 To numKey777, should that be 1 To numKeyC? (3) If you haven't already, add Option Explicit and Option Base 0 to the top of your module and compile to see if that flags any errors. (4) The Redim statements should be outside the For loops, and I just noticed in your original code that was not the case. The order is InputBox for bounds, Redim, For loop for values, repeated three times.
it's because the ReDim was inside the loop! Thanks a ton. Just one thing that needs to be debugged. When I go from 1 keywords for Apple, to 3 keywords for Banana, i get a subscript out of range error after typing in the 3rd keyword for banana. the error occurs on the line: strTemp(1, a-1) = InputBox("please enter keyword" & a)
@A.Cod Not sure off-hand. Please edit your question to include your code as it stands now, after the code you already have (so people reading this later understand the original context). I'll take a look at it tomorrow when I get back to my computer, or maybe someone else will have an idea. In the meantime, stick breakpoints on the Redim statements and use the Watch window to see how the dimensions of strTemp change each time. That should give you some clues. Good luck!
Thanks for helping. I ended up getting rid of the IIf(numKeyA>numKeyB, numKeyA, numKeyB)) and just found the max of that and resizing based on MaxColumn and it worked.
|
1

Try this: Refer comments for details.

   Sub test()

    Dim lCtrCol_New     As Long
    Dim lCtrRow_New     As Long
    Dim lInst           As Long
    Dim arrNew()



    '/ Start New Array
    ReDim arrNew(1 To 3, 1 To 1)

    '/ Add Values
    arrNew(1, 1) = "Apple"
    arrNew(2, 1) = "Orange"
    arrNew(3, 1) = "Tomato"

    '/ Loop and assign unique values
    For lCtrRow_New = LBound(arrNew) To UBound(arrNew)
      lInst = Val(InputBox("No. of Keywords for " & arrNew(lCtrRow_New, 1), ""))
                If lInst + 1 > UBound(arrNew, 2) Then
                    ReDim Preserve arrNew(1 To 3, 1 To lInst + 1)
                End If

                For lCtrCol_New = LBound(arrNew, 2) To lInst
                    arrNew(lCtrRow_New, lCtrCol_New + 1) = InputBox("Please enter keyword #" & lCtrCol_New & " for " & arrNew(lCtrRow_New, 1))
                Next

       Next
End Sub
Improve this answer

3 Comments

unfortunately this doesn't work the way i want it to. When I run this code it asks for number of keywords for apple so i put 1, then it asks for keywords for orange so i put 1 then it asks keyword for tomato and i put 1, then it never asks for the keywords, it just stops running.
edited the answer. never tested for #1 keyword. Try now.
awesome! found a few bugs however. If you say you want 3 keywords for apple, it asks you for 3 keywords for the 2 other fruits even though you put different numbers for the other two (eg, 2 keywords for banana, 1 for tomato)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.