Find and Replace?

[jmr0036]

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!

 

[jc.021286]

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.

 

[Rick Rothstein]

This code will do what you asked...

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

 

[jmr0036]

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>

 

[jmr0036]

This code will do what you asked...

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!

 

[jmr0036]

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?

 

[Rick Rothstein]

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.

 

[mirabeau]

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

 

[Rick Rothstein]

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).