VBA CODE- Match 2 cells and display the result

[murthysri272]

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

[p45cal]

then E20 should come as my output) should come as my output What should go into E20?

meanwhile a complete guess:
Replace
MainCell.Offset(columnOffset:=4).Value = Worksheets("Accounts").Range("D9")
with
MainCell.Offset(, 3).Value = SubCell.Value

[snb]

I don't think you live in Illinois.
I don't think you ever took a course on VBA.
I even doubt if you ever used one of Excel's formulae.
I'd suggest to learn a language (whether Excel or VBA) before using it.

[murthysri272]

What should go into E20?

meanwhile a complete guess:
Replace
MainCell.Offset(columnOffset:=4).Value = Worksheets("Accounts").Range("D9")
with
MainCell.Offset(, 3).Value = SubCell.Value

Hi,

Thanks for your response. Your guess is not helping the scenario. Please find below explanation about my scenario:



If B20 from worksheet HYPERION is found in Accounts worksheet of cell D25, the E25 cell from Accounts sheet should write the result in E20 cell of Hyperion sheet.

I hope you understand the scenario.


Thanks and Regards,
Sri

[p45cal]

Set varMainRange = Range(Worksheets("HYPERION").Range("B20"), Worksheets("HYPERION").Range("B3000").End(xlUp))
Set varSubRange = Range(Worksheets("Accounts").Range("D9"), Worksheets("Accounts").Range("D3000").End(xlUp))

For Each MainCell In varMainRange
For Each SubCell In varSubRange
If MainCell.Value = SubCell.Value Then
MainCell.Offset(, 3).Value = SubCell.Offset(, 1).Value
Exit For
End If
Next SubCell
Next MainCell

[murthysri272]

Hi,
It worked. Thanks for your help.


Thank you.


Regards,
Sri

[murthysri272]

Hi,
I working on the same case in a different way. Could you please help me with the sample code for below scenario:


I have Only one sheet name (HYP).
In that I have 4 columns column1, column2, column3, Column4.

Lookup each value of column1 in Column2.

If Column1 Cell 1 is matching with Column2 Cell 4 then adjacent value in Column3 Cell 4 (corresponds to column2 cell 4) should return on column4 Cell1.



I hope you understand the scenario.


Thanks for your help.

Regards,
Sri

[p45cal]

Formulae in column header column4 in attached.

[murthysri272]

Hi,
I am looking for VBA code for this scenario.


Regards,
Sri

[p45cal]

Run this in the file I provided in msg#8:
Sub blah()
With Range("G2:G22")
.FormulaR1C1 = "=VLOOKUP(RC[-3],R2C5:R22C6,2,FALSE)"
.Value = .Value
End With
End Sub