murthysri272
02-10-2016, 04:22 PM
Hi,
In the below code, It is returning "D9 cell data from Accounts Work sheet" in all cells from F20 to F3000.
End result should be as below
If B20 = any of (D9 to D3000), the cells adjacent to the matching cell (Lets suppose B20 = D20, then E20 should come as my output) should come as my output
Sub IdentifyAccounts()
Application.ScreenUpdating = False
Dim varMainRange As Range
Dim varSubRange As Range
Set varMainRange = Range(Worksheets("HYPERION").Range("B20"), _
Worksheets("HYPERION").Range("B3000").End(xlUp))
For Each MainCell In varMainRange
Set varSubRange = Range(Worksheets("Accounts.Range("D9"), _
Worksheets("Accounts.Range("D3000").End(xlUp))
For Each SubCell In varSubRange
If MainCell.Value = SubCell.Value Then
MainCell.Offset(columnOffset:=4).Value = Worksheets("Accounts").Range("D9")
Exit For
End If
Next SubCell
Next MainCell
Application.ScreenUpdating = True
End Sub
Note:- Below statement is my result statement and it needed a change.
Worksheets("Accounts").Range("D9")
Your help on this code is much appreciated.
Thank you,
Thanks and Regards,
Sri
In the below code, It is returning "D9 cell data from Accounts Work sheet" in all cells from F20 to F3000.
End result should be as below
If B20 = any of (D9 to D3000), the cells adjacent to the matching cell (Lets suppose B20 = D20, then E20 should come as my output) should come as my output
Sub IdentifyAccounts()
Application.ScreenUpdating = False
Dim varMainRange As Range
Dim varSubRange As Range
Set varMainRange = Range(Worksheets("HYPERION").Range("B20"), _
Worksheets("HYPERION").Range("B3000").End(xlUp))
For Each MainCell In varMainRange
Set varSubRange = Range(Worksheets("Accounts.Range("D9"), _
Worksheets("Accounts.Range("D3000").End(xlUp))
For Each SubCell In varSubRange
If MainCell.Value = SubCell.Value Then
MainCell.Offset(columnOffset:=4).Value = Worksheets("Accounts").Range("D9")
Exit For
End If
Next SubCell
Next MainCell
Application.ScreenUpdating = True
End Sub
Note:- Below statement is my result statement and it needed a change.
Worksheets("Accounts").Range("D9")
Your help on this code is much appreciated.
Thank you,
Thanks and Regards,
Sri