Hi
can any one help me in getting the below scenario.
i have 2 sheets in excel. sheet 1 with data. sheet2 has column headers as reference in column B2
i want to take column headers from sheet1 . Iterate through sheet2 (column 2) .If my header value matches then take the B3 value from sheet 2.It can b M(Mandatory field check), B(Boolean value check) and so on..
I have the code snippet for doing the check operations but it is hardcoded with the columnname which i want to be dynamic (retrieve the column cell based on matching column header) .
And i also struggle to get the header and find it in sheet2. can any of u help. I am attaching the 2 excel sheet and code
in the attached sheets, sheet1 finds the header in sheet2 of any order, then it takes the value M/B. in my sheet1, lastname row5 s empty which has to be highlighted since its mandate.the same way for country.
Dim intResult As Integer
Dim Data_sh1 As Worksheet, User_Details_sh2 As Worksheet
Dim Data_Lr As Long, User_Details_lr As Long, lc As Long, lc1 As Long, i As Long, j As Long
Dim a() As Variant, b() As Variant
Set Data_sh1 = Sheets("Service User") 'origin
Set User_Details_sh2 = Sheets("User Details") 'destination
'last row on origin sheet
Data_Lr = Data_sh1.Range("A" & Rows.Count).End(xlUp).Row
'last row on destination sheet
User_Details_lr = User_Details_sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
'Store headers in the "a" variable of the origin sheet
lc = Data_sh1.Cells(1, Columns.Count).End(xlToLeft).Column
a = WorksheetFunction.Transpose(Data_sh1.Range("A1", Data_sh1.Cells(1, lc)).Value)
'Store headers in the "b" variable of the destination sheet
lc1 = User_Details_sh2.Range("B" & Rows.Count).End(xlUp).Row
b = WorksheetFunction.Transpose(User_Details_sh2.Range("B2", User_Details_sh2.Cells(1, lc1)).Value)
MsgBox lc
Dim TimeZone_i As Integer
Dim TimeZone_rng As Range
Dim Picklist_TimeZone As Long
With Data_sh1
For TimeZone_i = 1 To .Range("A1").SpecialCells(xlCellTypeLastCell).Column
Set TimeZone_rng = .Range("A1:A" & TimeZone_i)
If WorksheetFunction.CountIf(User_Details_sh2.Range("B:B"), TimeZone_rng) = 0 Then 'picklist reference sheet column E
MsgBox User_Details_sh2.Range("B:B").Cells.Value
Picklist_TimeZone = Picklist_TimeZone + 1
Else
TimeZone_rng.Interior.Color = vbWhite
End If
Next
End With
individual mandatory check code hardcoded with column cell .which i want dynamic too
''Get the last row of a sheet
'LR = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
'
'Dim External_ID_i As Long
'Dim External_ID_c As Long
'Dim External_ID_myRange As Range
'Dim External_ID_myCell As Range
'Dim MyList(2) As String ' create array with definite size and value
'MyList(0) = "Open" ' assign value
'MyList(1) = "Closed"
'
'Set External_ID_myRange = Range("A1:A" & LR)
'
'For Each External_ID_myCell In External_ID_myRange '
' External_ID_c = External_ID_c + 1
' If IsEmpty(External_ID_myCell) Then
' External_ID_myCell.Interior.Color = RGB(255, 87, 87)
' External_ID_i = External_ID_i + 1
'
' Else
' External_ID_myCell.Interior.Color = vbWhite
' End If
'
'
'Next External_ID_myCell
'
'If External_ID_i > 0 Then
'MsgBox _
'"External ID (Column A) : There are total " & External_ID_i & " empty cells out of " & External_ID_c & ".Input a Number."
'
generic_sheet2.jpggeneric_sheet1.jpg