Array that stores values from two ranges and compares if one cell is blankVBA Text Array - Scan two columns...
About the actual radiative impact of greenhouse gas emission over time
What exactly is this small puffer fish doing and how did it manage to accomplish such a feat?
Does .bashrc contain syntax errors?
Calculate the frequency of characters in a string
Employee lack of ownership
Do I need life insurance if I can cover my own funeral costs?
Python if-else code style for reduced code for rounding floats
What's the meaning of a knight fighting a snail in medieval book illustrations?
Is it normal that my co-workers at a fitness company criticize my food choices?
How are passwords stolen from companies if they only store hashes?
I am trying to parse json data using jq
How could an airship be repaired midflight?
How to terminate ping <dest> &
separate limiter values with a pipe (|)
Can I use USB data pins as a power source?
TikZ node shape depends on inside text
Most cost effective thermostat setting: consistent temperature vs. lowest temperature possible
Print a physical multiplication table
Why is a white electrical wire connected to 2 black wires?
Book: Young man exiled to a penal colony, helps to lead revolution
Is it ever recommended to use mean/multiple imputation when using tree-based predictive models?
Is it insecure to send a password in a `curl` command?
Have the tides ever turned twice on any open problem?
Explaining pyrokinesis powers
Array that stores values from two ranges and compares if one cell is blank
VBA Text Array - Scan two columns rather than oneRefer to other cells besides the one in the Cells.FindReport Building (Data Retrieval, Validation, Aggregation, Business Logic, Report Building, Visual Presentation)Extracting spreadsheet cells using Apache POITrimming whitespace in Excel cellsCompare and find duplicates in 2 corresponding columns in 2 sheetsTest blank cell and out put phonetic (text)VBA code that compares 2 sheets from different systemsMapping one array onto another where columns from first array become rows in second arrayMacro that combines data from multiple worksheets
$begingroup$
This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements.
The long and short of the code is that it checks to see if there is a Customer name in Column B and checks that against Column A; if there is no value in Column A then an input box opens to prompt the user to add the CIF number to the specific named range.
Cells B2 through B9 will always have a value, but not every cell B2 through B9 will be used. Cells A2 through A9 will not always have a value.
Below is the Code:
Sub CheckCIF()
Dim fileArray
Dim finalRow As Long
Dim targetCol As Long
With Sheets("Loan Data")
finalRow = .Range("B" & Rows.Count).End(xlUp).Row
targetCol = .Range("A1").EntireRow.Find("CIF #").Column
fileArray = Array(.Range("B2:B" & finalRow), _
.Range(.Cells(2, targetCol), .Cells(finalRow, targetCol)))
End With
'fileArray(1)(1) Number in first parenthesis is the column and the number in the second parenthesis is the row
If fileArray(1)(1) = vbNullString And fileArray(0)(1) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_1") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(1))
End With
End If
If fileArray(1)(2) = vbNullString And fileArray(0)(2) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_2") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(2))
End With
End If
If fileArray(1)(3) = vbNullString And fileArray(0)(3) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_3") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(3))
End With
End If
If fileArray(1)(4) = vbNullString And fileArray(0)(4) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_4") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(4))
End With
End If
If fileArray(1)(5) = vbNullString And fileArray(0)(5) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_5") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(5))
End With
End If
If fileArray(1)(6) = vbNullString And fileArray(0)(6) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_6") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(6))
End With
End If
If fileArray(1)(7) = vbNullString And fileArray(0)(7) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_7") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(7))
End With
End If
If fileArray(1)(8) = vbNullString And fileArray(0)(8) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_8") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(8))
End With
End If
End Sub
array vba excel
asked 2 days ago
Zack EZack E
1114
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
|
show 3 more comments
$begingroup$
This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements.
The long and short of the code is that it checks to see if there is a Customer name in Column B and checks that against Column A; if there is no value in Column A then an input box opens to prompt the user to add the CIF number to the specific named range.
Cells B2 through B9 will always have a value, but not every cell B2 through B9 will be used. Cells A2 through A9 will not always have a value.
Below is the Code:
Sub CheckCIF()
Dim fileArray
Dim finalRow As Long
Dim targetCol As Long
With Sheets("Loan Data")
finalRow = .Range("B" & Rows.Count).End(xlUp).Row
targetCol = .Range("A1").EntireRow.Find("CIF #").Column
fileArray = Array(.Range("B2:B" & finalRow), _
.Range(.Cells(2, targetCol), .Cells(finalRow, targetCol)))
End With
'fileArray(1)(1) Number in first parenthesis is the column and the number in the second parenthesis is the row
If fileArray(1)(1) = vbNullString And fileArray(0)(1) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_1") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(1))
End With
End If
If fileArray(1)(2) = vbNullString And fileArray(0)(2) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_2") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(2))
End With
End If
If fileArray(1)(3) = vbNullString And fileArray(0)(3) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_3") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(3))
End With
End If
If fileArray(1)(4) = vbNullString And fileArray(0)(4) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_4") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(4))
End With
End If
If fileArray(1)(5) = vbNullString And fileArray(0)(5) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_5") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(5))
End With
End If
If fileArray(1)(6) = vbNullString And fileArray(0)(6) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_6") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(6))
End With
End If
If fileArray(1)(7) = vbNullString And fileArray(0)(7) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_7") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(7))
End With
End If
If fileArray(1)(8) = vbNullString And fileArray(0)(8) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_8") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(8))
End With
End If
End Sub
array vba excel
asked 2 days ago
Zack EZack E
1114
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
$begingroup$
Does this code work as expected? Column A appears to be excluded from the array.
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes the code works as expected. I reference Column A in this linetargetCol = .Range("A1").EntireRow.Find("CIF #").Columnand then in thefileArray(1)(1)where the 1 in the first () is column A.
$endgroup$
– Zack E
2 days ago
$begingroup$
But doesn't fileArray start from B?
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes. I know its backwards, but it is my first Array ever and I get the expected result.
$endgroup$
– Zack E
2 days ago
1
$begingroup$
I will play around with it and see what I come up with. Thanks for looking at this for me.
$endgroup$
– Zack E
2 days ago
|
show 3 more comments
$begingroup$
This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements.
The long and short of the code is that it checks to see if there is a Customer name in Column B and checks that against Column A; if there is no value in Column A then an input box opens to prompt the user to add the CIF number to the specific named range.
Cells B2 through B9 will always have a value, but not every cell B2 through B9 will be used. Cells A2 through A9 will not always have a value.
Below is the Code:
Sub CheckCIF()
Dim fileArray
Dim finalRow As Long
Dim targetCol As Long
With Sheets("Loan Data")
finalRow = .Range("B" & Rows.Count).End(xlUp).Row
targetCol = .Range("A1").EntireRow.Find("CIF #").Column
fileArray = Array(.Range("B2:B" & finalRow), _
.Range(.Cells(2, targetCol), .Cells(finalRow, targetCol)))
End With
'fileArray(1)(1) Number in first parenthesis is the column and the number in the second parenthesis is the row
If fileArray(1)(1) = vbNullString And fileArray(0)(1) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_1") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(1))
End With
End If
If fileArray(1)(2) = vbNullString And fileArray(0)(2) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_2") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(2))
End With
End If
If fileArray(1)(3) = vbNullString And fileArray(0)(3) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_3") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(3))
End With
End If
If fileArray(1)(4) = vbNullString And fileArray(0)(4) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_4") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(4))
End With
End If
If fileArray(1)(5) = vbNullString And fileArray(0)(5) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_5") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(5))
End With
End If
If fileArray(1)(6) = vbNullString And fileArray(0)(6) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_6") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(6))
End With
End If
If fileArray(1)(7) = vbNullString And fileArray(0)(7) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_7") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(7))
End With
End If
If fileArray(1)(8) = vbNullString And fileArray(0)(8) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_8") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(8))
End With
End If
End Sub
array vba excel
asked 2 days ago
Zack EZack E
1114
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements.
The long and short of the code is that it checks to see if there is a Customer name in Column B and checks that against Column A; if there is no value in Column A then an input box opens to prompt the user to add the CIF number to the specific named range.
Cells B2 through B9 will always have a value, but not every cell B2 through B9 will be used. Cells A2 through A9 will not always have a value.
Below is the Code:
Sub CheckCIF()
Dim fileArray
Dim finalRow As Long
Dim targetCol As Long
With Sheets("Loan Data")
finalRow = .Range("B" & Rows.Count).End(xlUp).Row
targetCol = .Range("A1").EntireRow.Find("CIF #").Column
fileArray = Array(.Range("B2:B" & finalRow), _
.Range(.Cells(2, targetCol), .Cells(finalRow, targetCol)))
End With
'fileArray(1)(1) Number in first parenthesis is the column and the number in the second parenthesis is the row
If fileArray(1)(1) = vbNullString And fileArray(0)(1) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_1") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(1))
End With
End If
If fileArray(1)(2) = vbNullString And fileArray(0)(2) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_2") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(2))
End With
End If
If fileArray(1)(3) = vbNullString And fileArray(0)(3) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_3") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(3))
End With
End If
If fileArray(1)(4) = vbNullString And fileArray(0)(4) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_4") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(4))
End With
End If
If fileArray(1)(5) = vbNullString And fileArray(0)(5) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_5") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(5))
End With
End If
If fileArray(1)(6) = vbNullString And fileArray(0)(6) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_6") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(6))
End With
End If
If fileArray(1)(7) = vbNullString And fileArray(0)(7) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_7") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(7))
End With
End If
If fileArray(1)(8) = vbNullString And fileArray(0)(8) <> vbNullString Then
With Sheets("Loan Data")
.Range("CIF_8") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(8))
End With
End If
End Sub
array vba excel
array vba excel
asked 2 days ago
Zack EZack E
1114
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Zack EZack E
1114
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
Zack E
asked 2 days ago
Zack EZack E
1114
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Zack EZack E
1114
asked 2 days ago
Zack EZack E
1114
1114
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Zack E is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$begingroup$
Does this code work as expected? Column A appears to be excluded from the array.
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes the code works as expected. I reference Column A in this linetargetCol = .Range("A1").EntireRow.Find("CIF #").Columnand then in thefileArray(1)(1)where the 1 in the first () is column A.
$endgroup$
– Zack E
2 days ago
$begingroup$
But doesn't fileArray start from B?
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes. I know its backwards, but it is my first Array ever and I get the expected result.
$endgroup$
– Zack E
2 days ago
1
$begingroup$
I will play around with it and see what I come up with. Thanks for looking at this for me.
$endgroup$
– Zack E
2 days ago
|
show 3 more comments
$begingroup$
Does this code work as expected? Column A appears to be excluded from the array.
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes the code works as expected. I reference Column A in this linetargetCol = .Range("A1").EntireRow.Find("CIF #").Columnand then in thefileArray(1)(1)where the 1 in the first () is column A.
$endgroup$
– Zack E
2 days ago
$begingroup$
But doesn't fileArray start from B?
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes. I know its backwards, but it is my first Array ever and I get the expected result.
$endgroup$
– Zack E
2 days ago
1
$begingroup$
I will play around with it and see what I come up with. Thanks for looking at this for me.
$endgroup$
– Zack E
2 days ago
$begingroup$
Does this code work as expected? Column A appears to be excluded from the array.
$endgroup$
– QHarr
2 days ago
$begingroup$
Does this code work as expected? Column A appears to be excluded from the array.
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes the code works as expected. I reference Column A in this line
targetCol = .Range("A1").EntireRow.Find("CIF #").Column and then in the fileArray(1)(1) where the 1 in the first () is column A.$endgroup$
– Zack E
2 days ago
$begingroup$
Yes the code works as expected. I reference Column A in this line
targetCol = .Range("A1").EntireRow.Find("CIF #").Column and then in the fileArray(1)(1) where the 1 in the first () is column A.$endgroup$
– Zack E
2 days ago
$begingroup$
But doesn't fileArray start from B?
$endgroup$
– QHarr
2 days ago
$begingroup$
But doesn't fileArray start from B?
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes. I know its backwards, but it is my first Array ever and I get the expected result.
$endgroup$
– Zack E
2 days ago
$begingroup$
Yes. I know its backwards, but it is my first Array ever and I get the expected result.
$endgroup$
– Zack E
2 days ago
1
1
$begingroup$
I will play around with it and see what I come up with. Thanks for looking at this for me.
$endgroup$
– Zack E
2 days ago
$begingroup$
I will play around with it and see what I come up with. Thanks for looking at this for me.
$endgroup$
– Zack E
2 days ago
|
show 3 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Zack E is a new contributor. Be nice, and check out our Code of Conduct.
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%2fcodereview.stackexchange.com%2fquestions%2f215436%2farray-that-stores-values-from-two-ranges-and-compares-if-one-cell-is-blank%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Zack E is a new contributor. Be nice, and check out our Code of Conduct.
Zack E is a new contributor. Be nice, and check out our Code of Conduct.
Zack E is a new contributor. Be nice, and check out our Code of Conduct.
Zack E is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f215436%2farray-that-stores-values-from-two-ranges-and-compares-if-one-cell-is-blank%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
$begingroup$
Does this code work as expected? Column A appears to be excluded from the array.
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes the code works as expected. I reference Column A in this line
targetCol = .Range("A1").EntireRow.Find("CIF #").Columnand then in thefileArray(1)(1)where the 1 in the first () is column A.$endgroup$
– Zack E
2 days ago
$begingroup$
But doesn't fileArray start from B?
$endgroup$
– QHarr
2 days ago
$begingroup$
Yes. I know its backwards, but it is my first Array ever and I get the expected result.
$endgroup$
– Zack E
2 days ago
1
$begingroup$
I will play around with it and see what I come up with. Thanks for looking at this for me.
$endgroup$
– Zack E
2 days ago