cade to raname values in next column

poker

Board Regular
Joined
Oct 2, 2005
Messages
74
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
FoundCHAR = "Not Found"
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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