DemonForce
asked on
Sweep a defined area replacing certain values with data contained within an array
Hi,
What I am trying to do is define a two dimension array,
dim mydata (1 to 5, 1 to 2) as string
mydata(1,1)="[version]"
mydata(1,2)="v1.4.3"
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)
Beep
End If
Next
Next
Next
End Sub
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?
Cheers
What I am trying to do is define a two dimension array,
dim mydata (1 to 5, 1 to 2) as string
mydata(1,1)="[version]"
mydata(1,2)="v1.4.3"
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,
Sheets("Sheet1").Cells(lp,
Beep
End If
Next
Next
Next
End Sub
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?
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.