if a is and c is then b = x

[AndrewNH]

hi I was wondering if someone could help I am trying to write a macro to change data in a cell if spacific data is in two other cells I will have various different variations of the data but the basic struture evades me.
i hav something along these lines, there is a large sheet of data that had to be run everyday and the information changes daily
could someone help with this macro ?
If Range("A").Value = "BNSSG Extra Demand" And Range("E").Value = "Almondsbury " Then Replace.Value("B") = "Almondsbury"

 

[steve the fish]

This will be slow and there could be quicker ways but you havent given us enough info to know if other methods are possible. You need to change sheet name to suit.

With Sheets("Sheet1")
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lr
        If .Range("A" & i).Value = "BNSSG Extra Demand" Then
            If .Range("E" & i).Value = "Almondsbury" Then
                .Range("B" & i).Value = "Almondsbury"
            End If
        End If
    Next
End With

 

[AndrewNH]

thanks With a few mods I got this to work for me, although I do have a lot more values to change the value of E and B need to change to search for other changes could this be added in after the Range "B" line or do i need to add this after the Next statement?

 

[steve the fish]

Sure. What are these values? Are they in a range of cells?

 

[AndrewNH]

Hi Steve, they are all based in the same columns just varying data based on locations probably six locations. Thanks this will make the daily report much smoother

 

[steve the fish]

Yes but you will need to feed the code with what they are. Do you have them in a separate list somewhere or do you want to hardcode them into the code? It will change the way it is written.

 

[AndrewNH]

hardcoding them into the code will be fine

 

[steve the fish]

Here you are. Put your values you want to search for in the array:

arr = Array("Almondsbury", "Wherever")

With Sheets("Sheet1")
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lr
        If .Range("A" & i).Value = "BNSSG Extra Demand" Then
            For a = LBound(arr) To UBound(arr)
                If LCase(.Range("E" & i).Value) = LCase(arr(a)) Then
                    .Range("B" & i).Value = arr(a)
                End If
            Next
        End If
    Next
End With