CODEWITHSUNDEEP
excelvba
Vvek Swaminathan
Vvek Swaminathan

Reputation: 11

How to assign Selection.Copy value to a String in Excel VBA

I am new to VB and Macro.

I'm trying to copy contents of a cell from one sheet and assign it to a string so that I can pass the string wherever I want to.

Here is what I am trying to do and it isn't working.

Dim findd As String
Columns("A:A").Select
Windows("Book111.xlsm").Activate
Range("A1").Select
Selection.Copy
findd = Selection.Paste
Windows("Book2.xls").Activate
Selection.Replace What:=findd, Replacement:="", LookAt _
    :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Upvotes: 1

Views: 4970

Answers (1)

YowE3K
YowE3K

Reputation: 23994

The way to assign a String variable to the value of a Cell would be something like:

findd = Range("A1").Value

As it is almost always a bad idea to use Select statements, I believe your code could be rewritten as:

Workbooks("Book2.xls").WorkSheets("whatever").Columns("A:A").Replace _
       What:=Workbooks("Book111.xlsm").Worksheets("whatever").Range("A1").Value, _
       Replacement:="", _
       LookAt:=xlPart, _
       SearchOrder:=xlByRows, _
       MatchCase:=False, _
       SearchFormat:=False, _
       ReplaceFormat:=False

I'm not sure what worksheets you have active in the two different workbooks, so you would need to replace the two occurrences of whatever with the appropriate names.

Edit: Update based on comment requesting how to do this over multiple values:

Dim toBeReplaced As Range
For Each toBeReplaced In WorkBooks("Book111.xlsm").Worksheets("whatever").Range("A1:A30")
    Workbooks("Book2.xls").WorkSheets("whatever").Columns("A:A").Replace _
           What:=toBeReplaced.Value, _
           Replacement:="", _
           LookAt:=xlPart, _
           SearchOrder:=xlByRows, _
           MatchCase:=False, _
           SearchFormat:=False, _
           ReplaceFormat:=False
Next

Upvotes: 2

Related Questions