PDA

View Full Version : [SOLVED] Macro code for matching and replacing the values



Dharani
09-25-2013, 08:54 PM
Dear Sir /Madam,
In the given file, where I have to match between column A and F. in F column few values are missing. According to A col, the cols F,G,H,I,J,K,L has to replaced in matching with A col. For easy understanding , I have did manually in the 2 sheet (required output sheet) for your kind reference.
please do help me... like this I have to do for 10 more files...

I need very urgently...please oblige and do the needful...
please find the attachment...

Thanks & Regards,
Dharani.

Kenneth Hobs
09-26-2013, 12:54 AM
When testing code, be sure to test on a backup copy.

Put this in a Module.

Option Explicit

' Find help: http://msdn.microsoft.com/en-us/library/office/ff839746.aspx
Sub FillOut()
Dim iWS As Worksheet, oWS As Worksheet
Dim iRR As Range, oRR As Range, f As Range, c As Range
Dim i As Integer

Set iWS = Worksheets("input sheet")
Set oWS = Worksheets("required output")

Set iRR = iWS.Range("F2", iWS.Range("F" & Rows.Count).End(xlUp))
Set oRR = oWS.Range("A2", oWS.Range("A" & Rows.Count).End(xlUp))

For Each c In oRR
Set f = iRR.Find(c.Value, , LookIn:=xlValues, SearchDirection:=xlNext)
If f Is Nothing Or Len(c.Value) = 0 Then GoTo nextC
For i = 6 To 12
oWS.Cells(c.Row, i).Value = iWS.Cells(f.Row, i).Value
Next i

nextC:
Next c
End Sub

patel
09-26-2013, 02:19 AM
Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A2:A" & LR)
r = 2
Do While r < LR
Pot = Range("F" & r)
Set c = Rng.Find(Pot, LookIn:=xlValues)
If Not c Is Nothing Then
If c.Row > r Then
Range("F" & r & ":L" & c.Row - 1).Insert xlDown
r = c.Row
End If
End If
r = r + 1
Loop
End Sub

Dharani
09-26-2013, 08:38 PM
Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A2:A" & LR)
End If
End If
r = r + 1
Loop
End Sub


Dear Sir,
Thank you for the quick and honest effort you made.. the code is working... my heartfelt thanks to you...
Thank you so much sir for spending your valuable time...

Regards,
Dharani.

Dharani
09-26-2013, 08:39 PM
[QUOTE=Kenneth Hobs;297764]When testing code, be sure to test on a backup copy.

Put this in a Module.

Option Explicit

' Find help: http://msdn.microsoft.com/en-us/library/office/ff839746.aspx
Sub FillOut()
Dim iWS As Worksheet, oWS As Worksheet
Dim iRR As Range, oRR As Range, f As Range, c As Range

Next c
End Sub


Dear Sir,
Thank you for the quick and honest effort you made..
Thank you so much sir for spending your valuable time...

Regards,
Dharani.