exceluser9
Active Member
- Joined
- Jun 27, 2015
- Messages
- 388
Hi Team,
I have a data in column A, B, C and D and I have written VBA code to pick results in column G,H and I and I get the data but in B6, G6, D6, B8, C8 and D8 isnt with data and the same hasnt appeared in G6, H6, I6, G8, G8 and I8 and when i input the data in B6, G6, D6, B8, C8 and D8 and run the macro for second time it has to run only for blank cells and it has to skip the cells which is already with data and its has to happen whenever i run it, it should fill only blank cells and not the rest. VBA code below. Please could you help
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet1").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C1:C4,2,0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],C1:C4,3,0)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],C1:C4,4,0)"
LRN = Range("F" & Rows.Count).End(xlUp).Row
Selection.End(xlDown).Select
Range("G2:G" & LRN).Select
Range(Selection, Selection.End(xlUp)).Select
Range("G2:G" & LRN).Select
Selection.FillDown
Range("G2:I" & LRN).Select
Selection.End(xlUp).Select
Range("G2:I" & LRN).Select
Range(Selection, Selection.End(xlUp)).Select
Range("G2:I" & LRN).Select
Selection.FillDown
Range("G2:I" & LRN).Select
Selection.End(xlUp).Select
Range("J2").Select
End Sub
[TABLE="width: 669"]
<tbody>[TR]
[TD] [/TD]
[TD]DATA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]RESULT[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[TD]COULMN C[/TD]
[TD]COLUMN D[/TD]
[TD][/TD]
[TD]COLUMN F[/TD]
[TD]COLUMN G[/TD]
[TD]COULMN H[/TD]
[TD]COLUMN I[/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]NUMBER[/TD]
[TD]AGE[/TD]
[TD]RANK[/TD]
[TD][/TD]
[TD]NAME[/TD]
[TD]NUMBER[/TD]
[TD]AGE[/TD]
[TD]RANK[/TD]
[/TR]
[TR]
[TD]DAVID[/TD]
[TD]123[/TD]
[TD]10[/TD]
[TD]GOOD[/TD]
[TD][/TD]
[TD]DAVID[/TD]
[TD]123[/TD]
[TD]10[/TD]
[TD]GOOD[/TD]
[/TR]
[TR]
[TD]ALWIN[/TD]
[TD]6767[/TD]
[TD]11[/TD]
[TD]POOR[/TD]
[TD][/TD]
[TD]ALWIN[/TD]
[TD]6767[/TD]
[TD]11[/TD]
[TD]POOR[/TD]
[/TR]
[TR]
[TD]GEORGE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]GEORGE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ALFRED[/TD]
[TD]767[/TD]
[TD]13[/TD]
[TD]BAD[/TD]
[TD][/TD]
[TD]ALFRED[/TD]
[TD]767[/TD]
[TD]13[/TD]
[TD]BAD[/TD]
[/TR]
[TR]
[TD]WILSON[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]WILSON[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
I have a data in column A, B, C and D and I have written VBA code to pick results in column G,H and I and I get the data but in B6, G6, D6, B8, C8 and D8 isnt with data and the same hasnt appeared in G6, H6, I6, G8, G8 and I8 and when i input the data in B6, G6, D6, B8, C8 and D8 and run the macro for second time it has to run only for blank cells and it has to skip the cells which is already with data and its has to happen whenever i run it, it should fill only blank cells and not the rest. VBA code below. Please could you help
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet1").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C1:C4,2,0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],C1:C4,3,0)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],C1:C4,4,0)"
LRN = Range("F" & Rows.Count).End(xlUp).Row
Selection.End(xlDown).Select
Range("G2:G" & LRN).Select
Range(Selection, Selection.End(xlUp)).Select
Range("G2:G" & LRN).Select
Selection.FillDown
Range("G2:I" & LRN).Select
Selection.End(xlUp).Select
Range("G2:I" & LRN).Select
Range(Selection, Selection.End(xlUp)).Select
Range("G2:I" & LRN).Select
Selection.FillDown
Range("G2:I" & LRN).Select
Selection.End(xlUp).Select
Range("J2").Select
End Sub
[TABLE="width: 669"]
<tbody>[TR]
[TD] [/TD]
[TD]DATA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]RESULT[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[TD]COULMN C[/TD]
[TD]COLUMN D[/TD]
[TD][/TD]
[TD]COLUMN F[/TD]
[TD]COLUMN G[/TD]
[TD]COULMN H[/TD]
[TD]COLUMN I[/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]NUMBER[/TD]
[TD]AGE[/TD]
[TD]RANK[/TD]
[TD][/TD]
[TD]NAME[/TD]
[TD]NUMBER[/TD]
[TD]AGE[/TD]
[TD]RANK[/TD]
[/TR]
[TR]
[TD]DAVID[/TD]
[TD]123[/TD]
[TD]10[/TD]
[TD]GOOD[/TD]
[TD][/TD]
[TD]DAVID[/TD]
[TD]123[/TD]
[TD]10[/TD]
[TD]GOOD[/TD]
[/TR]
[TR]
[TD]ALWIN[/TD]
[TD]6767[/TD]
[TD]11[/TD]
[TD]POOR[/TD]
[TD][/TD]
[TD]ALWIN[/TD]
[TD]6767[/TD]
[TD]11[/TD]
[TD]POOR[/TD]
[/TR]
[TR]
[TD]GEORGE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]GEORGE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ALFRED[/TD]
[TD]767[/TD]
[TD]13[/TD]
[TD]BAD[/TD]
[TD][/TD]
[TD]ALFRED[/TD]
[TD]767[/TD]
[TD]13[/TD]
[TD]BAD[/TD]
[/TR]
[TR]
[TD]WILSON[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]WILSON[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]