Going from the Excel INDEX command to proper VBA code.

[remirenaud]

I need help going from the Excel INDEX command to proper VBA code. I have up to 90 Excel files used for surveying clinic participants. Since each survey will have the questions in a different order, the VBA code needs vertically index to the proper question in the respondents’ files. The clinic may have up to 10 competitive products, but only a maximum of 4 are allowed to be evaluated per question (so this could be a different 4 for each question) so a horizontal index is also required to index to the proper ratings in the row, of which there are three different ratings per product: Likes, Dislikes and a Numeric rating. Each response is then concatenated with the respondents name in the upper left of the file and all are concatenated together into a single cell so that the team can evaluate all the responses in a single cell of text (except the numerics, which are averaged) This is repeated for up to 300 questions (rows). I have a version working where the indexing is performed in Excel using INDEX, but it requires two sheets as Excel has a limit of 8192 characters in a cell formula, and it includes files, even if they are not used. I would prefer to select the files to be used and then place the resulting text only into the cells.
Below is the excel formula I use, reduced down to only 3 files (vs. 90) to save space - In this example it looks for the question # in $B7, and calls the product selected of the 10 (Cell P7) and is looking for the “Dislikes” (thus the P7 & ”D”).


= [C01.xlsx]C!$C$2&"@ "&(INDEX([C01.xlsx]C!$A$6:$AL$600,MATCH($B7,[C01.xlsx]C!$A$6:$A$600,0),MATCH(P7&"D",[C01.xlsx]C!$A$6:$AL$6,0)))&":
"& [C02.xlsx]C!$C$2&"@ "&(INDEX([C02.xlsx]C!$A$6:$AL$600,MATCH($B7,[C02.xlsx]C!$A$6:$A$600,0),MATCH(P7&"D",[C02.xlsx]C!$A$6:$AL$6,0)))&":
"& [C03.xlsx]C!$C$2&"@ "&(INDEX([C03.xlsx]C!$A$6:$AL$600,MATCH($B7,[C03.xlsx]C!$A$6:$A$600,0),MATCH(P7&"D",[C03.xlsx]C!$A$6:$AL$6,0)))

 

[chrism216]

I need help going from the Excel INDEX command to proper VBA code. I have up to 90 Excel files used for surveying clinic participants. Since each survey will have the questions in a different order, the VBA code needs vertically index to the proper question in the respondents’ files. The clinic may have up to 10 competitive products, but only a maximum of 4 are allowed to be evaluated per question (so this could be a different 4 for each question) so a horizontal index is also required to index to the proper ratings in the row, of which there are three different ratings per product: Likes, Dislikes and a Numeric rating. Each response is then concatenated with the respondents name in the upper left of the file and all are concatenated together into a single cell so that the team can evaluate all the responses in a single cell of text (except the numerics, which are averaged) This is repeated for up to 300 questions (rows). I have a version working where the indexing is performed in Excel using INDEX, but it requires two sheets as Excel has a limit of 8192 characters in a cell formula, and it includes files, even if they are not used. I would prefer to select the files to be used and then place the resulting text only into the cells.
Below is the excel formula I use, reduced down to only 3 files (vs. 90) to save space - In this example it looks for the question # in $B7, and calls the product selected of the 10 (Cell P7) and is looking for the “Dislikes” (thus the P7 & ”D”).


= [C01.xlsx]C!$C$2&"@ "&(INDEX([C01.xlsx]C!$A$6:$AL$600,MATCH($B7,[C01.xlsx]C!$A$6:$A$600,0),MATCH(P7&"D",[C01.xlsx]C!$A$6:$AL$6,0)))&":
"& [C02.xlsx]C!$C$2&"@ "&(INDEX([C02.xlsx]C!$A$6:$AL$600,MATCH($B7,[C02.xlsx]C!$A$6:$A$600,0),MATCH(P7&"D",[C02.xlsx]C!$A$6:$AL$6,0)))&":
"& [C03.xlsx]C!$C$2&"@ "&(INDEX([C03.xlsx]C!$A$6:$AL$600,MATCH($B7,[C03.xlsx]C!$A$6:$A$600,0),MATCH(P7&"D",[C03.xlsx]C!$A$6:$AL$6,0)))

Hi.

Although i don't precisely understand what you are trying to achieve, it seems to me that what you need is a loop to go through all of the workbooks in a file directory (which you specify). The solution would require the following:

1. Placing all of your files in the same directory.
2. Designing a mechanism to specify which files to fetch information from. A userform with "alternating buttons" seems like the easiest way to set that up, even though it would be a lot of work to set up buttons for 90 files.
3. Creating a procedure for each workbook: open workbook, extract string (create it if necessary), paste into a "master" or "summary" workbook, close workbook.
4. Loop through the desired workbooks (see 2.)

is this more or less what you are looking for?

as for coding itself, it would require that you know a bit about userforms and vba in general. its hard to help you from this end, since you would have to design the userforms, and i dont know how the file directory looks from your side.

hope it helps

 

[remirenaud]

Hi.

Although i don't precisely understand what you are trying to achieve, it seems to me that what you need is a loop to go through all of the workbooks in a file directory (which you specify). The solution would require the following:

1. Placing all of your files in the same directory.
2. Designing a mechanism to specify which files to fetch information from. A userform with "alternating buttons" seems like the easiest way to set that up, even though it would be a lot of work to set up buttons for 90 files.
3. Creating a procedure for each workbook: open workbook, extract string (create it if necessary), paste into a "master" or "summary" workbook, close workbook.
4. Loop through the desired workbooks (see 2.)

is this more or less what you are looking for?

as for coding itself, it would require that you know a bit about userforms and vba in general. its hard to help you from this end, since you would have to design the userforms, and i dont know how the file directory looks from your side.

hope it helps

I don't think i need userforms, just help with converting INDEX to vMatch or something similar- i have never used vmatch and it looks challenging to understand, but if I had this example worked out I could use it for other things.

I already have a skeleton (below) but I need to also be able to concatenate the data across on a row four four vehicles, first all likes (90 responses in each cell, four cells) then dislikes in four cells and then the average scores in four cells, repeat for all questions (rows)


Sub Load_Survey_data_2()

If MsgBox("THIS WILL REPLACE ALL DATA IN THE RATING, LIKE AND DISLIKE COLUMNS. Do you want to continue?", vbYesNo) = vbYes Then
MsgBox "Yes pressed"


GetBook_Orig = ActiveWorkbook.Name
Path_Orig = Application.ActiveWorkbook.Path


Dim filenames As Variant


'Turn off error display
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename(, , , , True)


counter = 1


' ubound determines how many items in the array
While counter <= UBound(filenames)


'Opens the selected files
Workbooks.Open filenames(counter)


'increment counter
counter = counter + 1


Wend

' stuff here that concatenates by indexing throught the files
' then pastes into the GetBook_Orig file


'close all workbooks but the active one
Dim WB As Workbook
For Each WB In Workbooks
If Not (WB Is ActiveWorkbook) Then WB.Close
Next

'Reset screen display
Application.AskToUpdateLinks = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

Else
MsgBox "no pressed"
End If

End Sub