How to formulate Index - Match to include conditions in VBA?
![Multi tool use Multi tool use](http://sgv.ssvwv.com/sg/ssvwvcomimagb.png)
Multi tool use
I am trying to formulate an Index - Match function with three conditions:
Condition 1: It should check for only the highlighted cells
Condition 2: It should check for the same person in Scheduled Off Column
Condition 3: It should not repeat the same person again
I have two worksheets. On Sheet 1 I have the table with Positions against Employee. On Sheet2 I have the table where the Index match will be applicable. It also contains the Highlighted cells and drop down menu.
The Code should select name from the drop down list and not override the list
I have set Named Ranges in both the sheets. What I have tried is the following code.
Option Explicit
'use a constant to store the highlight color...
Const HIGHLIGHT_COLOR = 9894500 'RGB(100, 250, 150)'Is a cell highlighted?
EDIT: changed the function name to IsHighlighted
Function IsHighlighted(c As range)
IsHighlighted = (c.Interior.Color = HIGHLIGHT_COLOR)
End Function
Sub AssignBided()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cel1 As range
Dim cel2 As range
Dim Bid As range
Dim line As range
Dim Offemp As range
Dim BidL8 As range
Dim BidL8E As range
Dim coresVal As String
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set Bid = ws2.range("Bid")
Set line = ws2.range("All_Pos_Hilight_Mon")
Set Offemp = ws2.range("Off_Mon") 'Scheduled Off Column
Set BidL8 = ws1.range("Bid_Pos") 'Bided Position
Set BidL8E = ws1.range("Bid_Emp") 'Bided Employee for a Position
For Each cel1 In BidL8E
For Each cel2 In line
If IsHighlighted(cel2) Then
If Application.WorksheetFunction.CountIf(Offemp, cel1.Value) > 0 Then
coresVal = Evaluate("Index(" & BidL8E.Address & "),MATCH(" & cel2.Validation & "," & BidL8.Address & ",0))")
Debug.Print coresVal
cel2.Offset(0, 2).Value = coresVal
End If
End If
Next cel2
Next cel1
End Sub
This code is not executing. I am getting the error:
Object doesn't support this property or method
It highlights line 33. If any one can help me in this it would be greatly appreciated. Thank you.
excel vba excel-vba
|
show 4 more comments
I am trying to formulate an Index - Match function with three conditions:
Condition 1: It should check for only the highlighted cells
Condition 2: It should check for the same person in Scheduled Off Column
Condition 3: It should not repeat the same person again
I have two worksheets. On Sheet 1 I have the table with Positions against Employee. On Sheet2 I have the table where the Index match will be applicable. It also contains the Highlighted cells and drop down menu.
The Code should select name from the drop down list and not override the list
I have set Named Ranges in both the sheets. What I have tried is the following code.
Option Explicit
'use a constant to store the highlight color...
Const HIGHLIGHT_COLOR = 9894500 'RGB(100, 250, 150)'Is a cell highlighted?
EDIT: changed the function name to IsHighlighted
Function IsHighlighted(c As range)
IsHighlighted = (c.Interior.Color = HIGHLIGHT_COLOR)
End Function
Sub AssignBided()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cel1 As range
Dim cel2 As range
Dim Bid As range
Dim line As range
Dim Offemp As range
Dim BidL8 As range
Dim BidL8E As range
Dim coresVal As String
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set Bid = ws2.range("Bid")
Set line = ws2.range("All_Pos_Hilight_Mon")
Set Offemp = ws2.range("Off_Mon") 'Scheduled Off Column
Set BidL8 = ws1.range("Bid_Pos") 'Bided Position
Set BidL8E = ws1.range("Bid_Emp") 'Bided Employee for a Position
For Each cel1 In BidL8E
For Each cel2 In line
If IsHighlighted(cel2) Then
If Application.WorksheetFunction.CountIf(Offemp, cel1.Value) > 0 Then
coresVal = Evaluate("Index(" & BidL8E.Address & "),MATCH(" & cel2.Validation & "," & BidL8.Address & ",0))")
Debug.Print coresVal
cel2.Offset(0, 2).Value = coresVal
End If
End If
Next cel2
Next cel1
End Sub
This code is not executing. I am getting the error:
Object doesn't support this property or method
It highlights line 33. If any one can help me in this it would be greatly appreciated. Thank you.
excel vba excel-vba
3
Index(BidL8E.Address),MATCH
should beIndex(" & BidL8E.Address & ",MATCH
– Scott Craner
Jan 2 at 16:01
You're not the Dragon Reborn are you? Just that @RandAlthor might have a thing to say about that. Sorry... bit off topic there.
– Darren Bartrup-Cook
Jan 2 at 16:03
3
Does Countif ever return a value < 0 ? That would be new...
– Tim Williams
Jan 2 at 16:03
@TimWilliams Thanks for pointing that out. I corrected it.
– LewisThelemon
Jan 2 at 16:07
1
@DarrenBartrup-Cook I dont know, I am having these visions which tell me to go to Toman Head, and a horn of some sort, which I dont know if they exist. :P
– LewisThelemon
Jan 2 at 16:09
|
show 4 more comments
I am trying to formulate an Index - Match function with three conditions:
Condition 1: It should check for only the highlighted cells
Condition 2: It should check for the same person in Scheduled Off Column
Condition 3: It should not repeat the same person again
I have two worksheets. On Sheet 1 I have the table with Positions against Employee. On Sheet2 I have the table where the Index match will be applicable. It also contains the Highlighted cells and drop down menu.
The Code should select name from the drop down list and not override the list
I have set Named Ranges in both the sheets. What I have tried is the following code.
Option Explicit
'use a constant to store the highlight color...
Const HIGHLIGHT_COLOR = 9894500 'RGB(100, 250, 150)'Is a cell highlighted?
EDIT: changed the function name to IsHighlighted
Function IsHighlighted(c As range)
IsHighlighted = (c.Interior.Color = HIGHLIGHT_COLOR)
End Function
Sub AssignBided()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cel1 As range
Dim cel2 As range
Dim Bid As range
Dim line As range
Dim Offemp As range
Dim BidL8 As range
Dim BidL8E As range
Dim coresVal As String
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set Bid = ws2.range("Bid")
Set line = ws2.range("All_Pos_Hilight_Mon")
Set Offemp = ws2.range("Off_Mon") 'Scheduled Off Column
Set BidL8 = ws1.range("Bid_Pos") 'Bided Position
Set BidL8E = ws1.range("Bid_Emp") 'Bided Employee for a Position
For Each cel1 In BidL8E
For Each cel2 In line
If IsHighlighted(cel2) Then
If Application.WorksheetFunction.CountIf(Offemp, cel1.Value) > 0 Then
coresVal = Evaluate("Index(" & BidL8E.Address & "),MATCH(" & cel2.Validation & "," & BidL8.Address & ",0))")
Debug.Print coresVal
cel2.Offset(0, 2).Value = coresVal
End If
End If
Next cel2
Next cel1
End Sub
This code is not executing. I am getting the error:
Object doesn't support this property or method
It highlights line 33. If any one can help me in this it would be greatly appreciated. Thank you.
excel vba excel-vba
I am trying to formulate an Index - Match function with three conditions:
Condition 1: It should check for only the highlighted cells
Condition 2: It should check for the same person in Scheduled Off Column
Condition 3: It should not repeat the same person again
I have two worksheets. On Sheet 1 I have the table with Positions against Employee. On Sheet2 I have the table where the Index match will be applicable. It also contains the Highlighted cells and drop down menu.
The Code should select name from the drop down list and not override the list
I have set Named Ranges in both the sheets. What I have tried is the following code.
Option Explicit
'use a constant to store the highlight color...
Const HIGHLIGHT_COLOR = 9894500 'RGB(100, 250, 150)'Is a cell highlighted?
EDIT: changed the function name to IsHighlighted
Function IsHighlighted(c As range)
IsHighlighted = (c.Interior.Color = HIGHLIGHT_COLOR)
End Function
Sub AssignBided()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cel1 As range
Dim cel2 As range
Dim Bid As range
Dim line As range
Dim Offemp As range
Dim BidL8 As range
Dim BidL8E As range
Dim coresVal As String
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set Bid = ws2.range("Bid")
Set line = ws2.range("All_Pos_Hilight_Mon")
Set Offemp = ws2.range("Off_Mon") 'Scheduled Off Column
Set BidL8 = ws1.range("Bid_Pos") 'Bided Position
Set BidL8E = ws1.range("Bid_Emp") 'Bided Employee for a Position
For Each cel1 In BidL8E
For Each cel2 In line
If IsHighlighted(cel2) Then
If Application.WorksheetFunction.CountIf(Offemp, cel1.Value) > 0 Then
coresVal = Evaluate("Index(" & BidL8E.Address & "),MATCH(" & cel2.Validation & "," & BidL8.Address & ",0))")
Debug.Print coresVal
cel2.Offset(0, 2).Value = coresVal
End If
End If
Next cel2
Next cel1
End Sub
This code is not executing. I am getting the error:
Object doesn't support this property or method
It highlights line 33. If any one can help me in this it would be greatly appreciated. Thank you.
excel vba excel-vba
excel vba excel-vba
edited Jan 2 at 18:18
LewisThelemon
asked Jan 2 at 15:57
![](https://i.stack.imgur.com/Ge6nn.jpg?s=32&g=1)
![](https://i.stack.imgur.com/Ge6nn.jpg?s=32&g=1)
LewisThelemonLewisThelemon
227
227
3
Index(BidL8E.Address),MATCH
should beIndex(" & BidL8E.Address & ",MATCH
– Scott Craner
Jan 2 at 16:01
You're not the Dragon Reborn are you? Just that @RandAlthor might have a thing to say about that. Sorry... bit off topic there.
– Darren Bartrup-Cook
Jan 2 at 16:03
3
Does Countif ever return a value < 0 ? That would be new...
– Tim Williams
Jan 2 at 16:03
@TimWilliams Thanks for pointing that out. I corrected it.
– LewisThelemon
Jan 2 at 16:07
1
@DarrenBartrup-Cook I dont know, I am having these visions which tell me to go to Toman Head, and a horn of some sort, which I dont know if they exist. :P
– LewisThelemon
Jan 2 at 16:09
|
show 4 more comments
3
Index(BidL8E.Address),MATCH
should beIndex(" & BidL8E.Address & ",MATCH
– Scott Craner
Jan 2 at 16:01
You're not the Dragon Reborn are you? Just that @RandAlthor might have a thing to say about that. Sorry... bit off topic there.
– Darren Bartrup-Cook
Jan 2 at 16:03
3
Does Countif ever return a value < 0 ? That would be new...
– Tim Williams
Jan 2 at 16:03
@TimWilliams Thanks for pointing that out. I corrected it.
– LewisThelemon
Jan 2 at 16:07
1
@DarrenBartrup-Cook I dont know, I am having these visions which tell me to go to Toman Head, and a horn of some sort, which I dont know if they exist. :P
– LewisThelemon
Jan 2 at 16:09
3
3
Index(BidL8E.Address),MATCH
should be Index(" & BidL8E.Address & ",MATCH
– Scott Craner
Jan 2 at 16:01
Index(BidL8E.Address),MATCH
should be Index(" & BidL8E.Address & ",MATCH
– Scott Craner
Jan 2 at 16:01
You're not the Dragon Reborn are you? Just that @RandAlthor might have a thing to say about that. Sorry... bit off topic there.
– Darren Bartrup-Cook
Jan 2 at 16:03
You're not the Dragon Reborn are you? Just that @RandAlthor might have a thing to say about that. Sorry... bit off topic there.
– Darren Bartrup-Cook
Jan 2 at 16:03
3
3
Does Countif ever return a value < 0 ? That would be new...
– Tim Williams
Jan 2 at 16:03
Does Countif ever return a value < 0 ? That would be new...
– Tim Williams
Jan 2 at 16:03
@TimWilliams Thanks for pointing that out. I corrected it.
– LewisThelemon
Jan 2 at 16:07
@TimWilliams Thanks for pointing that out. I corrected it.
– LewisThelemon
Jan 2 at 16:07
1
1
@DarrenBartrup-Cook I dont know, I am having these visions which tell me to go to Toman Head, and a horn of some sort, which I dont know if they exist. :P
– LewisThelemon
Jan 2 at 16:09
@DarrenBartrup-Cook I dont know, I am having these visions which tell me to go to Toman Head, and a horn of some sort, which I dont know if they exist. :P
– LewisThelemon
Jan 2 at 16:09
|
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
});
}
});
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%2f54009354%2fhow-to-formulate-index-match-to-include-conditions-in-vba%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
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%2f54009354%2fhow-to-formulate-index-match-to-include-conditions-in-vba%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
q4STBqIhhbQUuinudnH,8hcX1hBA9,YmKWjU8rzJ2ehlxnBCB,zzeTentln,HU
3
Index(BidL8E.Address),MATCH
should beIndex(" & BidL8E.Address & ",MATCH
– Scott Craner
Jan 2 at 16:01
You're not the Dragon Reborn are you? Just that @RandAlthor might have a thing to say about that. Sorry... bit off topic there.
– Darren Bartrup-Cook
Jan 2 at 16:03
3
Does Countif ever return a value < 0 ? That would be new...
– Tim Williams
Jan 2 at 16:03
@TimWilliams Thanks for pointing that out. I corrected it.
– LewisThelemon
Jan 2 at 16:07
1
@DarrenBartrup-Cook I dont know, I am having these visions which tell me to go to Toman Head, and a horn of some sort, which I dont know if they exist. :P
– LewisThelemon
Jan 2 at 16:09