VBA Default value in cell based on another cell

Question

I'm pretty new to VBA, but found this code that inserts a default value. The problem is that i need to have a default value inserted in a column based on another column.

Say that in "Column A" it takes the value 1 if the row is active, and 0 if the row is inactive. If column "A1" = 1 it should insert 9999 in column "C1" if 0 it shouldn't do anything. Can anyone help me modify the code?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, inter As Range, r As Range
Set C = Range("C9:C21")
Set inter = Intersect(C, Target)
If inter Is Nothing Then Exit Sub

Application.EnableEvents = False
  For Each r In inter
     If r.Value = "" Then r.Value = 9999
  Next r
Application.EnableEvents = True
End Sub

Again thank you

What do you mean by active/inactive? Would this mean all of column A has zeros until a cell is selected within the column and then that cell turns to 1 and becomes zero again when a different cell becomes the active cell? — QHarr, Jan 06 '18 at 19:03
Column A contains a basic if formula that is equal to 1 if B <>"". To illustrate: B1, B2 and B3 contains a value, hence A1, A2 and A3 all equals 1. The calculation for column A is If(B1="",0,1). Again if B4 ==" then A1=0. In relation to the code it should automatically insert 9999 in C1 if A1=1, insert 9999 in C2 if A2=1. Sorry for any confusion — aagaardist, Jan 07 '18 at 08:21

QHarr · Accepted Answer · 2018-01-07T21:32:00.610

1

Try the following. It is based on detecting a change in column B and setting C according to A. I have set from cell B1 but you can adjust as necessary.

Not that it is terribly relevant but I checked the syntax for intersect against this

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim aRange As Range

    Set aRange = ActiveSheet.Range("B1:B21") 'Changed to B1 start

    If Not Application.Intersect(Target, aRange) Is Nothing Then

        If Target.Offset(, -1) = 1 Then          

            Target.Offset(, 1) = 9999

        ElseIf Target.Offset(, -1) = 0 Then 

            Target.Offset(, 1) = vbNullString

        End If

    End If

End Sub

edited Jan 07 '18 at 21:32

answered Jan 07 '18 at 10:05

QHarr

83,427
12
54
101

Hello the code you made worked allmost perfectly. I removed "If Target.Offset(, -1) = 1 Then". Hereby the code becomes: `Private Sub Worksheet_Change(ByVal Target As Range) Dim aRange As Range Set aRange = ActiveSheet.Range("B1:B21") If Not Application.Intersect(Target, aRange) Is Nothing Then Target.Offset(, 1) = 9999 End If End Sub` The only problem is that if i remove the value from column A, say A1, then 9999 in cell C1 should dissapear aswell, since A1 <> 1 anymore – aagaardist Jan 07 '18 at 17:19
I’ll update later today. Though col A is supposed to contain formula derived values isn’t it so you would be removing that? – QHarr Jan 07 '18 at 17:26
A will contain a formula that gives it value 1 if true and 0 if false. So if Column A, say A3 changes to 0 then C3 should be nothing "". If A3 later changes to 1, then C3 should again be 9999. I hope it makes sence, and thank you for all your help so far. – aagaardist Jan 07 '18 at 17:46
In the comment above you said “if i remove the value from column A...” – QHarr Jan 07 '18 at 17:49
Yes i'm sorry about that i wasn't being precise enough. The value in Column A never dissapears. It only takes the value 1 or 0. I tried to fix the macro myself by inserting an elseif function, but that doesn't seem to work. `Private Sub Worksheet_Change(ByVal Target As Range) Dim aRange As Range Set aRange = ActiveSheet.Range("A1:A21") If Application.Intersect(Target, aRange) = 1 Then Target.Offset(, 3) = 9999 ElseIf Application.Intersect(Target, aRange) = 0 Then Target.Offset(, 3) = "" End If End Sub` – aagaardist Jan 07 '18 at 18:15
Do you mean if A1 = 0 then C1 = “”? – QHarr Jan 07 '18 at 18:21
Yes that is exactly what i mean. If A1=0 then C1 should be equal to nothing. – aagaardist Jan 07 '18 at 18:25
I’ll check syntax when home but have posted an update – QHarr Jan 07 '18 at 19:12
Just looked at your update and that is exactly what i need. – aagaardist Jan 07 '18 at 20:09
Pleased to hear it – QHarr Jan 07 '18 at 20:09
Did you find a way to fix the syntax error in the line? `If Target.Offset(, -1) = 1 Then` – aagaardist Jan 08 '18 at 16:20
Did you try the above edit? It sets C to "" when B is empty and A = 0 and if B =1 then A=1and C=9999 – QHarr Jan 08 '18 at 16:36
Yes i tried it now and it works, but is it possible to only make it dependent on Column A? so that if A=1 then C=9999 and if A=0 then C=""?. So the macro do not care with column B at all? – aagaardist Jan 08 '18 at 19:28
This is a tricky one as you would be catching the change in a formula by the worksheet_calculate event rather than the worksheet_change (which required manual changes to cells, hence my use of column B). See link [here](https://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula). I would avoid the calculate route if possible as you will need to find a way to capture the prior states and then test if changed. IMHO it is far easier to monitor Col B since you state Col A is dependant on it. – QHarr Jan 08 '18 at 19:45
Okay thank you very much :) I'll use it as it is then! – aagaardist Jan 08 '18 at 20:33

Middle · Answer 2 · 2018-01-07T06:46:09.220

0

Check to see if this is something you are looking for:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As Range, inter As Range, r As Range
Set C = Range("C9:C21")
Set inter = Intersect(C, Target)
If inter Is Nothing Then Exit Sub

Application.EnableEvents = False
  For Each r In inter
     'this checks cell on the same row as r in column A
     If cells(r.row,1) = "Whatever values you need here" Then r.Value = 9999
     end if
  Next r
Application.EnableEvents = True
End Sub

Please clarify further if you want something else.

edited Jan 07 '18 at 06:46

answered Jan 06 '18 at 19:58

Middle

143
1
8

2

I thought one of the requisites was *'If column "A1" = 1 it should insert 9999 in column "C1"'*. – Jan 06 '18 at 20:10
Yes this code doesn't work, since it doesn't use the column A, but sets C9 to C21 equal to 9999 if i enter 1 in this range. It should be as Jeeped stated. – aagaardist Jan 07 '18 at 18:53

VBA Default value in cell based on another cell

2 Answers2