# Find and Replace?

#### jmr0036

##### New Member
I just started trying to use VBA and have run into a problem that I haven't been able to work through.

Lets say in column A I have values 1-20 and in column B there are random numbers. In range E1:H8, the numbers from column A are located randomly. Would it be possible to replace the numbers in range E1:H8 with their corresponding numbers in column B?

 A B C D E F G H 1 49 1 3 2 4 2 48 8 7 5 6 3 47 9 10 11 12 4 46 13 17 14 5 45 15 16 6 44 18 7 43 19 8 42 20 9 41 10 40 11 32 12 54 13 26 14 12 15 57 16 38 17 69 18 24 19 27 20 38

<tbody>
</tbody>

Any help would be much appreciated!

Last edited:

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Code:
``````for i = 1 to 20
currentvalue = cells(i,2)' this is the value in b
for b = 5 to 8
if(cells(i,b) <> "") then
cells(i,b) = currentvalue
end if
next b
next i``````

hopefully this is what I understood from your question.

This code will do what you asked...

Code:
``````Sub ReplaceAwithB()
Dim X As Long
For X = 1 To 20
Range("E1:H8").Replace Cells(X, "A").Value, Cells(X, "A").Offset(, 1).Value, xlWhole
Next
End Sub``````

Thanks for your response. That is close to what I was asking but not quite. I don't know how better to explain it but I guess pictures might help.

The code you gave produced this:
 E F G H 49 49 49 49 48 48 48 48 47 47 47 47 46 46 46 45 45 44 43 42

<colgroup><col width="64" span="4" style="width: 48pt; "></colgroup><tbody>
</tbody>

But I was trying to produce this:

 A B C D E F G H 1 49 49 47 48 46 2 48 42 43 45 44 3 47 41 40 32 54 4 46 26 69 12 5 45 57 38 6 44 24 7 43 27 8 42 38 9 41 10 40 11 32 12 54 13 26 14 12 15 57 16 38 17 69 18 24 19 27 20 38

<colgroup><col width="64" span="8" style="width: 48pt; "></colgroup><tbody>
</tbody>

This code will do what you asked...

Code:
``````Sub ReplaceAwithB()
Dim X As Long
For X = 1 To 20
Range("E1:H8").Replace Cells(X, "A").Value, Cells(X, "A").Offset(, 1).Value, xlWhole
Next
End Sub``````

It worked perfectly. Thanks for your help!

Ok this is weird. It worked perfectly with the small data set but when I tried using it with my full set it seemed to replace the numbers at random.

In order to account for the larger data set I changed the x value from 1to 20 to 1 to 574 and adjusted the range accordingly.
Sub ReplaceAwithB()
Dim X As Long
For X = 1 To 574
Range("e2:af29").Replace Cells(X, "a").Value, Cells(X, "a").Offset(, 1).Value, xlWhole
Next
End Sub

Any ideas?

Ok this is weird. It worked perfectly with the small data set but when I tried using it with my full set it seemed to replace the numbers at random.

In order to account for the larger data set I changed the x value from 1to 20 to 1 to 574 and adjusted the range accordingly.
Sub ReplaceAwithB()
Dim X As Long
For X = 1 To 574
Range("e2:af29").Replace Cells(X, "a").Value, Cells(X, "a").Offset(, 1).Value, xlWhole
Next
End Sub

Any ideas?

I'll be stepping out for a few hours, so I won't be able to get back to this question for awhile, but what I think may be happening is, given the way I set things up, the expanded set is allowing numbers that were substituted with a smaller value to be substituted again from a later number in the list... maybe. That's an off-the-top-of-my-head guess... as I said, I'll look at this when I get back.

Code:
``````Sub repl()
Dim c As Range
For Each c In Range("E1:H20")
If Len(c) > 0 Then c.Value = Cells(c.Value, 2)
Next
End Sub``````

I'll be stepping out for a few hours, so I won't be able to get back to this question for awhile, but what I think may be happening is, given the way I set things up, the expanded set is allowing numbers that were substituted with a smaller value to be substituted again from a later number in the list... maybe. That's an off-the-top-of-my-head guess... as I said, I'll look at this when I get back.

Actually, I have looked at what I posted and I do not see why it should not be working for you. My earlier guess (that perhaps it was re-substituting values) cannot happen because I specified xlWhole for the replacements, so smaller numbers cannot be being substituted because only whole cell values are looked at. Can you provide more details as to what you are seeing my function do incorrectly? Better would be if you could upload a copy of your workbook to one of the free file-sharing website so I (and others) can look directly at your data. If you have a SkyDrive account, use that, otherwise http://www.box.net/files works well (don't forget to post the URL they give you for your file). If you are reluctant to do that, you could send your workbook directly to me to look at. My email address is rickDOTnewsAtverizonDOTnet (replace the upper case letters with the symbols they spell out).

Replies
8
Views
766
Replies
15
Views
545
Replies
12
Views
778
Replies
18
Views
775
Replies
10
Views
515

1,196,502
Messages
6,015,585
Members
441,902
Latest member
alhaste

### 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