VBA: Placing the values of different variables across a row of cells
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
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
add a comment |
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
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
add a comment |
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
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
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
excel vba
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
edited Dec 30 '18 at 6:50
Cindy Meister
14.6k102234
14.6k102234
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
asked Dec 30 '18 at 5:19
E. DoeE. Doe
61
61
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
add a comment |
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
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
add a comment |
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
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
add a comment |
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
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
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
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
answered Dec 30 '18 at 5:34
SantoshSantosh
10.4k22959
10.4k22959
add a comment |
add a comment |
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
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
add a comment |
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
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
add a comment |
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
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
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
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
answered Dec 30 '18 at 19:56
VBasic2008VBasic2008
2,5812414
2,5812414
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
