0

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 see screenshot.

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
NewCalc
  • 3
  • 2
  • This isn't a forum. The @ sign doesn't work the way you think it does. It probably doesn't work this way even in forums. – Robert Harvey Jan 18 '19 at 16:09
  • You cannot do this while copy/paste. You need to do it before or after. Therefore you need to loop through the whole column and check each cell if it has a `'` as first letter and convert it. This will be a completely new macro and has nothing to do with your first one. Give it a try on your own. If you get stuck or errors [edit] your question and add your code. – Pᴇʜ Jan 18 '19 at 16:11
  • RobertHarvey, I'm new here but it is noted @Pᴇʜ, the ' is not copied into the table but still the columns are not treated as numbers? – NewCalc Jan 18 '19 at 16:17
  • Then just select the columns and chose a number format or select `general`. – Pᴇʜ Jan 18 '19 at 16:19
  • @Pᴇʜ is it posible to multiply the values in columns Year and Month with *1 after the paste and thus convert them to numbers? – NewCalc Jan 18 '19 at 16:21
  • You can loop through the column and do that if my previous comment didn't help. – Pᴇʜ Jan 18 '19 at 16:23
  • The columns are already formatted as `general` but still not converted as numbers I have also tried to format them as `number` but not working. Only by Paste Special and multiply then they convert to numbers – NewCalc Jan 18 '19 at 16:26
  • @NewCalc Then do that with these both columns. Macro Recorder can help. – Pᴇʜ Jan 18 '19 at 16:27
  • I have tried with rec macro but i'm not sure how to incorporate the recorded macro into the macro done by you? – NewCalc Jan 18 '19 at 16:35
  • you need to run it afterwards. – Pᴇʜ Jan 18 '19 at 16:35
  • `Sub ConvertToNumberYearMOnth() ActiveSheet.ListObjects("DataTable").ListColumns(10).DataBodyRange.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False End Sub` I get an 400 error, any idea why ? – NewCalc Jan 18 '19 at 16:51

1 Answers1

1

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
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thanks Scott, the macro is working fine but the performance is very slow. Any idea how it could be optimised? – NewCalc Jan 21 '19 at 15:14
  • If possible to restrict the macro to run the conversion only on the two columns not for the entire range? – NewCalc Jan 21 '19 at 15:48