Dynamic variable

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

ID Item Cash [$]
A. Poznań 1000
2 Profit 80
1 Cost Saving 120
3 Expenses 800
B. Wrocław 900
2 Profit 60
3 Expenses 720
1 Cost Saving 120

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

76 thoughts on “Dynamic variable

  1. Pingback: where to purchase social signals

  2. Pingback: 슈어맨시즌2

  3. Pingback: buy/order Tramadol 50mg 100mg 200mg online pharmacy no script cheap for pain anxiety in USA UK Canada Australia overseas overnight delivery

  4. Pingback: w88club

  5. Pingback: order generic vyvanse (lisdexamfetamine) capsle online no script legally

  6. Pingback: lunesta canada usa uk australia

  7. Pingback: ativan online pharmacy for sale

  8. Pingback: số đề người chết

  9. Pingback: best online pharmacy to buy prescription meds overnight delivery

  10. Pingback: danh de online uy tin

  11. Pingback: The Natural Penguin

  12. Pingback: eatverts

  13. Pingback: бытовая техника кривой рог

  14. Pingback: buy/order hydrocodone online pharmacy legally cheap no script for pain, anxiety from USA UK Canada Australia Europe overseas overnight delivery

  15. Pingback: Plumber Near Me

  16. Pingback: Is Bitcoin Loophole Legit? Read our 2020 Review now!

  17. Pingback: bitcoin era media review

  18. Pingback: bitcoin loophole reviews 2020

  19. Pingback: bitcoin era

  20. Pingback: หนังโป๊

  21. Pingback: mơ thấy tiền đánh con gì

  22. Pingback: Pomsky Puppies

  23. Pingback: Facebook Marketing

  24. Pingback: mơ thấy cây cầu là số mấy

  25. Pingback: nằm mơ bị điện giật

  26. Pingback: ดูอนิเมะ

  27. Pingback: mo thay tat ke

  28. Pingback: DevOps Companies

  29. Pingback: ejuice

  30. Pingback: Buy male sex dolls online

  31. Pingback: Rock Island Armory VR80 12ga Shotgun

  32. Pingback: Grand Power Stribog SP9A3

  33. Pingback: chiem bao thay lua

  34. Pingback: mơ thấy kim cương

  35. Pingback: satta king

  36. Pingback: nằm mơ thấy con chí

  37. Pingback: con dơi số mấy

  38. Pingback: nằm mơ thấy bị gãy răng

  39. Pingback: mơ đi xe máy đánh con gì

  40. Pingback: cung kim nguu hop voi cung nao nhat

  41. Pingback: song tử hợp với cung nào nhất trong tình yêu

  42. Pingback: cây để trong phòng làm việc

  43. Pingback: mơ thấy xương người

  44. Pingback: quả mít là số mấy

  45. Pingback: chom sao bao binh nu

  46. Pingback: mơ thấy đi xe đạp

  47. Pingback: mơ trả nợ đánh con gì

  48. Pingback: chiem bao thay di chua

  49. Pingback: mơ thấy thầy giáo cũ

  50. Pingback: 500 metformin

  51. Pingback: propecia cheap

  52. Pingback: new ed drugs

  53. Pingback: medicine for erectile

  54. Pingback: canada online pharmacy

  55. Pingback: legitimate canadian pharmacy

  56. Pingback: buy cialis doctor

  57. Pingback: lisinopril 7.5 mg

  58. Pingback: buy cialis tadalafil tablets

  59. Pingback: buy cialis now

  60. Pingback: cialis without presciption in usa

  61. Pingback: how to buy cialis online uk

  62. Pingback: buy cialis doctor

  63. Pingback: best ed pills non prescription

  64. Pingback: valtrex price south africa

  65. Pingback: compare ed drugs

  66. Pingback: over the counter viagra

  67. Pingback: prescription drugs online

  68. Pingback: psychological ed treatment

  69. Pingback: buy stromectol online uk

  70. Pingback: hydroxychloroquine 700mg

  71. Pingback: п»їorder stromectol online

  72. Pingback: aspirin and ed

  73. Pingback: best erection pills

  74. Pingback: cheap viagra online

  75. Pingback: buy cialis drug

  76. Pingback: can i buy cialis in uk

Leave a Reply

Your email address will not be published. Required fields are marked *