Mixing
Check one list against another list (VBA) using LBound and UBound . Or any way that works
I am trying to check a PO list against an open PO list and clear the cell from
the PO List if not in the open PO List. I tried multiple variations of code (below) and this one is giving me a Mismatch error. Usually, I do something like i = 0 to 5 but that's when I know the exact length of the list. Doing this without knowing the length has been a challenge. Any help would be very much appreciated.
Sub POCheck()
Dim OpenPO As Worksheet
Set OpenPO = Worksheets("OpenPO")
Dim All As Worksheet
Set All = Worksheets("All")
Dim OpenPOList As Variant
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
Set AllPO = All.Range("B2:B" & LastRow)
Dim i As Long
LastRow = All.Range("AH" & Rows.Count).End(xlUp).Row
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).Row
For Each cell In AllPO.Cells
For i = LBound(OpenPOList) To UBound(OpenPOList)
Found = False
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Found = True
Exit For
End If
Next i
If Not Found Then cell.Value = ""
Next cell
excel vba excel-vba
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 22:11
BruceBruce
266
add a comment |
I am trying to check a PO list against an open PO list and clear the cell from
the PO List if not in the open PO List. I tried multiple variations of code (below) and this one is giving me a Mismatch error. Usually, I do something like i = 0 to 5 but that's when I know the exact length of the list. Doing this without knowing the length has been a challenge. Any help would be very much appreciated.
Sub POCheck()
Dim OpenPO As Worksheet
Set OpenPO = Worksheets("OpenPO")
Dim All As Worksheet
Set All = Worksheets("All")
Dim OpenPOList As Variant
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
Set AllPO = All.Range("B2:B" & LastRow)
Dim i As Long
LastRow = All.Range("AH" & Rows.Count).End(xlUp).Row
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).Row
For Each cell In AllPO.Cells
For i = LBound(OpenPOList) To UBound(OpenPOList)
Found = False
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Found = True
Exit For
End If
Next i
If Not Found Then cell.Value = ""
Next cell
excel vba excel-vba
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 22:11
BruceBruce
266
1
Initialize LastRow and LastRowRO before using them, and try again. Also indent your code to make it more readable.
– Excelosaurus
Nov 19 '18 at 22:23
2
Suggestion: indentation - really helps with readability of nested block structures.
– Mathieu Guindon
Nov 19 '18 at 22:31
Would you mind showing me how to initialize before using them? Also, I will work on my indentation! Thanks!
– Bruce
Nov 19 '18 at 23:59
add a comment |
I am trying to check a PO list against an open PO list and clear the cell from
the PO List if not in the open PO List. I tried multiple variations of code (below) and this one is giving me a Mismatch error. Usually, I do something like i = 0 to 5 but that's when I know the exact length of the list. Doing this without knowing the length has been a challenge. Any help would be very much appreciated.
Sub POCheck()
Dim OpenPO As Worksheet
Set OpenPO = Worksheets("OpenPO")
Dim All As Worksheet
Set All = Worksheets("All")
Dim OpenPOList As Variant
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
Set AllPO = All.Range("B2:B" & LastRow)
Dim i As Long
LastRow = All.Range("AH" & Rows.Count).End(xlUp).Row
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).Row
For Each cell In AllPO.Cells
For i = LBound(OpenPOList) To UBound(OpenPOList)
Found = False
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Found = True
Exit For
End If
Next i
If Not Found Then cell.Value = ""
Next cell
excel vba excel-vba
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 22:11
BruceBruce
266
I am trying to check a PO list against an open PO list and clear the cell from
the PO List if not in the open PO List. I tried multiple variations of code (below) and this one is giving me a Mismatch error. Usually, I do something like i = 0 to 5 but that's when I know the exact length of the list. Doing this without knowing the length has been a challenge. Any help would be very much appreciated.
Sub POCheck()
Dim OpenPO As Worksheet
Set OpenPO = Worksheets("OpenPO")
Dim All As Worksheet
Set All = Worksheets("All")
Dim OpenPOList As Variant
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
Set AllPO = All.Range("B2:B" & LastRow)
Dim i As Long
LastRow = All.Range("AH" & Rows.Count).End(xlUp).Row
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).Row
For Each cell In AllPO.Cells
For i = LBound(OpenPOList) To UBound(OpenPOList)
Found = False
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Found = True
Exit For
End If
Next i
If Not Found Then cell.Value = ""
Next cell
excel vba excel-vba
excel vba excel-vba
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 22:11
BruceBruce
266
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 22:11
BruceBruce
266
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
edited Nov 20 '18 at 7:15
Pᴇʜ
20.5k42650
20.5k42650
asked Nov 19 '18 at 22:11
BruceBruce
266
asked Nov 19 '18 at 22:11
BruceBruce
266
asked Nov 19 '18 at 22:11
BruceBruce
266
266
1
Initialize LastRow and LastRowRO before using them, and try again. Also indent your code to make it more readable.
– Excelosaurus
Nov 19 '18 at 22:23
2
Suggestion: indentation - really helps with readability of nested block structures.
– Mathieu Guindon
Nov 19 '18 at 22:31
Would you mind showing me how to initialize before using them? Also, I will work on my indentation! Thanks!
– Bruce
Nov 19 '18 at 23:59
add a comment |
1
Initialize LastRow and LastRowRO before using them, and try again. Also indent your code to make it more readable.
– Excelosaurus
Nov 19 '18 at 22:23
2
Suggestion: indentation - really helps with readability of nested block structures.
– Mathieu Guindon
Nov 19 '18 at 22:31
Would you mind showing me how to initialize before using them? Also, I will work on my indentation! Thanks!
– Bruce
Nov 19 '18 at 23:59
1
1
Initialize LastRow and LastRowRO before using them, and try again. Also indent your code to make it more readable.
– Excelosaurus
Nov 19 '18 at 22:23
Initialize LastRow and LastRowRO before using them, and try again. Also indent your code to make it more readable.
– Excelosaurus
Nov 19 '18 at 22:23
2
2
Suggestion: indentation - really helps with readability of nested block structures.
– Mathieu Guindon
Nov 19 '18 at 22:31
Suggestion: indentation - really helps with readability of nested block structures.
– Mathieu Guindon
Nov 19 '18 at 22:31
Would you mind showing me how to initialize before using them? Also, I will work on my indentation! Thanks!
– Bruce
Nov 19 '18 at 23:59
Would you mind showing me how to initialize before using them? Also, I will work on my indentation! Thanks!
– Bruce
Nov 19 '18 at 23:59
add a comment |
3 Answers
3
active
oldest
votes
It is very quick to use arrays and Application.Match to see if current value is in the array containing the values to match against. No looping cells and data is read in and written out in one go.
Option Explicit
Public Sub POCheck()
Dim openPO As Worksheet, all As Worksheet, lastRow As Long, lastRowPO As Long
Set openPO = ThisWorkbook.Worksheets("OpenPO")
Set all = ThisWorkbook.Worksheets("All")
With all
lastRow = .Range("AH" & .Rows.Count).End(xlUp).Row
End With
With openPO
lastRowPO = .Range("A" & Rows.Count).End(xlUp).Row
End With
Dim openPOList(), allPOList(), i As Long
openPOList = Application.Transpose(openPO.Range("A2:A" & lastRowPO))
allPOList = Application.Transpose(all.Range("B2:B" & lastRow))
For i = LBound(allPOList) To UBound(allPOList)
If IsError(Application.Match(allPOList(i), openPOList, 0)) Then
allPOList(i) = vbNullString
End If
Next
openPO.Range("A2").Resize(UBound(allPOList), 1) = Application.Transpose(allPOList)
End Sub
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
Thanks for this but I am getting a mismatch error.
– Bruce
Nov 19 '18 at 23:56
on which line please? And can you verify the values of lastRowPO and lastRow ? If either are <2 then you will get this error.
– QHarr
Nov 19 '18 at 23:56
Nevermind It works. I had some misspelled Variable names. The only issue besides this was that it was clearing from the wrong list but that was an easy fix. Thanks!
– Bruce
Nov 20 '18 at 20:06
Spoke to soon. Its clearing the cell in OpenPo but I need it to clear the cell in all if it is not in PO.
– Bruce
Nov 20 '18 at 21:23
add a comment |
It is considered a best practice to add Option Explicit to the top of the code modules and declare a variables with the correct datatypes.
Dim LastRow As Long, LastRowPO As Long
Use & not And when concatenating strings.
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
LastRowPO is being used before its value is set.
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).row
OpenPOList = OpenPO.Range("A2:A" & LastRowPO).Value
Use Range.Find to search a group of cells not a single cell.
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Using a Scripting.Dictionary to match unique values is vastly faster then using nested loops. Watch: Excel VBA Introduction Part 39 - Dictionaries.
You should download RubberDuck and use its code formatter often.
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
add a comment |
You can do a vlookup to see if it exists and then clear the value if vlookup in adjacent cell isn't #N/A?
Or loop down the first list and do a countif in VBA to see if it resides within the other list, if it does, clear it?
So may ways to do it in VBA also...
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
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%2f53383380%2fcheck-one-list-against-another-list-vba-using-lbound-and-ubound-or-any-way-t%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
It is very quick to use arrays and Application.Match to see if current value is in the array containing the values to match against. No looping cells and data is read in and written out in one go.
Option Explicit
Public Sub POCheck()
Dim openPO As Worksheet, all As Worksheet, lastRow As Long, lastRowPO As Long
Set openPO = ThisWorkbook.Worksheets("OpenPO")
Set all = ThisWorkbook.Worksheets("All")
With all
lastRow = .Range("AH" & .Rows.Count).End(xlUp).Row
End With
With openPO
lastRowPO = .Range("A" & Rows.Count).End(xlUp).Row
End With
Dim openPOList(), allPOList(), i As Long
openPOList = Application.Transpose(openPO.Range("A2:A" & lastRowPO))
allPOList = Application.Transpose(all.Range("B2:B" & lastRow))
For i = LBound(allPOList) To UBound(allPOList)
If IsError(Application.Match(allPOList(i), openPOList, 0)) Then
allPOList(i) = vbNullString
End If
Next
openPO.Range("A2").Resize(UBound(allPOList), 1) = Application.Transpose(allPOList)
End Sub
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
Thanks for this but I am getting a mismatch error.
– Bruce
Nov 19 '18 at 23:56
on which line please? And can you verify the values of lastRowPO and lastRow ? If either are <2 then you will get this error.
– QHarr
Nov 19 '18 at 23:56
Nevermind It works. I had some misspelled Variable names. The only issue besides this was that it was clearing from the wrong list but that was an easy fix. Thanks!
– Bruce
Nov 20 '18 at 20:06
Spoke to soon. Its clearing the cell in OpenPo but I need it to clear the cell in all if it is not in PO.
– Bruce
Nov 20 '18 at 21:23
add a comment |
It is very quick to use arrays and Application.Match to see if current value is in the array containing the values to match against. No looping cells and data is read in and written out in one go.
Option Explicit
Public Sub POCheck()
Dim openPO As Worksheet, all As Worksheet, lastRow As Long, lastRowPO As Long
Set openPO = ThisWorkbook.Worksheets("OpenPO")
Set all = ThisWorkbook.Worksheets("All")
With all
lastRow = .Range("AH" & .Rows.Count).End(xlUp).Row
End With
With openPO
lastRowPO = .Range("A" & Rows.Count).End(xlUp).Row
End With
Dim openPOList(), allPOList(), i As Long
openPOList = Application.Transpose(openPO.Range("A2:A" & lastRowPO))
allPOList = Application.Transpose(all.Range("B2:B" & lastRow))
For i = LBound(allPOList) To UBound(allPOList)
If IsError(Application.Match(allPOList(i), openPOList, 0)) Then
allPOList(i) = vbNullString
End If
Next
openPO.Range("A2").Resize(UBound(allPOList), 1) = Application.Transpose(allPOList)
End Sub
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
Thanks for this but I am getting a mismatch error.
– Bruce
Nov 19 '18 at 23:56
on which line please? And can you verify the values of lastRowPO and lastRow ? If either are <2 then you will get this error.
– QHarr
Nov 19 '18 at 23:56
Nevermind It works. I had some misspelled Variable names. The only issue besides this was that it was clearing from the wrong list but that was an easy fix. Thanks!
– Bruce
Nov 20 '18 at 20:06
Spoke to soon. Its clearing the cell in OpenPo but I need it to clear the cell in all if it is not in PO.
– Bruce
Nov 20 '18 at 21:23
add a comment |
It is very quick to use arrays and Application.Match to see if current value is in the array containing the values to match against. No looping cells and data is read in and written out in one go.
Option Explicit
Public Sub POCheck()
Dim openPO As Worksheet, all As Worksheet, lastRow As Long, lastRowPO As Long
Set openPO = ThisWorkbook.Worksheets("OpenPO")
Set all = ThisWorkbook.Worksheets("All")
With all
lastRow = .Range("AH" & .Rows.Count).End(xlUp).Row
End With
With openPO
lastRowPO = .Range("A" & Rows.Count).End(xlUp).Row
End With
Dim openPOList(), allPOList(), i As Long
openPOList = Application.Transpose(openPO.Range("A2:A" & lastRowPO))
allPOList = Application.Transpose(all.Range("B2:B" & lastRow))
For i = LBound(allPOList) To UBound(allPOList)
If IsError(Application.Match(allPOList(i), openPOList, 0)) Then
allPOList(i) = vbNullString
End If
Next
openPO.Range("A2").Resize(UBound(allPOList), 1) = Application.Transpose(allPOList)
End Sub
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
It is very quick to use arrays and Application.Match to see if current value is in the array containing the values to match against. No looping cells and data is read in and written out in one go.
Option Explicit
Public Sub POCheck()
Dim openPO As Worksheet, all As Worksheet, lastRow As Long, lastRowPO As Long
Set openPO = ThisWorkbook.Worksheets("OpenPO")
Set all = ThisWorkbook.Worksheets("All")
With all
lastRow = .Range("AH" & .Rows.Count).End(xlUp).Row
End With
With openPO
lastRowPO = .Range("A" & Rows.Count).End(xlUp).Row
End With
Dim openPOList(), allPOList(), i As Long
openPOList = Application.Transpose(openPO.Range("A2:A" & lastRowPO))
allPOList = Application.Transpose(all.Range("B2:B" & lastRow))
For i = LBound(allPOList) To UBound(allPOList)
If IsError(Application.Match(allPOList(i), openPOList, 0)) Then
allPOList(i) = vbNullString
End If
Next
openPO.Range("A2").Resize(UBound(allPOList), 1) = Application.Transpose(allPOList)
End Sub
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
answered Nov 19 '18 at 23:20
QHarrQHarr
31k81941
31k81941
Thanks for this but I am getting a mismatch error.
– Bruce
Nov 19 '18 at 23:56
on which line please? And can you verify the values of lastRowPO and lastRow ? If either are <2 then you will get this error.
– QHarr
Nov 19 '18 at 23:56
Nevermind It works. I had some misspelled Variable names. The only issue besides this was that it was clearing from the wrong list but that was an easy fix. Thanks!
– Bruce
Nov 20 '18 at 20:06
Spoke to soon. Its clearing the cell in OpenPo but I need it to clear the cell in all if it is not in PO.
– Bruce
Nov 20 '18 at 21:23
add a comment |
Thanks for this but I am getting a mismatch error.
– Bruce
Nov 19 '18 at 23:56
on which line please? And can you verify the values of lastRowPO and lastRow ? If either are <2 then you will get this error.
– QHarr
Nov 19 '18 at 23:56
Nevermind It works. I had some misspelled Variable names. The only issue besides this was that it was clearing from the wrong list but that was an easy fix. Thanks!
– Bruce
Nov 20 '18 at 20:06
Spoke to soon. Its clearing the cell in OpenPo but I need it to clear the cell in all if it is not in PO.
– Bruce
Nov 20 '18 at 21:23
Thanks for this but I am getting a mismatch error.
– Bruce
Nov 19 '18 at 23:56
Thanks for this but I am getting a mismatch error.
– Bruce
Nov 19 '18 at 23:56
on which line please? And can you verify the values of lastRowPO and lastRow ? If either are <2 then you will get this error.
– QHarr
Nov 19 '18 at 23:56
on which line please? And can you verify the values of lastRowPO and lastRow ? If either are <2 then you will get this error.
– QHarr
Nov 19 '18 at 23:56
Nevermind It works. I had some misspelled Variable names. The only issue besides this was that it was clearing from the wrong list but that was an easy fix. Thanks!
– Bruce
Nov 20 '18 at 20:06
Nevermind It works. I had some misspelled Variable names. The only issue besides this was that it was clearing from the wrong list but that was an easy fix. Thanks!
– Bruce
Nov 20 '18 at 20:06
Spoke to soon. Its clearing the cell in OpenPo but I need it to clear the cell in all if it is not in PO.
– Bruce
Nov 20 '18 at 21:23
Spoke to soon. Its clearing the cell in OpenPo but I need it to clear the cell in all if it is not in PO.
– Bruce
Nov 20 '18 at 21:23
add a comment |
It is considered a best practice to add Option Explicit to the top of the code modules and declare a variables with the correct datatypes.
Dim LastRow As Long, LastRowPO As Long
Use & not And when concatenating strings.
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
LastRowPO is being used before its value is set.
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).row
OpenPOList = OpenPO.Range("A2:A" & LastRowPO).Value
Use Range.Find to search a group of cells not a single cell.
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Using a Scripting.Dictionary to match unique values is vastly faster then using nested loops. Watch: Excel VBA Introduction Part 39 - Dictionaries.
You should download RubberDuck and use its code formatter often.
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
add a comment |
It is considered a best practice to add Option Explicit to the top of the code modules and declare a variables with the correct datatypes.
Dim LastRow As Long, LastRowPO As Long
Use & not And when concatenating strings.
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
LastRowPO is being used before its value is set.
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).row
OpenPOList = OpenPO.Range("A2:A" & LastRowPO).Value
Use Range.Find to search a group of cells not a single cell.
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Using a Scripting.Dictionary to match unique values is vastly faster then using nested loops. Watch: Excel VBA Introduction Part 39 - Dictionaries.
You should download RubberDuck and use its code formatter often.
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
add a comment |
It is considered a best practice to add Option Explicit to the top of the code modules and declare a variables with the correct datatypes.
Dim LastRow As Long, LastRowPO As Long
Use & not And when concatenating strings.
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
LastRowPO is being used before its value is set.
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).row
OpenPOList = OpenPO.Range("A2:A" & LastRowPO).Value
Use Range.Find to search a group of cells not a single cell.
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Using a Scripting.Dictionary to match unique values is vastly faster then using nested loops. Watch: Excel VBA Introduction Part 39 - Dictionaries.
You should download RubberDuck and use its code formatter often.
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
It is considered a best practice to add Option Explicit to the top of the code modules and declare a variables with the correct datatypes.
Dim LastRow As Long, LastRowPO As Long
Use & not And when concatenating strings.
OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value
LastRowPO is being used before its value is set.
LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).row
OpenPOList = OpenPO.Range("A2:A" & LastRowPO).Value
Use Range.Find to search a group of cells not a single cell.
If Not cell.Find(OpenPOList(i)) Is Nothing Then
Using a Scripting.Dictionary to match unique values is vastly faster then using nested loops. Watch: Excel VBA Introduction Part 39 - Dictionaries.
You should download RubberDuck and use its code formatter often.
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
answered Nov 19 '18 at 22:50
TinManTinMan
2,196212
2,196212
add a comment |
add a comment |
You can do a vlookup to see if it exists and then clear the value if vlookup in adjacent cell isn't #N/A?
Or loop down the first list and do a countif in VBA to see if it resides within the other list, if it does, clear it?
So may ways to do it in VBA also...
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
add a comment |
You can do a vlookup to see if it exists and then clear the value if vlookup in adjacent cell isn't #N/A?
Or loop down the first list and do a countif in VBA to see if it resides within the other list, if it does, clear it?
So may ways to do it in VBA also...
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
add a comment |
You can do a vlookup to see if it exists and then clear the value if vlookup in adjacent cell isn't #N/A?
Or loop down the first list and do a countif in VBA to see if it resides within the other list, if it does, clear it?
So may ways to do it in VBA also...
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
You can do a vlookup to see if it exists and then clear the value if vlookup in adjacent cell isn't #N/A?
Or loop down the first list and do a countif in VBA to see if it resides within the other list, if it does, clear it?
So may ways to do it in VBA also...
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
answered Nov 19 '18 at 23:02
David JonesDavid Jones
406
406
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%2f53383380%2fcheck-one-list-against-another-list-vba-using-lbound-and-ubound-or-any-way-t%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
1
Initialize LastRow and LastRowRO before using them, and try again. Also indent your code to make it more readable.
– Excelosaurus
Nov 19 '18 at 22:23
2
Suggestion: indentation - really helps with readability of nested block structures.
– Mathieu Guindon
Nov 19 '18 at 22:31
Would you mind showing me how to initialize before using them? Also, I will work on my indentation! Thanks!
– Bruce
Nov 19 '18 at 23:59