# find replace question

#### edojan

##### New Member
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!

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### dave3009

##### Well-known Member
Hi

Where does the paid amt come from?

#### schielrn

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

Code:
``````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

##### MrExcel MVP, Moderator
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

##### New Member
thanks guys they all helped out alot!

Replies
4
Views
369
Replies
1
Views
372
Replies
12
Views
756
Replies
3
Views
621
Replies
10
Views
592

1,190,898
Messages
5,983,451
Members
439,843
Latest member
PlanetFitness

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back