Problems with Select Case Matching

[Oberon70]

For some reason my case select is not matching with the variable. I only have

Sub CrtWorkFlw()

Dim Lastrow As Double
Dim AgtName As String
Dim InvNum As String

Lastrow = wsSupSheet.Cells(Rows.Count, "A").End(xlUp).Row

'AgtName = Trim(wsInvDtls.Range("B3").Value)
'InvNum = wsInvDtls.Range("B1").Value

AgtName = Trim(wsInvDtls.Range("B7").Value)
InvNum = wsInvDtls.Range("B8").Value

Select Case AgtName
    Case AgtName = "Wayne Tech"
        AgtName = "WayneTech"
    Case AgtName = "Lex Corp"
        AgtName = "LexCorp"
End Select

wsSupSheet.Range("A" & Lastrow & ":" & "C" & Lastrow).Offset(1).Merge
wsSupSheet.Range("A" & Lastrow & ":" & "C" & Lastrow).Offset(1) = (AgtName & " " & InvNum)
             
Debug.Print AgtName

End Sub

Any idea why?

 

[Fluff]

What exactly is the value of AgtName?

 

[RoryA]

Your Case statements are wrong. They should be:

Select Case AgtName
    Case "Wayne Tech"
        AgtName = "WayneTech"
    Case "Lex Corp"
        AgtName = "LexCorp"
End Select

although it looks like you could just replace spaces and skip the Select Case altogether.

 

[Oberon70]

The agent name is taken from another worksheet, where the name is taken from a statement.

 

[Oberon70]

Thank you

 

[Fluff]

the name is taken from a statement

In that case you may non-breaking spaces rather than a normal space. What does =CODE(MID(B7,6,1)) return?

 

[Oberon70]

It is working now with the change to

Select Case AgtName
    Case "Wayne Tech"
        AgtName = "WayneTech"
    Case "Lex Corp"
        AgtName = "LexCorp"
End Select

but I have put the code

=CODE(MID(B7,6,1))

and it has returned 32

 

[Fluff]

32 is correct, I had missed the fact that you had Case AgtName = "Wayne Tech" rather than Case "Wayne Tech"

 

[Oberon70]

All good, I really appreciate everyone's help. I had one of those Eureka moments today that is going to likely help me get caught up on receipting for my work.

The main system I use allows you to paste into it. There are three sections to receipting.

1. T Screen - which is where I can enter a max of 16 funds paid.
2. Transactions Screens - where you can enter 6 transactions
3. Final Screen, which is were you enter the opposite of the transactions you entered before. This screen has a max of 12 entries.

I manually entered the information into an excel spreadsheet and figured out the way the information needs to be spaced in the spreadsheet. I am then able to copy and paste the information into the program.

It basically means in about 10 key presses I have receipted 16 entries in probably about 10-20 seconds instead of about 10 minutes.

So, I am working hard to pull the information from a statement and and enter it onto a new worksheet in the format I found that works.

The next part is going to be the hardest part, where I create loops to enter the information onto the spreadsheet as this needs to be dynamic.

I am really excited as work has been extremely stressful due unplanned leave in my team and being a team member down and being the 2nd responsible for the team I have a lot of other task to manage, but I enjoy it and have been at the role for 13 years.