Hello,
I am trying to build an XLOOKUP into my code that will lookup the first value underneath the header "STATE CD 1", as my columns can vary depending on the workbook. I am not sure if I should use the lookup in this code, or if I should make it separately so I am open to feedback on this point.
Below is my current code and attached sample screenshot, I need to xlookup the state codes to my min wage table using the STATE MW column, then I also need to have the MW * HRS column multiply the "Reg Hours" * "STATE MW" and pull the value into MW * HRS column. The reg hours needs to be coded like I have my State CD 1 column due to the column numbers potentially changing (I do this for multiple companies so placement can vary).
Here is the current xlookup formula =XLOOKUP(BL2,'https://thyssenkrupp.sharepoint.com/teams/CentralizedPayroll/Freigegebene Dokumente/General/Audit Documents General/Gross Pay Compare/2024/[00. Min Wage Lookup Table.xlsx]Sheet1'!$A:$A,'https://thyssenkrupp.sharepoint.com/teams/CentralizedPayroll/Freigegebene Dokumente/General/Audit Documents General/Gross Pay Compare/2024/[00. Min Wage Lookup Table.xlsx]Sheet1'!$B:$B,FALSE)
Sub InsertTwoColumnsRightOfStateCD1()
Dim ws As Worksheet
Dim stateCDColumn As Range
' Set the worksheet
Set ws = ThisWorkbook.Sheets("CSV Earnings Statement Register")
' Find the "STATE CD 1" column
Set stateCDColumn = ws.Rows(1).Find(What:="STATE CD 1", LookIn:=xlValues, LookAt:=xlWhole)
' If the column is found, insert two columns to the right
If Not stateCDColumn Is Nothing Then
stateCDColumn.Offset(0, 1).Resize(1, 2).EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
MsgBox "Two columns inserted to the right of STATE CD 1."
' Set the new column ranges
Set newColumn1 = stateCDColumn.Offset(0, 1)
Set newColumn2 = stateCDColumn.Offset(0, 2)
' Name the new columns
newColumn1.Value = "STATE MW"
newColumn2.Value = "MW * HRS"
Else
MsgBox "STATE CD 1 column not found!"
End If
End Sub
I am trying to build an XLOOKUP into my code that will lookup the first value underneath the header "STATE CD 1", as my columns can vary depending on the workbook. I am not sure if I should use the lookup in this code, or if I should make it separately so I am open to feedback on this point.
Below is my current code and attached sample screenshot, I need to xlookup the state codes to my min wage table using the STATE MW column, then I also need to have the MW * HRS column multiply the "Reg Hours" * "STATE MW" and pull the value into MW * HRS column. The reg hours needs to be coded like I have my State CD 1 column due to the column numbers potentially changing (I do this for multiple companies so placement can vary).
Here is the current xlookup formula =XLOOKUP(BL2,'https://thyssenkrupp.sharepoint.com/teams/CentralizedPayroll/Freigegebene Dokumente/General/Audit Documents General/Gross Pay Compare/2024/[00. Min Wage Lookup Table.xlsx]Sheet1'!$A:$A,'https://thyssenkrupp.sharepoint.com/teams/CentralizedPayroll/Freigegebene Dokumente/General/Audit Documents General/Gross Pay Compare/2024/[00. Min Wage Lookup Table.xlsx]Sheet1'!$B:$B,FALSE)
Sub InsertTwoColumnsRightOfStateCD1()
Dim ws As Worksheet
Dim stateCDColumn As Range
' Set the worksheet
Set ws = ThisWorkbook.Sheets("CSV Earnings Statement Register")
' Find the "STATE CD 1" column
Set stateCDColumn = ws.Rows(1).Find(What:="STATE CD 1", LookIn:=xlValues, LookAt:=xlWhole)
' If the column is found, insert two columns to the right
If Not stateCDColumn Is Nothing Then
stateCDColumn.Offset(0, 1).Resize(1, 2).EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
MsgBox "Two columns inserted to the right of STATE CD 1."
' Set the new column ranges
Set newColumn1 = stateCDColumn.Offset(0, 1)
Set newColumn2 = stateCDColumn.Offset(0, 2)
' Name the new columns
newColumn1.Value = "STATE MW"
newColumn2.Value = "MW * HRS"
Else
MsgBox "STATE CD 1 column not found!"
End If
End Sub
