# cade to raname values in next column

#### poker

##### Board Regular
column b contains the names of our berths while column A needs to contain the dock number.
so if column b contains:
S= column A=1
3=2
4=2
5=5
6=5
8=8
9=4
10=4
11=6
13=7
14=7

I would like to have this in vb code so I can include in a module and use on a reg basis

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Peter_SSs

##### MrExcel MVP, Moderator
column b contains the names of our berths while column A needs to contain the dock number.
so if column b contains:
S= column A=1
3=2
4=2
5=5
6=5
8=8
9=4
10=4
11=6
13=7
14=7

I would like to have this in vb code so I can include in a module and use on a reg basis
poker

I suspect no answer to this problem by now means that your requirements are not understood. Could you try to explain this more clearly?
What does S = column A=1 mean?
What does 3=2 mean?
etc

#### poker

##### Board Regular
dock berth
1 2S1
2 3
2 4
5 5
5 6
8 8
4 9
4 10
6 11
7 13
7 14
if any of the cells in column b contain a S column A would contain a 1 (same row), if any row in column b contains a 3 or a 4, column a would = 2 (same row)
if any row in column b contains a 5 or a 6 column a would =5 (same row)
and so on. What this is going to do is link the berth number to the correct dock number

#### stanleydgromjr

##### Banned
poker,

I now understand what you are trying to do.

If any cell in column 'B' contains an 'S', column 'A' (same row number) would = 1.

If any cell in column 'B' contains a 3 or a 4, column 'A' (same row number) would = 2.

If any cell in column 'B' contains a 5 or a 6, column 'A' (same row number) would = 5.

What are the rest of the test scenarios - "and so on"?

I am using Windows XP Professional SP2 and Excel 2003 SP2.

Have a great day,
Stan

#### pgc01

##### MrExcel MVP

Hi poker

If I understand the problem you just need a vlookup.

In this example I wrote your lookup table in E2:F11. I changed the order of the columns because Vlookup always searches the first column.

Then, in A2:

Code:
``=VLOOKUP(B2,\$E\$2:\$F\$12,2,FALSE)``
Copy down

If you give an invalid berth, the corresponding dock will display an error (like when I gave berth the value 12, that doesn't exist).

Is this what you want?
PGC
Book1
ABCDEFGH
1DockBerthBerthDock
223S1
35532
45642
58855
671365
761188
871494
91S104
10611116
1124137
1249147
1349
14#N/A12
1523
16713
1724
1824
1988
2049
21
22
Sheet2

#### seeblue

##### Board Regular
any cell in column 'B' contains a 9 or a 10, column 'A' (same row number) would = 4.
any cell in column 'B' contains a 13 or a 14, column 'A' (same row number) would = 7.
any cell in column 'B' contains a 8, column 'A' (same row number) would = 8.
any cell in column 'B' contains a 11, column 'A' (same row number) would = 6.

#### stanleydgromjr

##### Banned
poker,
I have created a function, 'RenameValuesInNextColumn'
Copy the below code:

'----------Code Begins Here------------------------------------------
Private Function RenameValuesInNextColumn(InString) As String
'
' RenameValuesInNextColumn Function
' Function created 9/19/2006 by Stanley D. Grom, Jr.
'
' dock berth
' 1 S
' 1 2S1
' 2 3
' 2 4
' 5 5
' 5 6
' 8 8
' 4 9
' 4 10
' 6 11
' 7 13
' 7 14
'
CheckStringCHAR = ""
StringLength = Len(InString)
InStringHold = ""

For i = 1 To StringLength
If Mid(InString, i, 1) = "s" Then
InStringHold = "s"
InString = InStringHold
Exit For
ElseIf Mid(InString, i, 1) = "S" Then
InStringHold = "S"
InString = InStringHold
Exit For
End If
Next i

Select Case InString
Case "3"
FoundCHAR = "2"
Case "4"
FoundCHAR = "2"
Case "5"
FoundCHAR = "5"
Case "6"
FoundCHAR = "5"
Case "8"
FoundCHAR = "8"
Case "9"
FoundCHAR = "4"
Case "10"
FoundCHAR = "4"
Case "11"
FoundCHAR = "6"
Case "13"
FoundCHAR = "7"
Case "14"
FoundCHAR = "7"
Case "s"
FoundCHAR = "1"
Case "S"
FoundCHAR = "1"
Case Else
End Select

RenameValuesInNextColumn = FoundCHAR

End Function
'----------Code Ends Here--------------------------------------------

Paste the above code into a 'Module'.

Copy the next formula (without the leading ' ) into cell 'A2'.
=RenameValuesInNextColumn(B2)

Copy cell 'A2' down to the last row of information in column 'B'.

I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan

Replies
13
Views
156
Replies
9
Views
312
Replies
3
Views
103
Replies
3
Views
140
Replies
1
Views
88

### Forum statistics

1,141,227
Messages
5,705,141
Members
421,380
Latest member
Nuwan Sanjeewa Aponso ### 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