Excel VBA autofilter for multiple wildcard then change field value and filldown
0
I have search until I cannot find how to do this and it work properly. What I am trying to do is find a wildcard value that is more than one. I also would like to fill down column Z.
What is happening is that if I enter more than 1 wildcard it only finds one of them even though the column has many. If there is only 1 returned it inputs Tier 1 then on filldown it defaults back to Tier 2. What am I missing?
Thank you in advance for your help!
ActiveSheet.Range("$A$1:$AB$" & Rows.Count).End(xlUp).AutoFilter Field:=13, Criteria1:=Array( _
"*9365*", "*9575*", "*9375*"), _
Operator:=xlOr
With Worksheets("Raw Data").AutoFilter.Range
Range("Z" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
ActiveCell.FormulaR1C1 = "Tier 1"
With ActiveSheet.UsedRange
.Resize(.Rows.Count - 1).Offset(1).Columns("Z"). _
SpecialCells(xlCellTypeVisible).FillDown
End With
I have tried the fix per @dwirony but my values return no data.
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "Z").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 1).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 1).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$A$1:$AB$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
This is a picture of the result of the filtered list if I manually enter "95"
This code did the trick!
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 13).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 13).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$M$1:$M$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
arrays excel vba autofill autofilter
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
|
show 4 more comments
0
I have search until I cannot find how to do this and it work properly. What I am trying to do is find a wildcard value that is more than one. I also would like to fill down column Z.
What is happening is that if I enter more than 1 wildcard it only finds one of them even though the column has many. If there is only 1 returned it inputs Tier 1 then on filldown it defaults back to Tier 2. What am I missing?
Thank you in advance for your help!
ActiveSheet.Range("$A$1:$AB$" & Rows.Count).End(xlUp).AutoFilter Field:=13, Criteria1:=Array( _
"*9365*", "*9575*", "*9375*"), _
Operator:=xlOr
With Worksheets("Raw Data").AutoFilter.Range
Range("Z" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
ActiveCell.FormulaR1C1 = "Tier 1"
With ActiveSheet.UsedRange
.Resize(.Rows.Count - 1).Offset(1).Columns("Z"). _
SpecialCells(xlCellTypeVisible).FillDown
End With
I have tried the fix per @dwirony but my values return no data.
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "Z").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 1).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 1).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$A$1:$AB$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
This is a picture of the result of the filtered list if I manually enter "95"
This code did the trick!
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 13).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 13).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$M$1:$M$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
arrays excel vba autofill autofilter
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
Why don't you try defining the range by finding the last visible row and setting all the values to"Tier 1"at once?
– Jchang43
Nov 15 '18 at 17:43
You can't use more than 2 criteria with wildcards inAutofilter- I wrote a workaround to this issue here. You have to create an array based on your criteria then filter on that.
– dwirony
Nov 15 '18 at 17:57
@dwirony see my update in my post for more details. I did try the code from you fix but it returns nothing. Did I miss something? I have multiple other areas to be able to use this so it could be a HUGE time saver!
– Dan Lane
Nov 15 '18 at 19:19
@DanLane So you want to filter on column M, is that right?
– dwirony
Nov 15 '18 at 19:46
1
@dwirony I got it to work and posted the updated corrected code above. Thank you!!!
– Dan Lane
Dec 7 '18 at 17:39
|
show 4 more comments
0
0
0
I have search until I cannot find how to do this and it work properly. What I am trying to do is find a wildcard value that is more than one. I also would like to fill down column Z.
What is happening is that if I enter more than 1 wildcard it only finds one of them even though the column has many. If there is only 1 returned it inputs Tier 1 then on filldown it defaults back to Tier 2. What am I missing?
Thank you in advance for your help!
ActiveSheet.Range("$A$1:$AB$" & Rows.Count).End(xlUp).AutoFilter Field:=13, Criteria1:=Array( _
"*9365*", "*9575*", "*9375*"), _
Operator:=xlOr
With Worksheets("Raw Data").AutoFilter.Range
Range("Z" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
ActiveCell.FormulaR1C1 = "Tier 1"
With ActiveSheet.UsedRange
.Resize(.Rows.Count - 1).Offset(1).Columns("Z"). _
SpecialCells(xlCellTypeVisible).FillDown
End With
I have tried the fix per @dwirony but my values return no data.
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "Z").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 1).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 1).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$A$1:$AB$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
This is a picture of the result of the filtered list if I manually enter "95"
This code did the trick!
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 13).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 13).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$M$1:$M$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
arrays excel vba autofill autofilter
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
I have search until I cannot find how to do this and it work properly. What I am trying to do is find a wildcard value that is more than one. I also would like to fill down column Z.
What is happening is that if I enter more than 1 wildcard it only finds one of them even though the column has many. If there is only 1 returned it inputs Tier 1 then on filldown it defaults back to Tier 2. What am I missing?
Thank you in advance for your help!
ActiveSheet.Range("$A$1:$AB$" & Rows.Count).End(xlUp).AutoFilter Field:=13, Criteria1:=Array( _
"*9365*", "*9575*", "*9375*"), _
Operator:=xlOr
With Worksheets("Raw Data").AutoFilter.Range
Range("Z" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
ActiveCell.FormulaR1C1 = "Tier 1"
With ActiveSheet.UsedRange
.Resize(.Rows.Count - 1).Offset(1).Columns("Z"). _
SpecialCells(xlCellTypeVisible).FillDown
End With
I have tried the fix per @dwirony but my values return no data.
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "Z").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 1).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 1).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$A$1:$AB$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
This is a picture of the result of the filtered list if I manually enter "95"
This code did the trick!
Sub AutoFilterWorkaround()
Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long
Set sht = ThisWorkbook.Worksheets("Raw Data")
lastrow = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
'List the parts of the words you need to find here
tofindarr = Array("9365", "9375")
ReDim filterarr(0 To 0)
j = 0
For k = 0 To UBound(tofindarr)
For i = 2 To lastrow
If InStr(sht.Cells(i, 13).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 13).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i
Next k
'Filter on array
sht.Range("$M$1:$M$" & lastrow).AutoFilter Field:=13,
Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub
arrays excel vba autofill autofilter
arrays excel vba autofill autofilter
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
edited Dec 7 '18 at 17:38
Dan Lane
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
asked Nov 15 '18 at 17:41
Dan LaneDan Lane
12
12
Why don't you try defining the range by finding the last visible row and setting all the values to"Tier 1"at once?
– Jchang43
Nov 15 '18 at 17:43
You can't use more than 2 criteria with wildcards inAutofilter- I wrote a workaround to this issue here. You have to create an array based on your criteria then filter on that.
– dwirony
Nov 15 '18 at 17:57
@dwirony see my update in my post for more details. I did try the code from you fix but it returns nothing. Did I miss something? I have multiple other areas to be able to use this so it could be a HUGE time saver!
– Dan Lane
Nov 15 '18 at 19:19
@DanLane So you want to filter on column M, is that right?
– dwirony
Nov 15 '18 at 19:46
1
@dwirony I got it to work and posted the updated corrected code above. Thank you!!!
– Dan Lane
Dec 7 '18 at 17:39
|
show 4 more comments
Why don't you try defining the range by finding the last visible row and setting all the values to"Tier 1"at once?
– Jchang43
Nov 15 '18 at 17:43
You can't use more than 2 criteria with wildcards inAutofilter- I wrote a workaround to this issue here. You have to create an array based on your criteria then filter on that.
– dwirony
Nov 15 '18 at 17:57
@dwirony see my update in my post for more details. I did try the code from you fix but it returns nothing. Did I miss something? I have multiple other areas to be able to use this so it could be a HUGE time saver!
– Dan Lane
Nov 15 '18 at 19:19
@DanLane So you want to filter on column M, is that right?
– dwirony
Nov 15 '18 at 19:46
1
@dwirony I got it to work and posted the updated corrected code above. Thank you!!!
– Dan Lane
Dec 7 '18 at 17:39
Why don't you try defining the range by finding the last visible row and setting all the values to
"Tier 1" at once?– Jchang43
Nov 15 '18 at 17:43
Why don't you try defining the range by finding the last visible row and setting all the values to
"Tier 1" at once?– Jchang43
Nov 15 '18 at 17:43
You can't use more than 2 criteria with wildcards in
Autofilter - I wrote a workaround to this issue here. You have to create an array based on your criteria then filter on that.– dwirony
Nov 15 '18 at 17:57
You can't use more than 2 criteria with wildcards in
Autofilter - I wrote a workaround to this issue here. You have to create an array based on your criteria then filter on that.– dwirony
Nov 15 '18 at 17:57
@dwirony see my update in my post for more details. I did try the code from you fix but it returns nothing. Did I miss something? I have multiple other areas to be able to use this so it could be a HUGE time saver!
– Dan Lane
Nov 15 '18 at 19:19
@dwirony see my update in my post for more details. I did try the code from you fix but it returns nothing. Did I miss something? I have multiple other areas to be able to use this so it could be a HUGE time saver!
– Dan Lane
Nov 15 '18 at 19:19
@DanLane So you want to filter on column M, is that right?
– dwirony
Nov 15 '18 at 19:46
@DanLane So you want to filter on column M, is that right?
– dwirony
Nov 15 '18 at 19:46
1
1
@dwirony I got it to work and posted the updated corrected code above. Thank you!!!
– Dan Lane
Dec 7 '18 at 17:39
@dwirony I got it to work and posted the updated corrected code above. Thank you!!!
– Dan Lane
Dec 7 '18 at 17:39
|
show 4 more comments
0
active
oldest
votes
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
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%2f53325137%2fexcel-vba-autofilter-for-multiple-wildcard-then-change-field-value-and-filldown%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
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
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%2f53325137%2fexcel-vba-autofilter-for-multiple-wildcard-then-change-field-value-and-filldown%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
Why don't you try defining the range by finding the last visible row and setting all the values to
"Tier 1"at once?– Jchang43
Nov 15 '18 at 17:43
You can't use more than 2 criteria with wildcards in
Autofilter- I wrote a workaround to this issue here. You have to create an array based on your criteria then filter on that.– dwirony
Nov 15 '18 at 17:57
@dwirony see my update in my post for more details. I did try the code from you fix but it returns nothing. Did I miss something? I have multiple other areas to be able to use this so it could be a HUGE time saver!
– Dan Lane
Nov 15 '18 at 19:19
@DanLane So you want to filter on column M, is that right?
– dwirony
Nov 15 '18 at 19:46
1
@dwirony I got it to work and posted the updated corrected code above. Thank you!!!
– Dan Lane
Dec 7 '18 at 17:39