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













2












$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









share|improve this question









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 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$
    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
















2












$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









share|improve this question









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 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$
    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














2












2








2


1



$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









share|improve this question









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






share|improve this question









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.











share|improve this question









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.









share|improve this question




share|improve this question








edited 2 days ago







Zack E













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




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 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$
    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$
    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$
    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










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.










draft saved

draft discarded


















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.










draft saved

draft discarded


















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.




draft saved


draft discarded














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





















































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

Fairchild Swearingen Metro Inhaltsverzeichnis Geschichte | Innenausstattung | Nutzung | Zwischenfälle...

Pilgersdorf Inhaltsverzeichnis Geografie | Geschichte | Bevölkerungsentwicklung | Politik | Kultur...

Marineschifffahrtleitung Inhaltsverzeichnis Geschichte | Heutige Organisation der NATO | Nationale und...