VBA code works differently

Question

I have these 3 pieces of code, which work differently but deliver similar result. The aim is that I have in a separate column only the values from filled cells, and no blanks

This code copies only the results from the formulas in I10:I61. The formula returns also "", which I do not need.

With Worksheets("Calculator").Range("I10:I61")
    .Offset(, 0).SpecialCells(xlCellTypeFormulas, xlNumbers).Copy
    Worksheets("Graphs").Range("F2").PasteSpecial SkipBlanks:=True, Paste:=xlPasteValues
End With

This code copies values, where there are no formulas

With Worksheets("Calculator")
    Set VisRng = .Range("D10:D61").SpecialCells(xlCellTypeConstants)
    VisRng.Copy
    Worksheets("Graphs").Range("B2").PasteSpecial xlValues
End With

This is supposed to copy text results from formula. It does not work. The formula returns also "", which I do not need.

With Worksheets("Calculator").Range("K10:K61")
    .Offset(, 0).SpecialCells(xlCellTypeFormulas, xlTextValues).Copy
    Worksheets("Graphs").Range("A2").PasteSpecial SkipBlanks:=True, Paste:=xlPasteValues
End With

However, the last piece of code does not work. It should copy and paste only the filled cells and not the emty once(containing ""). Now it copies and pastes everything. How can I make it work? The result is:

K             A
Paris         Paris


Amsterdam      Amsterdam

I need it to be

A
Paris
Amsterdam

Your observation that they work differently is correct. But what is your question? — GSerg, Mar 08 '17 at 13:49
There are now more observations but still no question. Are you trying to ask how to make the third piece work? If so, please describe what it should do and what it does instead. — GSerg, Mar 08 '17 at 13:57
When you say it copies the cells containing `""` do you mean the formulas place an empty string result, like `=IF(A1=2,A1,"")`. If so, Excel treat this as text and you may need to use another method to collect only the cells you want to copy. — Scott Holtzman, Mar 08 '17 at 14:01
It copies the column and the cells, which contain a "" result in the original range. are left empty — tombata, Mar 08 '17 at 14:06
your reply is still not clear to me but check out my proposed solution posted as an answer — Scott Holtzman, Mar 08 '17 at 14:07
It works like it should. It might not be what you expect, but that's because an empty string is [not the same](http://stackoverflow.com/q/1119614/11683) as blank. — GSerg, Mar 08 '17 at 14:11

Scott Holtzman · Accepted Answer · 2017-03-08T17:10:47.810

2

Since excel treats formula results of "" as text, try this method instead.

Dim vData as Variant
vData = Worksheets("Calculator").Range("K10:K61")

Dim i as Integer
For i = Lbound(vData) to uBound(vData)
   If len (vData(i,1)) And Not IsNumeric(vData(i,1)) Then 
     With Worksheets("Graphs")
         .Range("A" & .Rows.Count).End(xlUp).Offset(1).Value = vData(i,1)
     End With
   End If
Next

edited Mar 08 '17 at 17:10

answered Mar 08 '17 at 14:07

Scott Holtzman

27,099
5
37
72

Just to comment: This does not replace the number 3 of the question. This copies **any** non-empty cell (including numeric values) instead of text values of formulas only! – Pᴇʜ Mar 08 '17 at 14:12
1

Still not the same. If the formula returns a string convertible to number (e.g. `="42"`), `SpecialCells(xlCellTypeFormulas, xlTextValues)` will properly see it, but `IsNumeric()` on it will be `True`. You probably want `and vartype(vData(i,1)) = vbstring`. – GSerg Mar 09 '17 at 06:56
1

@GSerg yes and another difference is `xlCellTypeFormulas` takes only results of formulas (e.g. cell contains `="aaa"`) and no constant strings (e.g. cell contains `aaa`). But your code still does. Nontheless the question owner accepted the answer it should be clear that the code does not the same as number 3 of the question. – Pᴇʜ Mar 09 '17 at 09:52

VBA code works differently

1 Answers1