CODEWITHSUNDEEP
excelvba
jnskls
jnskls

Reputation: 3

Copy Paste Cell beginning with equal sign (=====)

So my issue is that I have an input sheet that I can't change the formatting of. Unfortunately they used a line of equal signs =========== in a cell for differentiating the sections. I now need to copy/paste certain columns (almost as a whole) including theses "separator cells", however those can't be copied because they aren't recognised as strings. The error msg is Run-time error 1004: Application-defined or object-defined error, which is guess stems from the fact that I want to copy paste a cell with an equal sign in the front, so normally a formula. The columns also include different data types, so I can't really force everything to be a string. How can I skip these or even better copy/paste them into my other worksheet?

My problem is a very isolated issue so I'll just give the relevant lines of code. For context: wksh and wksh2 are declared worksheets, LastRow is the declared last row of the column, etc. It works for other columns, just not the one's with these ======== cells.

Dim arrC As Variant

arrC = wksh.Range("A2:" & LastRow)
wksh2.Range("A2").Resize(UBound(arrC), 1).Value = arrC

Upvotes: 0

Views: 719

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57743

First note that "A2:" & LastRow is no valid address as the column letter for the second part is missing. It needs to be something like "A2:A" & LastRow

First Option

One option is to loop through the array and test each element if it begins with = and replace it with '= (the apostroph is not shown but ensures that it is handled as text and not as formula). Note that this will kill all formulas in that range.

Dim arrC As Variant
arrC = wksh.Range("A2:A" & LastRow).Value

Dim iRow As Long
For iRow = 1 To UBound(arrC, 1)
    Dim iCol As Long
    For iCol = 1 To UBound(arrC, 2)
        If Left$(arrC(iRow, iCol), 1) = "=" Then
            arrC(iRow, iCol) = "'" & arrC(iRow, iCol) 
        End If
    Next iCol
Next iRow

wksh2.Range("A2").Resize(UBound(arrC), 1).Value = arrC

Second Option

The second option is to set the number format of the destination to text: .NumberFormat = "@" then paste the values and turn it back to general.

Dim arrC As Variant
arrC = wksh.Range("A2:A" & LastRow).Value

With wksh2.Range("A2").Resize(UBound(arrC), 1)
    .NumberFormat = "@"
    .Value = arrC
    .NumberFormat = "General"
End With

Note that .NumberFormat = "@" will turn also numbers into text so you need to turn it back to General to ensure that if there were numbers they are turned back to numbers again and you can calculate with them.

This workaround might have some odd effects on dates or other number formats. So this might not be a reliable solution depending on what data you have.

Third Option

Last option is .Copy and .PasteSpecial

wksh.Range("A2:A" & LastRow).Copy
wksh2.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats

'or without number formats
wksh2.Range("A2").PasteSpecial xlPasteValues

Upvotes: 2

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Are you performing any special operation post loading the data into an array? If not then I'd suggest using simple copy and paste routine through VBA which should work reliably.

Public Sub CopyPasteData()
    Dim wksh As Worksheet, wksh2 As Worksheet
    Set wksh = ThisWorkbook.Sheets(1)
    Set wksh2 = ThisWorkbook.Sheets(2)
    wksh.Range("A2:" & LastRow).Copy wksh2.Range("A2")
End Sub

Upvotes: 0

Related Questions