VBA to automatically update a row when another cell is updated.

[prondish]

Hi everyone,

I have a VBA code that makes a function to evaluate the string in a cell as a function itself. It looks like this:

Function Evalu(ByVal S As String) As String
Evalu = Evaluate(S)
End Function

The idea is that the user can write a term like "sports" into one cell (A2) in a different sheet, and then using a bunch of concatenate formulas, it writes a code like this:

=CONCATENATE("IF(SUM(COUNTIF($B3,{"&A2&"}))>0,1,0))"

So that in the end, the concatenated string is searching another cell to see if it contains "sports". The idea is that this is done for a bunch of rows, so that I can label all the rows 0 or 1, to determine if they contain "sports".

However, if you write something different into A2, like "movies", I have to manually go back and auto fill the column that uses my eval function to relabel all my rows 0 or 1 according to "movies" now. Is there some way I can set it to automatically update the column where my VBA EVAL function is running? If possible, I'd like to do for just that column, since rebuilding the entire worksheet is quite time consuming.

Thanks everyone!

 

[GRTorres]

Ok, so in the VBA Project screen, did you make sure to paste the above code in the Sheet1 Object tab? Where you have your change event, so on Sheet1 cell E3, thats the sheet the VBA must be on:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a = Sheets("Sheet1")
Set b = Sheets("Sheet2")
Dim i As Long
Dim pickValue As Variant

If Target.Address = "$E$3" Then
    pickValue = a.Range("E3")
    For i = 3 To b.Range("J" & Rows.Count).End(xlUp).Row
        Set s = b.Range("J" & i).Find(pickValue, LookIn:=xlValues)
        If Not s Is Nothing Then
        b.Range("J" & i).Offset(0, -1) = 1
        Else: b.Range("J" & i).Offset(0, -1) = 0
        End If
    Next i
End If

End Sub

If something is a calculating problem, maybe make sure automatic calculation is on in your excel options.

This should work, I just tested it.

 

[prondish]

Hey GR,

Does my Evalu function have to be anywhere specific?

I just checked and my options are set to auto-calculate. Other than that, I have everything the same.... curious. If it's not the evalu function, could I send you a sample of what I'm working on? I'm getting pretty stumped.

Pete