CODEWITHSUNDEEP
excelvba
himanshu shekhar
himanshu shekhar

Reputation: 11

how to specify column as a range in a excel macro

I am trying to write a macro to compare two columns and if value are equal then it should copy value of another cell into a new cell. I wrote below macro: 

Sub Value()

   Dim rng1 As Range
   Set rng1 = Worksheets("Sheet1").Range("L2:L11860")
   Dim rng2 As Range
   Set rng2 = Worksheets("Sheet1").Range("W2:W12000")
   Dim rng3 As Range
   Set rng3 = Worksheets("Sheet1").Range("A2:A12000")
   Dim rng4 As Range
   Set rng4 = Worksheets("Sheet1").Range("X2:X12000")

If rng1.Value = rng2.Value Then

rng3.Value = rng4.Value


 End If

End Sub

It is giving me Run Time error 13 for the below line of code

If rng1.Value = rng2.Value Then

Please help in solving this.

Upvotes: 1

Views: 403

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

This should do it without loops:

Sub Value()

Dim rng1 As Range
Set rng1 = Worksheets("Sheet1").Range("L2:L12000")

Dim rng2 As Range
Set rng2 = Worksheets("Sheet1").Range("W2:W12000")

Dim rng3 As Range
Set rng3 = Worksheets("Sheet1").Range("A2:A12000")

Dim rng4 As Range
Set rng4 = Worksheets("Sheet1").Range("X2:X12000")

rng3.Value = Worksheets("Sheet1").Evaluate("=IF(INDEX(" & rng1.Address(0, 0) & "=" & rng2.Address(0, 0) & ",)," & rng4.Address(0, 0) & ","""")")


End Sub

Or as Dirk Suggested you could just:

Sub Value()

Dim rng1 As Range
Set rng1 = Worksheets("Sheet1").Range("L2:L12000")

Dim rng2 As Range
Set rng2 = Worksheets("Sheet1").Range("W2:W12000")

Dim rng3 As Range
Set rng3 = Worksheets("Sheet1").Range("A2:A12000")

Dim rng4 As Range
Set rng4 = Worksheets("Sheet1").Range("X2:X12000")

rng3.Value = Worksheets("Sheet1").Evaluate("=IF(" & rng1.Address(0, 0) & "=" & rng2.Address(0, 0) & "," & rng4.Address(0, 0) & ","""")")


End Sub

Upvotes: 2

Related Questions