same case value but run different formula if first character is

Matt Taylor

I am making a renaming script and all is working unless I have a specific file name. Which is common in every project.

I am renaming file names based on the file length. I have 2 file lengths that are both = to 12 but need to be named differently. Can I have an if statement inside of that one case to see if the first Character is =0,c,e"?

Sub Convert()
Application.ScreenUpdating = False

Dim rng As Range, aCell As Range
Dim val As String
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:A" & LastRow)

For Each aCell In rng.Cells
Select Case Len(aCell)
    Case 12
        If val = Left(aCell, 1) = "0" Or "c" Or "e" Then 'Example: 01730101.pdf = S-173-0101.pdf
        val = "S-" & Mid(aCell, 2, Len(aCell) - 9) & "-" & Mid(aCell, 5, Len(aCell) - 8)
        Else 'Example: 173d0071.pdf = S-173-D7.pdf
        val = "S-" & Left(aCell, Len(aCell) - 9) & "-" & Mid(aCell, 4, Len(aCell) - 8)
        End If
    Case 13 'Example: 173d00710.pdf = S-173-D7.pdf
        val = "S-" & Left(aCell, Len(aCell) - 10) & "-" & Mid(aCell, 4, Len(aCell) - 9)
    Case 15 'Example: 173d170c071.pdf = SD-170-C7.pdf
        val = "SD-" & Left(aCell, Len(aCell) - 15) & Mid(aCell, 5, Len(aCell) - 12) & "-" & Mid(aCell, 8, Len(aCell) - 12)
    Case 16 'Example: REF-173d0071.pdf = REF-173-D7.pdf
        val = Left(aCell, Len(aCell) - 9) & "-" & (Mid(aCell, 8, Len(aCell) - 12))
    Case 17 'Example: REF173d00710.pdf = REF-173-D7.pdf
        val = Left(aCell, Len(aCell) - 10) & "-" & (Mid(aCell, 8, Len(aCell) - 13))
On Error GoTo whoa
    Case Else
        val = "_Mod " & Left(aCell, Len(aCell) - 4)
End Select

val = UCase(val)

val = val & " " & aCell.Offset(, 2) & aCell.Offset(, 3)

aCell.Offset(, 1).Value = val
Next
Call RemoveZero
Call RemoveBadChar
    Range("C1").Select
    Worksheets("Rename").Columns("B").AutoFit
    Application.ScreenUpdating = True
whoa:
MsgBox "Please delete any empty rows."
ActiveSheet.Range("A1").End(xlDown).Offset(1).EntireRow.Select
Application.ScreenUpdating = True
Exit Sub
End Sub

Thx for any help

Lowpar
Sub Convert()
Application.ScreenUpdating = False

Dim rng As Range, aCell As Range
Dim val As String, check
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:A" & LastRow)

For Each aCell In rng.Cells
Select Case Len(aCell)
    Case 12
        'I added a check here
        check = Left(aCell, 1)
        If check = "0" Or check = "c" Or check = "e" Then  'Example: 01730101.pdf = S-173-0101.pdf
        val = "S-" & Mid(aCell, 2, Len(aCell) - 9) & "-" & Mid(aCell, 5, Len(aCell) - 8)
        Else 'Example: 173d0071.pdf = S-173-D7.pdf
        val = "S-" & Left(aCell, Len(aCell) - 9) & "-" & Mid(aCell, 4, Len(aCell) - 8)
        End If
        check = ""
    Case 13 'Example: 173d00710.pdf = S-173-D7.pdf
        val = "S-" & Left(aCell, Len(aCell) - 10) & "-" & Mid(aCell, 4, Len(aCell) - 9)
    Case 15 'Example: 173d170c071.pdf = SD-170-C7.pdf
        val = "SD-" & Left(aCell, Len(aCell) - 15) & Mid(aCell, 5, Len(aCell) - 12) & "-" & Mid(aCell, 8, Len(aCell) - 12)
    Case 16 'Example: REF-173d0071.pdf = REF-173-D7.pdf
        val = Left(aCell, Len(aCell) - 9) & "-" & (Mid(aCell, 8, Len(aCell) - 12))
    Case 17 'Example: REF173d00710.pdf = REF-173-D7.pdf
        val = Left(aCell, Len(aCell) - 10) & "-" & (Mid(aCell, 8, Len(aCell) - 13))
On Error GoTo whoa
    Case Else
        val = "_Mod " & Left(aCell, Len(aCell) - 4)
End Select

val = UCase(val)

val = val & " " & aCell.Offset(, 2) & aCell.Offset(, 3)

aCell.Offset(, 1).Value = val
Next
Call RemoveZero
Call RemoveBadChar
    Range("C1").Select
    Worksheets("Rename").Columns("B").AutoFit
    Application.ScreenUpdating = True
whoa:
MsgBox "Please delete any empty rows."
ActiveSheet.Range("A1").End(xlDown).Offset(1).EntireRow.Select
Application.ScreenUpdating = True
Exit Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Comparing same character gives different results

From Dev

Different enums, same value

From Dev

How to treat first character different when appending same string variable in JavaScript?

From Dev

Filter column value by the first character?

From Dev

hyphen character and apostrophe character - the same ASCII code in different languages?

From Dev

How do I add String value in Hash Set if a string contains the same character in upper case and lower case?

From Dev

Scala declare multiple variables at the same row with the first character as upper-case

From Dev

Why is a field with a value of 0 not the same as 0 in this formula?

From Dev

Unable to use the same formula for different rows in excel

From Dev

use ((c1^c2) & ~32) to test if c1 and c2 are the same character in different case

From Dev

In R Merging rows where a column has same value but different case

From Dev

Displaying different unicode values for same character in php

From Dev

Run formula only If cell contains a value

From Dev

Different enums, same value

From Dev

How to use different formula in case of en error?

From Dev

AutoComplete list is not showing same value but different in case for textbox control in VB.Net

From Dev

DB2: find field value where first character is a lower case letter

From Dev

Finding words with the same first character

From Dev

same formula but different result in Matlab and Excel

From Dev

hyphen character and apostrophe character - the same ASCII code in different languages?

From Dev

How do I add String value in Hash Set if a string contains the same character in upper case and lower case?

From Dev

Run same command with different parameters

From Dev

use ((c1^c2) & ~32) to test if c1 and c2 are the same character in different case

From Dev

Excel formula to subtract a different value based on conditions

From Dev

Replacing two of the same character with different strings

From Dev

NSBundle files with the same name but different Upper / Lower case at first letter

From Dev

Check if first character is the same as the last character?

From Dev

Run formula after matching to corresponding value

From Dev

Formula excel find data from first character in cell

Related Related

  1. 1

    Comparing same character gives different results

  2. 2

    Different enums, same value

  3. 3

    How to treat first character different when appending same string variable in JavaScript?

  4. 4

    Filter column value by the first character?

  5. 5

    hyphen character and apostrophe character - the same ASCII code in different languages?

  6. 6

    How do I add String value in Hash Set if a string contains the same character in upper case and lower case?

  7. 7

    Scala declare multiple variables at the same row with the first character as upper-case

  8. 8

    Why is a field with a value of 0 not the same as 0 in this formula?

  9. 9

    Unable to use the same formula for different rows in excel

  10. 10

    use ((c1^c2) & ~32) to test if c1 and c2 are the same character in different case

  11. 11

    In R Merging rows where a column has same value but different case

  12. 12

    Displaying different unicode values for same character in php

  13. 13

    Run formula only If cell contains a value

  14. 14

    Different enums, same value

  15. 15

    How to use different formula in case of en error?

  16. 16

    AutoComplete list is not showing same value but different in case for textbox control in VB.Net

  17. 17

    DB2: find field value where first character is a lower case letter

  18. 18

    Finding words with the same first character

  19. 19

    same formula but different result in Matlab and Excel

  20. 20

    hyphen character and apostrophe character - the same ASCII code in different languages?

  21. 21

    How do I add String value in Hash Set if a string contains the same character in upper case and lower case?

  22. 22

    Run same command with different parameters

  23. 23

    use ((c1^c2) & ~32) to test if c1 and c2 are the same character in different case

  24. 24

    Excel formula to subtract a different value based on conditions

  25. 25

    Replacing two of the same character with different strings

  26. 26

    NSBundle files with the same name but different Upper / Lower case at first letter

  27. 27

    Check if first character is the same as the last character?

  28. 28

    Run formula after matching to corresponding value

  29. 29

    Formula excel find data from first character in cell

HotTag

Archive