How to formulate Index - Match to include conditions in VBA?

Multi tool use
Multi tool use












0















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.










share|improve this question




















  • 3





    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






  • 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
















0















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.










share|improve this question




















  • 3





    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






  • 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














0












0








0








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.










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 2 at 18:18







LewisThelemon

















asked Jan 2 at 15:57









LewisThelemonLewisThelemon

227




227








  • 3





    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






  • 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





    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






  • 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












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


















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
















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














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





















































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
dXMsoe 2aWEpW,G3gPZ,JZDakWIokZZYMhE2HiRPl qB,c,6JqdvCGSj9seAPnnjiIA,5K,8JdJAx2Bv,ESbCFxKxAxMTNPmD,9p

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas