修剪工作表VBA中的最后4个字符 [英] Trim last 4 characters from worksheet vba

问题描述

我在尝试将此代码应用于工作表时遇到问题.谁能提供一些指导?基本上我将我的文件名转换为工作表，并且我想从其中删除.pdf.左(Sh.Name，Len(Sh.Name)-4)我也有 Dim Sh.Name as String 的问题，所以我不知道如何从这里进行上述操作.

I'm having problem trying to apply this codes to worksheet.. can anyone provide some guidance? basically I have my filenames converted as worksheet and I want to remove .pdf from it.. Left(Sh.Name, Len(Sh.Name)-4) I also have problem dim Sh.Name as String so I have no idea how to proceed from here to apply the above..

Sub findstring()

 Dim sh As Worksheet, myCounter
 Dim Loc As Range, str As Variant

sh.Name = Left(sh.Name, Len(sh.Name) - 4)


For Each sh In ThisWorkbook.Worksheets

 If sh.Name <> "email" Then 
 With sh.UsedRange
 Set Loc = .Cells.Find(What:="Truck")

 If Not Loc Is Nothing Then
            MsgBox sh.Name

            myCounter = 1
            Set Loc = .FindNext(Loc)

 End If

End With
End If Next
If myCounter = 0 Then
MsgBox "Value not present in this workbook"

End If

End Sub

推荐答案

您说的

sh.Name = Left(sh.Name, Len(sh.Name) - 4)

... sh 是刚刚声明的，未定义.如果要对工作簿的所有工作表执行此操作，则可以删除此行代码，并使用下面的循环:

... sh is just declared, not defined. If you want to do this to all the worksheets of your workbook, you can remove this line of code and use the loop just below:

For Each sh In ThisWorkbook.Worksheets
    If Right(sh.Name,4) = ".pdf" Then
        sh.Name = Left(sh.Name, Len(sh.Name) - 4)
    End If
Next sh

这篇关于修剪工作表VBA中的最后4个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！