VBA: Placing the values of different variables across a row of cells












1















This macro is a "save" function that takes a given set of parameters in a model and saves them across a row in a table. I'm able to "move" across the row using a for loop and offset, but I'm unsure of how to save different variables in each cell.



Sub TestOffset()

Dim Counter As Integer
Dim StartingCell As Range
Set StartingCell = Range("C10")

For Counter = 0 To 15

StartingCell.Offset(0, Counter).Value = 1

Next Counter

End Sub


This is my current code, but instead of putting "1" into every cell, I'd like to put different variables such as Demand, Supply, Cost, Capacity in each. Is that possible?










share|improve this question





























    1















    This macro is a "save" function that takes a given set of parameters in a model and saves them across a row in a table. I'm able to "move" across the row using a for loop and offset, but I'm unsure of how to save different variables in each cell.



    Sub TestOffset()

    Dim Counter As Integer
    Dim StartingCell As Range
    Set StartingCell = Range("C10")

    For Counter = 0 To 15

    StartingCell.Offset(0, Counter).Value = 1

    Next Counter

    End Sub


    This is my current code, but instead of putting "1" into every cell, I'd like to put different variables such as Demand, Supply, Cost, Capacity in each. Is that possible?










    share|improve this question



























      1












      1








      1








      This macro is a "save" function that takes a given set of parameters in a model and saves them across a row in a table. I'm able to "move" across the row using a for loop and offset, but I'm unsure of how to save different variables in each cell.



      Sub TestOffset()

      Dim Counter As Integer
      Dim StartingCell As Range
      Set StartingCell = Range("C10")

      For Counter = 0 To 15

      StartingCell.Offset(0, Counter).Value = 1

      Next Counter

      End Sub


      This is my current code, but instead of putting "1" into every cell, I'd like to put different variables such as Demand, Supply, Cost, Capacity in each. Is that possible?










      share|improve this question
















      This macro is a "save" function that takes a given set of parameters in a model and saves them across a row in a table. I'm able to "move" across the row using a for loop and offset, but I'm unsure of how to save different variables in each cell.



      Sub TestOffset()

      Dim Counter As Integer
      Dim StartingCell As Range
      Set StartingCell = Range("C10")

      For Counter = 0 To 15

      StartingCell.Offset(0, Counter).Value = 1

      Next Counter

      End Sub


      This is my current code, but instead of putting "1" into every cell, I'd like to put different variables such as Demand, Supply, Cost, Capacity in each. Is that possible?







      excel vba






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Dec 30 '18 at 6:50









      Cindy Meister

      14.6k102234




      14.6k102234










      asked Dec 30 '18 at 5:19









      E. DoeE. Doe

      61




      61
























          2 Answers
          2






          active

          oldest

          votes


















          0














          Try using Choose function



          Sub TestOffset()

          Dim Counter As Integer
          Dim StartingCell As Range

          Dim Demand As Long, Supply As Long, Cost As Long, Capacity As Long

          Set StartingCell = Range("C10")

          Demand = 5
          Supply = 6
          Cost = 7
          Capacity = 8

          For Counter = 1 To 4

          StartingCell.Offset(0, Counter).Value = Choose(Counter, Demand, Supply, Cost, Capacity)

          Next Counter

          End Sub




          Output



          enter image description here






          share|improve this answer































            0














            Variables Array



            Make sure you put the vntVariables line after the values for the variables have changed.



            Sub TestOffset()

            Dim vntVariables As Variant ' Variables Array
            Dim Counter As Integer ' Columns Counter / Array Rows Counter
            Dim StartingCell As Range ' Starting Cell

            Dim Demand As Long
            Dim Supply As Long
            Dim Cost As Double ' Maybe Currency
            Dim Capacity As Long

            Set StartingCell = Range("C10")

            Demand = 3
            Supply = 7
            Cost = 2.99
            Capacity = 10

            vntVariables = Array(Demand, Supply, Cost, Capacity)

            For Counter = 0 To UBound(vntVariables)

            StartingCell.Offset(0, Counter).Value = vntVariables(Counter)

            Next

            End Sub





            share|improve this answer























              Your Answer






              StackExchange.ifUsing("editor", function () {
              StackExchange.using("externalEditor", function () {
              StackExchange.using("snippets", function () {
              StackExchange.snippets.init();
              });
              });
              }, "code-snippets");

              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "1"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53975447%2fvba-placing-the-values-of-different-variables-across-a-row-of-cells%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              0














              Try using Choose function



              Sub TestOffset()

              Dim Counter As Integer
              Dim StartingCell As Range

              Dim Demand As Long, Supply As Long, Cost As Long, Capacity As Long

              Set StartingCell = Range("C10")

              Demand = 5
              Supply = 6
              Cost = 7
              Capacity = 8

              For Counter = 1 To 4

              StartingCell.Offset(0, Counter).Value = Choose(Counter, Demand, Supply, Cost, Capacity)

              Next Counter

              End Sub




              Output



              enter image description here






              share|improve this answer




























                0














                Try using Choose function



                Sub TestOffset()

                Dim Counter As Integer
                Dim StartingCell As Range

                Dim Demand As Long, Supply As Long, Cost As Long, Capacity As Long

                Set StartingCell = Range("C10")

                Demand = 5
                Supply = 6
                Cost = 7
                Capacity = 8

                For Counter = 1 To 4

                StartingCell.Offset(0, Counter).Value = Choose(Counter, Demand, Supply, Cost, Capacity)

                Next Counter

                End Sub




                Output



                enter image description here






                share|improve this answer


























                  0












                  0








                  0







                  Try using Choose function



                  Sub TestOffset()

                  Dim Counter As Integer
                  Dim StartingCell As Range

                  Dim Demand As Long, Supply As Long, Cost As Long, Capacity As Long

                  Set StartingCell = Range("C10")

                  Demand = 5
                  Supply = 6
                  Cost = 7
                  Capacity = 8

                  For Counter = 1 To 4

                  StartingCell.Offset(0, Counter).Value = Choose(Counter, Demand, Supply, Cost, Capacity)

                  Next Counter

                  End Sub




                  Output



                  enter image description here






                  share|improve this answer













                  Try using Choose function



                  Sub TestOffset()

                  Dim Counter As Integer
                  Dim StartingCell As Range

                  Dim Demand As Long, Supply As Long, Cost As Long, Capacity As Long

                  Set StartingCell = Range("C10")

                  Demand = 5
                  Supply = 6
                  Cost = 7
                  Capacity = 8

                  For Counter = 1 To 4

                  StartingCell.Offset(0, Counter).Value = Choose(Counter, Demand, Supply, Cost, Capacity)

                  Next Counter

                  End Sub




                  Output



                  enter image description here







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Dec 30 '18 at 5:34









                  SantoshSantosh

                  10.4k22959




                  10.4k22959

























                      0














                      Variables Array



                      Make sure you put the vntVariables line after the values for the variables have changed.



                      Sub TestOffset()

                      Dim vntVariables As Variant ' Variables Array
                      Dim Counter As Integer ' Columns Counter / Array Rows Counter
                      Dim StartingCell As Range ' Starting Cell

                      Dim Demand As Long
                      Dim Supply As Long
                      Dim Cost As Double ' Maybe Currency
                      Dim Capacity As Long

                      Set StartingCell = Range("C10")

                      Demand = 3
                      Supply = 7
                      Cost = 2.99
                      Capacity = 10

                      vntVariables = Array(Demand, Supply, Cost, Capacity)

                      For Counter = 0 To UBound(vntVariables)

                      StartingCell.Offset(0, Counter).Value = vntVariables(Counter)

                      Next

                      End Sub





                      share|improve this answer




























                        0














                        Variables Array



                        Make sure you put the vntVariables line after the values for the variables have changed.



                        Sub TestOffset()

                        Dim vntVariables As Variant ' Variables Array
                        Dim Counter As Integer ' Columns Counter / Array Rows Counter
                        Dim StartingCell As Range ' Starting Cell

                        Dim Demand As Long
                        Dim Supply As Long
                        Dim Cost As Double ' Maybe Currency
                        Dim Capacity As Long

                        Set StartingCell = Range("C10")

                        Demand = 3
                        Supply = 7
                        Cost = 2.99
                        Capacity = 10

                        vntVariables = Array(Demand, Supply, Cost, Capacity)

                        For Counter = 0 To UBound(vntVariables)

                        StartingCell.Offset(0, Counter).Value = vntVariables(Counter)

                        Next

                        End Sub





                        share|improve this answer


























                          0












                          0








                          0







                          Variables Array



                          Make sure you put the vntVariables line after the values for the variables have changed.



                          Sub TestOffset()

                          Dim vntVariables As Variant ' Variables Array
                          Dim Counter As Integer ' Columns Counter / Array Rows Counter
                          Dim StartingCell As Range ' Starting Cell

                          Dim Demand As Long
                          Dim Supply As Long
                          Dim Cost As Double ' Maybe Currency
                          Dim Capacity As Long

                          Set StartingCell = Range("C10")

                          Demand = 3
                          Supply = 7
                          Cost = 2.99
                          Capacity = 10

                          vntVariables = Array(Demand, Supply, Cost, Capacity)

                          For Counter = 0 To UBound(vntVariables)

                          StartingCell.Offset(0, Counter).Value = vntVariables(Counter)

                          Next

                          End Sub





                          share|improve this answer













                          Variables Array



                          Make sure you put the vntVariables line after the values for the variables have changed.



                          Sub TestOffset()

                          Dim vntVariables As Variant ' Variables Array
                          Dim Counter As Integer ' Columns Counter / Array Rows Counter
                          Dim StartingCell As Range ' Starting Cell

                          Dim Demand As Long
                          Dim Supply As Long
                          Dim Cost As Double ' Maybe Currency
                          Dim Capacity As Long

                          Set StartingCell = Range("C10")

                          Demand = 3
                          Supply = 7
                          Cost = 2.99
                          Capacity = 10

                          vntVariables = Array(Demand, Supply, Cost, Capacity)

                          For Counter = 0 To UBound(vntVariables)

                          StartingCell.Offset(0, Counter).Value = vntVariables(Counter)

                          Next

                          End Sub






                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Dec 30 '18 at 19:56









                          VBasic2008VBasic2008

                          2,5812414




                          2,5812414






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Stack Overflow!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53975447%2fvba-placing-the-values-of-different-variables-across-a-row-of-cells%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Monofisismo

                              Angular Downloading a file using contenturl with Basic Authentication

                              Olmecas