Hi All,
I want macro to Autofill the data based on Criteria. I have two sheets.
Sheet1:
ID NAME
01 Roy
02 John
03 Wesly
Sheet2:
ID Name Status
ROY P
ROY O
ROY S
John S
John O
Wesly P
Sheet3:
I want Auto fill thier ID in column A
ID Name Status
01 ROY P
01 ROY O
01 ROY S
02 John S
02 John O
03 Wesly P
Regards,
kiruthika
Re: Auto fill data based on Criteria
There's no common field to link the lists and you haven't explained how/why the various Roys (for example) all get '01' as the status.
Re: Auto fill data based on Criteria
Quote from cytop;760261There's no common field to link the lists and you haven't explained how/why the various Roys (for example) all get '01' as the status.
Hi,
I want to fill id based on name .
Regards,
Keerthi
Re: Auto fill data based on Criteria
You're simply repeating what you said in your first post...
Explain the logic. 'Roy P' is not the same as 'Roy O'. I'm not going to make the assumption that anything starting 'Roy' gets the ID '01' - it's down to you to explain.
Re: Auto fill data based on Criteria
Quote from cytop;760271You're simply repeating what you said in your first post...
Explain the logic. 'Roy P' is not the same as 'Roy O'. I'm not going to make the assumption that anything starting 'Roy' gets the ID '01' - it's down to you to explain.
Yes, I want fill where ever Roy comes fill the ID 01
Re: Auto fill data based on Criteria
Hi keerthi deva
Try
Option Explicit
Sub Test_Macro()
Dim MaxRowMatch As Long
Dim MaxRowIndex As Long
MaxRowIndex = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet2")
MaxRowMatch = .Range("b" & Rows.Count).End(xlUp).Row
.Cells.Copy Sheets("Sheet3").Range("A1")
End With
With Sheets("Sheet3").Range("A2:A" & MaxRowMatch)
.FormulaR1C1 = _
"=INDEX(Sheet1!R2C:R" & MaxRowIndex & "C,MATCH(Sheet3!RC[1],Sheet1!R2C[1]:R" & MaxRowIndex & "C[1],0),)"
.Value = .Value
End With
End SubDon’t have an account yet? Register yourself now and be a part of our community!