(vba) replacing values.

montecarlo2012

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
984
Office Version
  1. 2010
Platform
  1. Windows
Hello, I am not pretend to be a programmer I just try tospeak your vba language, so maybe I help a little more.


For something
If (O.value = array A:F.values)
Then H:M =P:U.values
Next
Ubound
End
A2:F2 corresponding to P values, A3:F3 to Q, and so on.
In the spreadsheet you can see on H1 value 1 because A2=9and O11=9 then replace for P.value next to O11.
Where is my array? B2:G2060
And the pu.values at P2:AMA1000
DISPLAY RESULTS AT (“i:N”)

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
1
2
9
12
15
21
33
45
1
1
4
1
0
1
1
2
2
2
2
2
2
3
20
25
29
30
36
37
2
2
0
0
3
4
2
3
3
2
2
2
2
4
2
11
13
20
27
28
3
3
3
3
4
4
5
5
4
6
15
24
47
52
4
2
2
2
1
1
0
6
10
16
21
25
37
49
5
1
1
1
1
1
0
7
4
5
8
13
27
44
6
1
2
2
1
1
2
8
2
3
15
39
44
49
7
1
1
1
1
1
1
9
19
23
28
38
48
53
8
3
3
3
3
3
2
10
3
8
16
20
43
48
9
1
1
1
2
2
2
11
13
16
17
40
42
44
10
2
2
3
3
2
2
12
1
12
22
44
46
52
11
1
1
1
2
3
3
13
10
13
35
36
37
42
12
1
1
2
2
2
2
14
9
26
34
40
42
49
13
4
4
3
3
3
2
15
1
2
25
43
48
53
14
1
2
2
2
2
2
16
8
32
35
46
47
52
15
4
4
4
3
3
3
17
14
15
23
36
49
50
16
4
4
4
4
3
3
18
15
17
22
40
41
45
17
2
2
3
3
3
3
19
3
26
36
37
43
44
18
0
0
0
0
0
0
20
7
16
31
38
39
42
19
1
1
1
1
1
1
21
6
14
37
41
48
49
20
3
2
1
1
1
1
22
10
11
12
17
28
46
21
1
1
1
2
1
1
23
3
9
11
21
24
49
22
2
2
2
2
2
2
24
11
24
36
45
46
47
23
2
2
2
2
2
2
25
3
6
36
46
48
52
24
1
1
1
1
2
2
26
2
7
11
15
43
45
25
3
2
2
2
1
1
27
3
6
16
30
31
40
26
2
2
2
2
2
2
28
20
44
46
48
52
53
27
2
2
1
1
1
0
29
29
33
46
48
49
53
28
2
2
2
2
2
2
30
3
4
5
39
47
48
29
1
0
0
0
0
0
31
4
8
17
20
51
52
30
1
0
0
0
0
0
32
6
10
18
27
28
51
31
1
1
1
1
1
1
33
4
8
15
16
32
52
32
1
1
1
1
1
1
34
8
21
22
27
40
44
33
0
0
0
0
0
0
35
1
3
4
12
15
38
34
1
1
1
1
1
1
36
2
10
11
41
42
53
35
2
2
2
2
2
2
37
13
17
20
23
24
30
36
4
3
3
3
4
5
38
1
7
35
39
43
47
37
4
4
4
4
3
3
39
3
13
42
47
52
53
38
2
2
2
2
2
2
40
1
3
5
14
28
32
39
2
2
2
2
2
2
41
5
13
18
28
31
43
40
3
3
3
3
3
3
42
2
7
31
40
46
52
41
1
2
2
2
2
2
43
5
8
9
12
23
27
42
4
4
4
4
4
4
44
3
16
19
20
32
52
43
3
3
3
3
3
3
45
2
29
35
37
41
42
44
5
5
5
5
5
4
46
21
24
34
41
47
52
45
1
1
1
1
2
2
47
13
19
22
44
46
52
46
2
2
3
3
4
5
48
5
15
29
35
40
47
47
2
2
2
1
2
2
49
4
36
42
47
48
52
48
3
4
4
4
4
5
50
1
21
26
30
46
48
49
4
5
5
6
5
5
51
21
24
33
37
43
46
50
1
1
1
1
1
1
52
1
11
19
33
37
49
51
0
0
0
0
0
0
53
5
6
13
33
35
40
52
3
3
3
2
2
3
54
20
23
26
32
41
46
53
2
2
2
2
2
2
thanks.
 
Last edited by a moderator:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
montecarlo2012,

Sample raw data:

Book1
ABCDEFGHIJKLMNOPQRSTU
1
2912152133451222222
32025293036372332222
4211132027283333445
546152447524222110
61016212537495111110
74581327446122112
823153944497111111
91923283848538333332
1038162043489111222
1113161740424410223322
121122244465211111233
1310133536374212112222
149263440424913443332
15122543485314122222
168323546475215444333
1714152336495016444433
1815172240414517223333
193263637434418000000
207163138394219111111
216143741484920321111
2210111217284621111211
23391121244922222222
2411243645464723222222
25363646485224111122
26271115434525322211
27361630314026222222
2820444648525327221110
2929334648495328222222
3034539474829100000
31481720515230100000
326101827285131111111
33481516325232111111
348212227404433000000
3513412153834111111
362101141425335222222
3713172023243036433345
38173539434737444433
393134247525338222222
4013514283239222222
415131828314340333333
42273140465241122222
4358912232742444444
443161920325243333333
452293537414244555554
4621243441475245111122
4713192244465246223345
485152935404747222122
494364247485248344445
501212630464849455655
5121243337434650111111
521111933374951000000
53561333354052333223
5420232632414653222222
Sheet1

After the macro:

Book1
ABCDEFGHIJKLMNOPQRSTU
1
2912152133451142021222222
32025293036373200432332222
4211132027283131123333445
546152447522241234222110
61016212537492412355111110
74581327442133146122112
823153944493342557111111
91923283848531222428333332
1038162043483341359111222
1113161740424444334410223322
121122244465221254311111233
1310133536374224233412112222
149263440424912134513443332
15122543485323234214122222
168323546475231232315444333
1714152336495014235116444433
1815172240414542232217223333
193263637434432343418000000
207163138394214122419111111
216143741484912424520321111
2210111217284621232521111211
23391121244931122522222222
2411243645464711314223222222
25363646485232334324111122
26271115434531133225322211
27361630314032401326222222
2820444648525335342227221110
2929334648495310345228222222
3034539474832122529100000
31481720515223310330100000
326101827285112012031111111
33481516325223441332111111
348212227404431213433000000
3513412153823223234111111
362101141425332124235222222
3713172023243042122036433345
38173539434721223237444433
393134247525334412238222222
4013514283223122139222222
415131828314314021340333333
42273140465231134341122222
4358912232713122042444444
443161920325234111343333333
452293537414230242444555554
4621243441475211122345111122
4713192244465241254346223345
485152935404714023247222122
494364247485223414348344445
501212630464821204549455655
5121243337434611043550111111
521111933374921103551000000
53561333354012302352333223
5420232632414632212553222222
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

VBA Code: 
Option Explicit
Sub FindMy_HM()
' hiker95, 11/16/2013
' http://www.mrexcel.com/forum/excel-questions/739804-visual-basic-applications-replacing-values.html
Dim a As Variant, h As Variant
Dim i As Long, c As Long
Dim frrng As Range
a = Range("A1:F" & Range("A" & Rows.Count).End(xlUp).Row)
ReDim h(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 2 To UBound(a, 1)
  For c = 1 To UBound(a, 2)
    Set frrng = Columns(15).Find(Cells(i, c))
    h(i, c) = Cells(frrng.Row, frrng.Column + c)
  Next c
Next i
Range("H1").Resize(UBound(h, 1), UBound(h, 2)) = h
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindMy_HM macro.
 
Last edited by a moderator:
Upvote 0
Solution
Do you really need to use VBA code? I ask because the output that hike95's code puts out can be obtained using a relatively simple formula. Put this formula in cell H2 and copy it across to cell M2, then copy the cells H2:M2 down to the bottom of your data...

=INDEX($P$2:$U$54,A2,COLUMN(A1))

If you really need to insert your values using VB code, then using the above formula allows us to create this short to do it (probably slower than hiker95's code, but for the amount of data you have, probably not noticeably so)...

Code: 
Sub DoIt()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Range("H2:M" & LastRow)
    .Formula = "=INDEX($P$2:$U$" & LastRow & ",A2,COLUMN(A1))"
    .Value = .Value
  End With
End Sub
 
Upvote 0
Hello Rick Rothstein, thank you for answer.
Since I received the code I start to work on it, and Ithink “I didn’t explain enough”.
Because: the road (“A2:F2”) is the numbers9-12-15-21-33-45 so the code go to column (“P”)and swap the values “only in P”for this row, so the results in (“H2:M2”) must be iqual to the values in P and only in P for the this first row (1-1-4-1-0-1).
Now for the row (“A3:F3”) swap for the values in Q and only Q;
For the row (“A4:F4”) swap for the values in R and only in R and so on.
My problem here is I can provided all the columns, I was thinking as illustration.
I provided just the P:T information enough to cover theH:M outcome for fast communication purpose.
Sorry I react so quickly to say something; by the wayguys, the results in P:T I just made it manually, I mean I write down for everycolumn the function {=frequency( and so on)}, my data is, if I go 18 by 18every time will be like 2049 lines, you don’t have to respond this of course,but sound like I need something else, sorry I am just thinking loud.
Thank you Rick Rothstein.
Thank you Hicker95.
I really appreciate your answers.
 
Last edited by a moderator:
Upvote 0
montecarlo2012,

Did you even try my macro?

If the macro did not do what you require, then can we have your raw data workbook?

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Thank you Hicker95 I will be doing that right now, thank you. I really tried .
 
Upvote 0
I sing up already for Box Net, and putting in share for HICKER95RICKBOX.xls
 
Upvote 0
montecarlo2012 said:
I sing up already for Box Net, and putting in share for HICKER95RICKBOX.xls
Click to expand...

When you upload a file for sharing on box.net, they give you a URL for your file... you need to post that URL back here so we can retrieve the copy of your file.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Xlookup with multiple conditions
Replies
5
Views
228
Cubist
Cubist
J
  • Question
Removing blank rows based on cell value
Replies
2
Views
208
Peter_SSs
Peter_SSs
tabbytomo
  • Question
=IF Exact formula stops working part way through
Replies
1
Views
218
arthurbr
arthurbr
J
  • Solved
#N/A Error
2
Replies
14
Views
311
Jedi Master
J
V
  • Solved
Week number function
Replies
2
Views
164
vmjan02
V

Forum statistics

Threads
1,221,497
Messages
6,160,152
Members
451,625
Latest member
sukhman

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top