Sweep a defined area replacing certain values with data contained within an array
Posted on 2014-01-26
What I am trying to do is define a two dimension array,
dim mydata (1 to 5, 1 to 2) as string
Then, when a certain defined range within the sheet is changed, it does a check to see if any of the cells equal mydata(1,-) contain [version] and if they do it replaces them with mydata(,2)
So in this if I type into C3 "[update]" it will become v1.4.3 straight away.
This is my current code, which did work until I tried adding arrays in...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mydata(1 To 5, 1 To 2) As String
mydata(1, 1) = "[version]"
mydata(1, 2) = "v1.4.3"
For lp = 1 To 40
For lp2 = 1 To 40
For lp3 = 1 To 5
If Sheets("Sheet1").Cells(lp, lp2) = mydata(lp3, 1) Then
Sheets("Sheet1").Cells(lp, lp2) = mydata(lp3, 2)
As you can see I am using a for loop to check 40 x 40 cells, other than fixing the above so it works please :) Is there a better way to scan 40x40 cells and change values based on criteria from array?