删除最后一个字符vba [英] Removing last characters vba

问题描述

我有一列，每个单元格上都有一组数字.

I have a column that has a set of Numbers on each cell.

我只是想将列上的数据复制到最后一行，而在每个单元格上保留最后3位数字.

Im simply looking to copy the data on the column til the last row leaving behind the last 3 digits on each cell.

我在最后一行有一个变量: LASTROW 和一个已设置的范围.

I have a variable for the last row: LASTROW and a range already set.

我的代码有点像这样

inputWS1.Range("A4:A" & lastRow).Copy outputWS.Range("B2")

正如我所说，我希望将数据从A4复制到B2等，但是当它们移到另一张纸上的B2时，我想删除A4上的最后3个字符.

As i said, im looking to copy data from A4 to B2 and on, but i want to remove the last 3 characters on A4 when they move to B2 on the other sheet.

请帮助，并感谢您的所有帮助！

Please Help and thanks for all your help!

我当前的代码是:

Private Sub Update_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim path As String, fileName As String
Dim lastRowUniversal As Long, lastRowOutput As Long, rowCntr As Long, lastColumn As Long
Dim inputWS1 As Worksheet, inputWS2 As Worksheet, inputWS3 As Worksheet, outputWS As Worksheet
Dim rng As Range
Dim arr As Variant
Dim i As Long
Const dtFORM As String = "=IF(ISNUMBER(J4:J<r>),DATE(YEAR(J4:J<r>)-1," & "MONTH(J4:J<r>),DAY(J4:J<r>)),J4:J<r>)"



'set your sheets here
Set inputWS1 = ThisWorkbook.Sheets("Universal")
Set inputWS2 = ThisWorkbook.Sheets("Geovera")
Set inputWS3 = ThisWorkbook.Sheets("Citizens")
Set outputWS = ThisWorkbook.Sheets("Carriers")
Set rng = inputWS3.Range("D2:D")
arr = inputWS3.Range("M2:M" & LastRowCitizens)

    'get last rows from both sheets
lastRowUniversal = inputWS1.Cells(Rows.Count, "A").End(xlUp).Row
LastRowCitizens = inputWS3.Cells(Rows.Count, "A").End(xlUp).Row
lastRowGeovera = inputWS2.Cells(Rows.Count, "A").End(xlUp).Row
LastRowPolicy = outputWS.Cells(Rows.Count, "B").End(xlUp).Row
lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
lastColumn = inputWS1.Cells(1, Columns.Count).End(xlToLeft).Column

    'get last rows from both sheets
lastRowUniversal = inputWS1.Cells(Rows.Count, "A").End(xlUp).Row
LastRowCitizens = inputWS3.Cells(Rows.Count, "A").End(xlUp).Row
lastRowGeovera = inputWS2.Cells(Rows.Count, "A").End(xlUp).Row
LastRowPolicy = outputWS.Cells(Rows.Count, "B").End(xlUp).Row
lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
lastColumn = inputWS1.Cells(1, Columns.Count).End(xlToLeft).Column

rowCntr = 1

For i = 1 To UBound(arr)
arr(i) = Left(arr(i), Len(arr(i)) - 3)
Next i


'get last rows from both sheets
lastRowUniversal = inputWS1.Cells(Rows.Count, "A").End(xlUp).Row
LastRowCitizens = inputWS3.Cells(Rows.Count, "A").End(xlUp).Row
lastRowGeovera = inputWS2.Cells(Rows.Count, "A").End(xlUp).Row
LastRowPolicy = outputWS.Cells(Rows.Count, "B").End(xlUp).Row
lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
lastColumn = inputWS1.Cells(1, Columns.Count).End(xlToLeft).Column

'Universal
inputWS1.Range("A4:A" & lastRowUniversal).Copy outputWS.Range("B2")
inputWS1.Range("B4:B" & lastRowUniversal).Copy outputWS.Range("C2")
inputWS1.Range("N4:N" & lastRowUniversal).Value = inputWS1.Name
inputWS1.Range("N4:N" & lastRowUniversal).Copy outputWS.Range("E2")
inputWS1.Range("L4:L" & lastRowUniversal).Value = inputWS1.Evaluate(Replace(dtFORM, "<r>", lastRowUniversal))
inputWS1.Range("L4:L" & lastRowUniversal).Copy outputWS.Range("G2")
inputWS1.Range("G4:G" & lastRowUniversal).Copy outputWS.Range("H2")

'Geovera
inputWS2.Range("F2:F" & lastRowGeovera).Copy outputWS.Range("B" & lastRowUniversal - 1)
inputWS2.Range("I2:I" & lastRowGeovera).Copy outputWS.Range("C" & lastRowUniversal - 1)
inputWS2.Range("P2:P" & lastRowGeovera).Value = inputWS2.Name
inputWS2.Range("P2:P" & lastRowGeovera).Copy outputWS.Range("E" & lastRowUniversal - 1)
inputWS2.Range("N2:N" & lastRowGeovera).Copy outputWS.Range("H" & lastRowUniversal - 1)
inputWS2.Range("G2:G" & lastRowGeovera).Copy outputWS.Range("G" & lastRowUniversal - 1)

'Citizens
inputWS3.Range("M2:M" & LastRowCitizens).Value = arr






Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

推荐答案

使用左功能

With inputWS3
    For i = 2 To lastrow
        .Cells(i, "M") = Left(.Cells(i, "M").Value, Len(.Cells(i, "M").Value) - 3)
    Next i
End With

这篇关于删除最后一个字符vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！