Link to home
Start Free TrialLog in
Avatar of DemonForce
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
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial