范围的一部分格式化为文本 [英] Part of the range formatted as text

问题描述

在此链接中@Pᴇʜ后面的答案.

我通过@Pᴇʜ使用宏将rng"RawTab1"复制到名为"Data"的工作表中的"DataTable"表中.

所以我的问题是:

• 是否可以粘贴这两个数字格式的列?

否则，宏将完美运行.

解决方案

尝试:

  Public Sub Combined()昏暗作为工作表设置sht = ThisWorkbook.Worksheets("Data")sht.Range("A3:M3"，sht.Range("A3:M3").End(xlDown)).ClearContents带有工作表(原始数据").范围("RawTab1")'从RawTab1复制所有内容，但不复制前2行暗淡弯曲范围设置Crng = .Resize(RowSize:=.Rows.Count-2).Offset(RowOffset:= 2)结束于sht.Range("A2").Resize(Crng.Rows.Count，Crng.Columns.Count).Value = _sht.Evaluate("IF(ISNUMBER(-"&Crng.Address(0，0，xlA1，1)&)，-"& Crng.Address(0，0，xlA1，1)&，"& Crng.Address(0，0，xlA1，1)&)")结束子 

Following the answer by @Pᴇʜ in this link Merging two macros

I have an additional challenge. Within the Named Range called "RawTab1" the column J contains values "Year" formatted as '2018 and in the column K contains values "Months" formatted as '12 .

I use the macro by @Pᴇʜ to copy the rng "RawTab1" to the Table called "DataTable" in the sheet called "Data"

So my question is:

• Is it possible to paste these two columns formatted as numbers?

Otherwise the macro is working perfect.

解决方案

Try:

Public Sub Combined()
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Worksheets("Data")
    sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents

    With Worksheets("Raw Data").Range("RawTab1")
        'copy everything from RawTab1 but not the first 2 rows
        Dim Crng As Range
        Set Crng = .Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2)
    End With

    sht.Range("A2").Resize(Crng.Rows.Count, Crng.Columns.Count).Value = _
         sht.Evaluate("IF(ISNUMBER(--" & Crng.Address(0, 0, xlA1, 1) & "),--" & Crng.Address(0, 0, xlA1, 1) & "," & Crng.Address(0, 0, xlA1, 1) & ")")

End Sub

这篇关于范围的一部分格式化为文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！