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

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
Joined
May 28, 2005
Messages
50,585
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 2, 2005
Messages
74
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
 
Joined
Jul 30, 2006
Messages
3,656
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
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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
Joined
Jan 23, 2005
Messages
178
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.
 
Joined
Jul 30, 2006
Messages
3,656
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
 

Forum statistics

Threads
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.
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
Top