Index _ Match function Error

[Priyantha]

Dear All,

I am trying to get deta from "Deta" sheet to "Format" sheet using index match function with excel vba. I tryed two methord but not working.

please help me to solve this issue (What is the error with two error)

My code

G_Allo_01 = Application.Index(ws2.Range("J:J").Value, _
Application.Match(empno & "-" & (Year(endDate)) & "/" & (Month(endDate)), _
Application.Index(ws2.Range("C:C") & "-" & ws.Range("B:B"), 0), 0))

G_Allo_02 = WorksheetFunction.Index(ws2.Range("J:J"), WorksheetFunction.Match(ws.Range("C4"), ws2.Range("C:C"), 0) + _
WorksheetFunction.Match(ws.Range("H4"), ws2.Range("B:B"), 0), 0)

BR

Priyantha.

[HansV]

Try

Code: Select all

    G_Allo_01 = [INDEX(Deta!J:J,MATCH(C4&"-"&I4-DAY(I4)+1, Deta!C:C&"-"&Deta!B:B, 0))]

    G_Allo_02 = [INDEX(Deta!J:J,MATCH(C4,Deta!C:C,0)+MATCH(H4-DAY(H4)+1,Deta!B:B,0))]

See if you can do the same for Hard_Allo_01 and Hard_Allo_02.

[Priyantha]

Dear Hans,

Lot of thanx. it is working.

Cant i apply "ws2.Range("J:J")" for "Deta!J:J" (Set ws2 = ThisWorkbook.Sheets("Deta") ) ? other wise, if i get deta from other workbook and how to modify the code (can't use variable in this code)?

my coloring code not working correcly with my condition, it always colored while running macro.

If G_Allo_01 <> G_Allo_02 Then
ws.Range("M7").Interior.Color = RGB(250, 150, 100)
Else
ws.Range("M7").Interior.Color = xlNone
End If

BR,

Priyantha

[HansV]

You could use

Code: Select all

    G_Allo_01 = Evaluate("INDEX('" & ws2.Name & "'!J:J,MATCH(C4&""-""&I4-DAY(I4)+1, '" & ws2.Name & "'!C:C&""-""&'" & ws2.Name & "'!B:B, 0))")

    G_Allo_02 = Evaluate("INDEX('" & ws2.Name & "'!J:J,MATCH(C4,'" & ws2.Name & "'!C:C,0)+MATCH(H4-DAY(H4)+1,'" & ws2.Name & "'!B:B,0))")

[Priyantha]

It is working correcly.
I used as below,
G_Allo_01 = [INDEX(Deta!J:J,MATCH(C4&"-"&I4-DAY(I4)+1, Deta!C:C&"-"&Deta!B:B, 0))]

G_Allo_01 = [INDEX(Deta!J:J,MATCH(C4&"-"&H4-DAY(H4)+1, Deta!C:C&"-"&Deta!B:B, 0))]
This two veriable get deta correctly fro deta sheet. But "ws.Range("M7").Interior.Color" cell colour not work my condition i try to run code line by line then above variables get correct deta.

But next step,

If G_Allo_01 <> G_Allo_02 Then
ws.Range("M7").Interior.Color = RGB(250, 150, 100)
Else
ws.Range("M7").Interior.Color = xlNone
End If

Although actually value of two days is same, G_Allo_01 gets correct deta, G_Allo_02 not correct Deta in debugging mode always seems "emty"

Please help me to solve this issue

Br,

Priyantha

[HansV]

G_Allo_01 = [INDEX(Deta!J:J,MATCH(C4&"-"&I4-DAY(I4)+1, Deta!C:C&"-"&Deta!B:B, 0))]

G_Allo_01 = [INDEX(Deta!J:J,MATCH(C4&"-"&H4-DAY(H4)+1, Deta!C:C&"-"&Deta!B:B, 0))]

should be

G_Allo_01 = [INDEX(Deta!J:J,MATCH(C4&"-"&I4-DAY(I4)+1, Deta!C:C&"-"&Deta!B:B, 0))]

G_Allo_02 = [INDEX(Deta!J:J,MATCH(C4&"-"&H4-DAY(H4)+1, Deta!C:C&"-"&Deta!B:B, 0))]

[Priyantha]

Dear Hans,

Very sorry! My Mistake.

BR

Priyantha

[Priyantha]

I tryed as next step to get Colum G values (in Deta Sheet) in to F11 to below, according to Cell H4 Value (Start date in format sheet).

If start date is between 1 - 15 (start date 2023/02/05) then colect deta in colom C from 2023 April , other wise start date is from 16 to end of month(2023/02/20), get deta colum C from 2023 May.

As example, if start date is 2023/02/05, F11 & F12 cell value should "0" & From F13 (2023-April) to below should be filled deda (Matching with, C4 & A13 values) from Colum H (Deta Sheet)

If Day(startDate) <= 15 Then
columnCMonth = DateSerial(Year(startDate), Month(startDate) + 2, 1)
Else
columnCMonth = DateSerial(Year(startDate), Month(startDate) + 3, 1)
End If

vlookupResultC = Application.VLookup(columnCMonth & empNo, ws2.Range("A:K"), 8, False)
ws.Range("F11").Value = vlookupResultC

My code not working correctly. i also try index match but not working correcly. help me to solve this issue (vlookup or Indexmatch or any aother methord).

Br,

Priyantha

[HansV]

Can you attach the current version of the workbook with the code?

[Priyantha]

Dear Hans,


Attached here with my file.


BR

Priyantha

[HansV]

You're trying to look up the concatenation of a date and the employee number in column A of the Deta sheet, but that column contains very different values.

The date you're trying to look up is the last day of a month, but that cannot be found anywhere on the Deta sheet. The only dates are in column B, but those are the first day of a month.

[Priyantha]

Dear Hans,

Sorry to keep bothering you.

I am trying to get data from "Deta" sheet, maching to C4 and from A11 to end by looping (year/month). Like "37224 & 2023/ March" in "Format" sheet. E.g. to get the overtime details done in each month related to the employee number in C4.

The data to be obtained is available in the "Data" sheet. That is, column B has the relevant year/month, column C has the employee number and column G has the overtime payment details. (Only a limited amount of data is uploaded in Deta sheet)

The following code will be able to used with changes for this purpose. (Abandoned due to persistent problems while setting variables).
Evaluate("INDEX('" & ws2.Name & "'!G:G,MATCH(C4&""-""&I4-DAY(I4)+1, '" & ws2.Name & "'!C:C&""-""&'" & ws2.Name & "'!B:B, 0))")
I think should be edited "&I4-DAY(I4)+1" with "startrow" and " i" variables.

Furthermore,In addition to the above modification, I am trying to add a new condition based on the start day, As example, if start date, C4 value is 2023/02/05, (Then A11 _ 2023/February, A12 _ 2023/March ect.. ) A11 & A12 cell value should "0" & should be fill deta From A13 (2023/April) to end (Matching with, C4 & A13 values). from Colum G in 'Deta" Sheet. If "C4" date is 16 to day of month end, then A11 , A12 & A13 cell value should "0" & should be fill deta From A14 (2023/May _ 3 month later from start cell C4 value month)

Br,
Priyantha

[HansV]

I cannot help you with this, sorry.

[Priyantha]

Can you help me to corect my code,

This code is supplied by you previasly:
OT = Evaluate("INDEX('" & ws2.Name & "'!G:G,MATCH(C4&""-""&H4-DAY(H4)+1, '" & ws2.Name & "'!C:C&""-""&'" & ws2.Name & "'!B:B, 0))")

I tried to edit this part of the code, &H4-DAY(H4)+1 and i replased &A '"&startRow &'" + '"&i&'"&-DAY(A '"&startRow &'" + '"&i&'")+1

My modified Code:
OT = Evaluate("INDEX('" & ws2.Name & "'!G:G,MATCH(C4&""-""&A '"&startRow &'" + '"&i&'"&-DAY(A '"&startRow &'" + '"&i&'")+1, '" & ws2.Name & "'!C:C&""-""&'" & ws2.Name & "'!B:B, 0))")

Not working correcly. help me.

Br,
Priyantha.

[HansV]

Try

OT = Evaluate("INDEX('" & ws2.Name & "'!G:G, MATCH(C4&""-""&A" & startRow + i & "-DAY(A" & startRow + i & ")+1, '" & ws2.Name & "'!C:C&""-""&'" & ws2.Name & "'!B:B, 0))")

[Priyantha]

Thanks. it is working.

Br,

Priyantha.