Going from the Excel INDEX command to proper VBA code.

remirenaud

New Member
Joined
Oct 28, 2014
Messages
2
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)))
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

chrism216

Board Regular
Joined
Aug 6, 2013
Messages
211
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

New Member
Joined
Oct 28, 2014
Messages
2
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)

:confused:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,517
Messages
5,602,127
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top