VBA更改多个单元格格式 - 停止重复值 [英] VBA for changing multiple cell formats - stop values from repeating

问题描述

我提出的一个以前的问题是感谢 Vincent G 的帮助。总之，我已经写了一些VBA，它标识了一般格式化的单元格，如< 0（符号<作为单元格值的一部分），并且VBA将格式改变为具有与原始值相同的小数位数（例如，< 0.564general将被改变为<<< ; 0.564数字）。

这段代码在一小组测试数据上工作，但是当扩展时，我遇到了另一个问题：多个单元格的小数位数相同，VBA按照<0.0到<0.00000的自定义格式工作，但是由于某种原因，它将它遇到的第一个< 0实例的相同值复制到该格式的所有其他实例中。

描述有点痛苦，所以请查看我在Dropbox上的电子表格样例：工作表有测试数据，VBA和问题描述。

非常感谢您的时间和专业知识，
Christian

解决方案

使用@Jeeped解决方案解决最后一个问题只是做这样的事情。也许可以做得更健壮一点（修改c.value之前做检查等），但它应该做你想做的。

  Sub test（）
 
 For Each c In Sheet1.Range（A2：A25）
如果Left（c.Value，1）=<然后
 c.Value = Right（c.Value，Len（c.Value）-1）
 c.NumberFormat =[> 1]< 0; [> 0]< 0.0 ####; 0; @
 End If 
 Next 
 
 End Sub 
  

A previous problem I posed to the group was solved thanks to Vincent G's help.

To summarize, I have written some VBA which identifies cells formatted general like "<0" (with the symbol "<" as part of the cell value), and the VBA will change the format to a numerical format (with the "<" as part of the format) with the same number of decimal places as the original value (e.g., "<0.564" general will be changed to "< 0.564" numerical).

This code worked on a small set of test data, but when scaling up, I have run into another problem: with multiple instances of cells with teh same number of decimal places, the VBA works as intended for custom formats "< 0.0" through "< 0.00000", but for some reason it copies the same value for the first instance of "<0" it encounters into all other instances of this formatting.

It's a little painful to describe so please check out my example spreadsheet on Dropbox: the sheet has test data, the VBA, and a description of the issue.

Many thanks for your time and expertise, Christian

解决方案

Using @Jeeped solution to the last problem you could just do something like this. Probably could be made a bit more robust (trimming c.value before doing checks and such) but it should do what you want.

Sub test()

For Each c In Sheet1.Range("A2:A25")
    If Left(c.Value, 1) = "<" Then
        c.Value = Right(c.Value, Len(c.Value) - 1)
        c.NumberFormat = "[>1]< 0;[>0]< 0.0####; 0; @"
    End If
Next

End Sub

这篇关于VBA更改多个单元格格式 - 停止重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！