My Sub xxx(c1,c2,c3 etc) only lets me use 60 variables) . I there a way to use more ? I need 100.

[zill2]

My Sub is working on an Spreadsheet of 5000 rows and 100 columns using the results of each row when analysing the results of the combined columns. Each columns of an individual row is define on a separate spreadsheet and the combined result is used as part of an AI calculation.

Consequently, I don't know what you mean by "value for this field"

All I know is my C1,C2,C3 etc in the function macro above is made up of number and letters.

[#2]

@zill2 

It seems like you are working with Excel VBA (Visual Basic for Applications) and you have a subroutine (Sub) that takes multiple parameters (C1, C2, C3, etc.), but you're limited to 60 variables and you need to work with 100 columns of data.

Here are a few suggestions to address this issue:

1. Restructure your Code: Instead of passing each column as a separate parameter, you can pass an array or a range that contains all the data you need. This way, you can pass a single parameter to your Sub, and then access the individual columns within the array or range. This will help you avoid the limitation of 60 variables.
2. Use a Class or Custom Object: If your columns represent related data, you could create a custom class or object that encapsulates the data for each column. Then, you can create an array or collection of these objects and pass that as a parameter to your Sub.
3. Use Global or Module-Level Variables: If your Sub is part of a larger module or workbook, you can declare variables at the module level or even as global variables. This would allow you to access those variables from within your Sub without needing to pass them as parameters.
4. Dynamic Data Loading: If loading all the columns at once is not feasible due to memory constraints, you could load the necessary columns dynamically within your Sub as needed, processing a subset of columns at a time.
5. Refactor the Spreadsheet Structure: Depending on the nature of your data and analysis, consider whether you can consolidate some columns or perform some preprocessing before passing the data to the Sub.
6. Split the Analysis: If possible, split the analysis into smaller, more manageable chunks. For example, if you're performing the same analysis on different sections of your data, you could split the data into smaller blocks and process them one at a time.
7. Use External Data Sources: If your data is stored externally (like in a database), you might consider retrieving only the columns you need for a specific analysis rather than trying to load everything into Excel.

It is important to note that the best solution depends on the specifics of your data, the nature of your analysis, and your programming skills. The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

Thank you! I will look at using arrays.