Monday, April 20, 2015

macros - Find and replace in Excel

I have a table in sheet 1 has two columns A and B. I created a macro to find the value of A1 in sheet 2 and replace the value of B1 instead of value A1 in sheet 2


I am looking to improve the macro to cover the whole range of A and B columns.



Sub Find_Replace()
'
'
Sheets("Sheet1").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Selection.Replace What:="Value 1 ", Replacement:="value X", LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

sample table


** looking to improve Range("A1").Select to cover all available cells in range A and B

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...