# 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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### 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
7
Views
69
Replies
3
Views
59
Replies
4
Views
68
Replies
3
Views
81
Replies
0
Views
25