VBA - Dynamic Variable Name in a Loop

0 votes

I have a macro that has a variable with a fixed name, but the final character is changed: i.e. (BatchTotal1, BatchTotal2, BatchTotal3,...,etc.).

The above BatchTotal values are each defined in a different macro, so I made them publicly defined first.

My attempt to use them in a loop was unsuccessful because BatchTotal(i) always returns 0. Please review my code:

Public BatchTotal(1 To 5) As Double
Sub priority_calculation()

'Some Code

For i = 1 To 5
  If BatchTotal(i) > 0 Then           
    Cells(k, 2).PasteSpecial Paste:=xlPasteValues
    Cells(k, 3) = C1
    Cells(k, 8) = Q1
  End If
Next i

End Sub
Mar 19, 2023 in Others by narikkadan
• 63,720 points
1,518 views

1 answer to this question.

0 votes

Set the array values with:

Dim i as Long
For i = 1 To 5
    BatchTotal(i) = UserForm1.Controls("BatchTotal" & i)
Next
answered Mar 19, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel VBA: Open Hyperlinks in a loop and copy paste download link in a sheet

Refer this tutorial for your solution: https://evermap.com/Tutorial_AB ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,720 points
1,153 views
0 votes
1 answer

VBA Export as PDF and Save to Location with name as per a Cell in the worksheet

Following is the code that gets generated ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,720 points
1,672 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,720 points
720 views
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,720 points
1,994 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,004 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,370 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
678 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
889 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,408 views
0 votes
1 answer

Excel VBA creating a new Outlook appointment results in a cancelled appointment

Because an inappropriate sender will be used, ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
674 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP