CODEWITHSUNDEEP
vbaexcelcopy-paste
Nitin Jadhav
Nitin Jadhav

Reputation: 527

VBA | How to Copy value from Cell to Cell in Excel

I want to copy a cell value to another cell, but I want to retain the value in a variable so I can use it as per requirement.

Following is the code i tried-

Private Sub CommandButton1_Click()
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
For x = 1 To NumRows
    a= Cells(x, 1).Value.Copy
    Cells(x, 2).Value= a.PasteSpecial
Next
End Sub

Upvotes: 4

Views: 114159

Answers (3)

L42
L42

Reputation: 19727

If you just want to pass Range value to array, no need to loop.
Try something like this:

Dim a

With Sheets("YourSheetName")
    a = Application.Transpose(.Range("A1", .Range("A" & _
        .Rows.Count).End(xlUp).Address))
End With

'~~> check your array
For i = Lbound(a)  To Ubound(a)
    Debug.Print a(i)
    '~~> rest of your code here to do what you like.
Next

Now, if you want to use your variable a after code execution, just declare it outside the sub like this:

Dim a As Variant '~~> you can also use Public a As Variant

Sub Something()
'~~> put similar code above here and the rest of your code
End Sub

Now, you need to put a test if the variable a is empty or not, else you'll just overwrite it and defeat your purpose.
A simple check like this should work:

If IsEmpty(a) Then '~~> or IsArray if you're sure you'll always have a as array
    '~~>  populate a
    '~~> proceed with what you want to do with a
Else
    '~~> proceed with what you want to do with a
End If

Hope it helps.

Upvotes: 0

user3516774
user3516774

Reputation: 29

First, I suggest that you use the Record Macro facility on the Developer ribbon. Be sure to set "Use Relative References" on, and record the mouse clicks and keystrokes of one iteration of what you want to do (copy A1 to B1). Then open the macro in VB and modify it.

This is what I got when I used this approach, and it seems to work for me. I hope it works for you too.

Sub Macro1()

Dim NumRows As Integer
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Activate

    For i = 1 To NumRows

        ActiveCell.Select
        Selection.Copy
        ActiveCell.Offset(0, 1).Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        ActiveCell.Offset(1, -1).Activate

    Next

End Sub

Upvotes: -2

CodeJockey
CodeJockey

Reputation: 1981

No need to use the Range.Copy method. Try this:

Dim a As Variant
a = Cells(x, 1).Value
Cells(x, 2).Value = a

If you want to retain ALL of the values, you will need to use an array:

Dim x As Long
Dim NumRows As Long

NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
ReDim a(1 to NumRows)

For x = 1 To NumRows
    a(x) = Cells(x,1).Value
    Cells(X,2).Value = a(x)
Next x

I also recommend explicit variable declaration. It can be found in your options or by typing Option Explicitat the VERY top, above any subs or functions. This will force you to declare your variables. I know it feels like a burden as a newbee, but it only takes one typo getting declared as a new variable to change your mind.

Upvotes: 8

Related Questions