我想讓列中的每個字串都小寫并洗掉所有空格。但我想參考代碼中的列名而不是列號（因為它可能會有所不同，我想在列具有相同名稱但不在同一個地方的其他作業表上使用代碼）。作業表中的資料如下所示（簡單示例）：
| 家具 | 顏色 | 數量 |
|---|---|---|
| 椅子 | 粉色的 | 2 |
| 沙發 | 粉色的 | 1 |
| 桌子 | 藍色 | 1 |
| 沙發 | 1 |
所以第 1 行實際上是標題。我想將“顏色”列中的值小寫并洗掉空格
我試過了
For Each cell In Column('color').cells
cell.Value = LCase(cell.Value)
Next cell
甚至似乎都無法正確回圈
有人有建議嗎？提前致謝
uj5u.com熱心網友回復：
只是一些快速代碼，但我認為它應該可以作業。GetColumnNumber 函式接受一個名稱和一個范圍，并在該范圍的第一行中找到具有該名稱的列。它回傳找到它的列號。其他代碼只是遍歷所有行，進行所描述的替換和小寫。
Sub test()
Dim myrange As Range
Set myrange = Application.ActiveSheet.usedrange
colorcolumn = GetColumnNumber("color", myrange)
For x = 2 To myrange.Rows.Count
currdata = myrange.Cells(x, colorcolumn)
myrange.Cells(x, colorcolumn) = Replace(LCase(currdata), " ", "")
Next x
End Sub
Function GetColumnNumber(n As String, r As Range)
For x = 1 To r.Columns.Count
If r.Cells(1, x) = n Then
GetColumnNumber = x
Exit For
End If
Next x
End Function
uj5u.com熱心網友回復：
請嘗試下一個方法：
Sub LowerCaseNoSpaces()
Dim sh As Worksheet, lastR As Long, colName As String, rngProc As Range, necCol As Range
colName = "color"
Set sh = ActiveSheet
Set necCol = sh.rows(1).Find(what:=colName, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not necCol Is Nothing Then 'if the header has been found:
Set rngProc = sh.Range(necCol, sh.cells(sh.rows.count, necCol.Column).End(xlUp)) 'set the range from the header to the last filled cell in that column
'process all the filled column using Evaluate:
rngProc.value = Application.Evaluate("SUBSTITUTE(LOWER(" & rngProc.Address & "), "" "", """")")
End If
End Sub
uj5u.com熱心網友回復：
假設您要替換所有空格而不僅僅是修剪字串，這應該與@BigBen 的 Range.Find 建議一起使用并回圈遍歷單元格
Option Explicit
Public Sub ChangeColumToLower()
Const HEADER_ROW As Integer = 1
Const FIND_COLUMN As String = "color"
Dim rgeHeader As Range
Dim rgeColumn As Range
Dim rgeValues As Range
Dim lngCol As Long
Dim lngRow As Long
Dim lngLastRow As Long
Dim colValue As Object
Set rgeHeader = Range(HEADER_ROW & ":" & HEADER_ROW) ' Header Row
Set rgeColumn = rgeHeader.Find(FIND_COLUMN)
lngCol = rgeColumn.Column
lngRow = rgeColumn.Row 1
' Best way to find last row of data if column has empty cells
lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Set rgeValues = Range(Cells(lngRow, lngCol), Cells(lngLastRow, lngCol))
' Loop through all values
For Each colValue In rgeValues
' Change to lower case and remove all spaces
colValue.Value = Replace(LCase(colValue.Value), " ", vbNullString)
Next
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454689.html