In order to create dynamic variable – array need to be used. Through dynamic variable I understand variable what get numeric value for example to define row number.
Source code for my code has been found in www.mrexcel.com.
Original code from www.mrexcel.com:
' Source: https://www.mrexcel.com/forum/excel-questions/524440-dynamic-variable-name-vba.html Dim i, x Dim Pos(1 To 5) As Integer Pos(1) = 10 Pos(2) = 19 Pos(3) = 25 Pos(4) = 36 Pos(5) = 42 For i = 1 To 5 x = Cells(Pos(i), 1).value Next i
Unfortunately when I was writing appliction with dynamic variable I didn’t describe code in proper way. It is example why code should contain a lot of description – otherwise you will spend a lot of hours to undestand what you’ve done in the past. Therefor I will show simplify code for two reasons:
– I couldn’t understand what I’ve done in the past because of lack of description 🙁
– there is some confidentiality clause – so I don’t show original data and proccess done by code.
Sheet data for dynamic variable test (should be pasted to A1 cell):
Code assumptions to use dynamic variable (it could be done in Pivot Table – but I couldn’t use it):
– whole source list has to be searched
– city name, budget value and cost saving value have to be added to new raport
– report has to be consisted of:
* 1st column: city name
* 2nd column: budget value
*3rd column: cost saving value
Code to build report with dynamic variable hidden in array:
Sub ChangingVariable() ' Report has to built where city, budget and cost saving will be shown Dim RowP(1 To 2, 1) As Variant Dim i, k, ArrayCounter As Long i = 2 ArrayCounter = 1 With ThisWorkbook.ActiveSheet Erase RowP ' array cleaning ' Main loop searching cities Do Until .Cells(i, 1) = "" If .Cells(i, 1) = "A." Or _ .Cells(i, 1) = "B." Then ' Cities are marked by capital letters RowP(ArrayCounter, 0) = .Cells(i, 1).Row ' add city row number to array ' Second array to search cost saving in city k = i Do Until .Cells(k, 1) = "" If .Cells(k, 2) = "Cost Saving" Then ' Cost saving has been found - adding row number to array RowP(ArrayCounter, 1) = .Cells(k, 2).Row i = k ArrayCounter = ArrayCounter + 1 Exit Do Else ' Nothing happened End If k = k + 1 Loop Else ' Nothing happened End If i = i + 1 Loop ' Building report - adding headers .Cells(1, 5) = "City" .Cells(1, 6) = "Budget" .Cells(1, 7) = "Cost Saving" ' Dynamic variable use example to find row number k = 2 For i = 1 To UBound(RowP) .Cells(k, 5) = .Cells(RowP(i, 0), 2) 'adding city .Cells(k, 6) = .Cells(RowP(i, 0), 3) 'adding budget .Cells(k, 7) = .Cells(RowP(i, 1), 3) 'adding cost saving k = k + 1 Next i End With End Sub
Dynamic variable use code part:
' Dynamic variable use example to find row number k = 2 For i = 1 To UBound(RowP) .Cells(k, 5) = .Cells(RowP(i, 0), 2) 'adding city .Cells(k, 6) = .Cells(RowP(i, 0), 3) 'adding budget .Cells(k, 7) = .Cells(RowP(i, 1), 3) 'adding cost saving k = k + 1 Next i