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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

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

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

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

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.

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
10
Views
315
Replies
9
Views
252
Replies
8
Views
229
Replies
5
Views
270
Replies
5
Views
297

1,219,800
Messages
6,150,322
Members
450,951
Latest member
kh198

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.

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