I want to Update Data specific format

[Abhijeet]

Hi

I have 2 Sheet in Main Data Sheet Names are mention in Column A & Trust are mention in Row 1 i want to update in any Name & Trust Update in next sheet that is Refer Sheet then Update Status Column Value in Range C3:O8

Please tell me how to do this

 

[Marc L]

Hi !

To clarify, source date are in "Main Data" worksheet
and you want same result in "Refer Sheet" ?
​

 

[Abhijeet]

No Refer sheet is Source Data & Main Data i want result

 

[Marc L]

At beginner level using MATCH Excel worksheet function …​

Sub Demo()
  VA = Range("'Refer Sheet'!A1").CurrentRegion.Value
    With Range("'Main Data'!A1").CurrentRegion
        For L& = 2 To UBound(VA)
                VR = Application.Match(VA(L, 1), .Columns(1), 0)
            If IsNumeric(VR) Then
                VC = Application.Match(VA(L, 2), .Rows(1), 0)
                If IsNumeric(VC) Then .Cells(VR, VC).Value = VA(L, 3)
            End If
        Next
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Abhijeet]

Thanks Marc L its work perfect

 

[Marc L]

If you are sure all source data match in destination worksheet :​

Sub DemoNoControl()
  VA = Range("'Refer Sheet'!A1").CurrentRegion.Value
    With Range("'Main Data'!A1").CurrentRegion
        For L& = 2 To UBound(VA)
          .Cells(Application.Match(VA(L, 1), .Columns(1), 0), Application.Match(VA(L, 2), .Rows(1), 0)).Value = VA(L, 3)
        Next
    End With
End Sub

You may Like it !
​

 

[Abhijeet]

Please tell me what is difference in this code.
Both are work in this file i will check in my actual data if anything problem then i will let you know

 

[Marc L]

First code controls if data are matching (safer),
second not (faster) and in case of not matching, an error occurs …
​