find replace question

[edojan]

Hi

I have 2 excel sheets,

sheet 1 col:
id, paid amt
sheet 2 col:
is, paid amt

What i need is like if id on sheet1 = id on sheet 2 and paid amt on sheet 2 is null then replace paid amt with paid amt on sheet 1. Whats the code?

Thanks!

 

[dave3009]

Hi

Where does the paid amt come from?

 

[schielrn]

Based on these values being in column A and B, starting in row 2 and id is completely unique try:

Sub test()
Dim lastrow1 As Integer, lastrow2 As Integer

lastrow1 = ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
lastrow2 = ThisWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row

For a = 2 To lastrow1
For b = 2 To lastrow1
If (Sheets(1).Cells(a, 1).Value = Sheets(2).Cells(b, 1).Value) And Sheets(2).Cells(b, 2).Value = "" Then
Sheets(2).Cells(b, 2).Value = Sheets(1).Cells(a, 2).Value
Exit For
End If
Next b
Next a
End Sub

 

[Peter_SSs]

Here is another code that may suit.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> PaidAmt()
    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws2 = Worksheets("Sheet 2")
    lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
    <SPAN style="color:#00007F">With</SPAN> ws2.Range("B1:B" & lr)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IF(ISNUMBER(MATCH(RC[-1],'Sheet 1'!C1,0)),VLOOKUP(RC[-1],'Sheet 1'!C1:C2,2,0),"""")"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[edojan]

thanks guys they all helped out alot!