如果Len=3,则将单元格数据向左移动1列,循环遍历列。

huangapple go评论51阅读模式
英文:

If Len=3, move Cell Data 1 column to the left, loop through column

问题

我需要将组代码从它们当前的列移到左边的一列。这是一个宏,因为行数会根据文件/表格而变化。
我找到了一个答案,看起来与想法一样简单,但对我不起作用。
列实际上没有变化。

'Moving Group Codes

Dim cell As Range
For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange)
If Len(cell.Value) = 3 Then
'偏移粘贴(向下偏移0个单元格,向左偏移1个单元格)
Selection.Copy
Selection.Offset(, -1).Select
ActiveSheet.Paste
End If
Next cell

错误:运行时错误'438'
对象不支持此属性或方法

代码出错:

Selection.Offset(, -1).Select

任何帮助将不胜感激。
谢谢,
朱莉

我尝试了这些代码:全部失败

Selection.ColumnOffset(, -1).Paste

Selection.ColumnOffset(, -1).Select
ActiveSheet.Paste

我确信我尝试了更多,但它们都失败了。

英文:

I need to move group codes out of their current column and move to the left 1 column. this is in a Macro because the rows will change based on the file/tables.
I've found an answer that seems as simple as the idea, but its not working for me.
The columns don't really change.

'Moving Group Codes

Dim cell As Range
For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange)
    If Len(cell.Value) = 3 Then
        'Offset Paste (offsets 0 cells down and 1 to the LEFT)
    Selection.Copy
    Selection.Offset(, -1).Select
    ActiveSheet.Paste
    End If
Next cell

>Error: Run-time error '438'
Object doesn't support this property or method

Code getting error:

Selection.Offset(, -1).Select

Any help will be greatly appreciated.
Thank you,
Julie

I tried these codes: all fails

Selection.ColumnOffset(, -1).Paste

Selection.ColumnOffset(, -1).Select
ActiveSheet.Paste

I'm sure I tried more, but they all failed.

答案1

得分: 0

今天早上我再次仔细查看,找到了一个非常有效的解决方案!

Dim cell As Range
For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange)
    If Len(cell.Value) = 3 Then
        cell.Cut Destination:=cell.Offset(columnoffset:=-1)
    End If
Next cell

对于这段代码的结果和简洁性,我非常满意!

英文:

This morning I took another look and found a solution that worked like a CHARM!

Dim cell As Range
For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange)
    If Len(cell.Value) = 3 Then
    cell.Cut Destination:=cell.Offset(columnoffset:=-1)
    End If
Next cell

I couldn't be Happier with the results and simplicity of the code.

huangapple
  • 本文由 发表于 2023年6月18日 22:54:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76501139.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定