WHat I am doing wrong....

[msussams]

What Am I doing wrong
what I want to input in code is this:


Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 2 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, -1) = Format(Now(), "MM-DD-yyyy")
Target.Offset(0, 6).Value = "TO"
Target.Offset(0, 7).Value = "TEFA"
Target.Offset(0, 1).Value = "GWR"
Target.Offset(0, 8).formula = "=IF(I156="","",IF(I156="TEFA",IF(O156="D/D","Disputed","Unallocated"),VLOOKUP(I156,Bugle!A:C,3)))"
Application.EnableEvents = True
End If
End Sub


But it doesn't like it when I add this line - it comes up with an error.


Target.Offset(0, 8).formula = "=IF(I156="","",IF(I156="TEFA",IF(O156="D/D","Disputed","Unallocated"),VLOOKUP(I156,Bugle!A:C,3)))"


It works ok without itbefore i input it but doesn't work with it or after I have to recopy and paste from an old version to get it to do it without the formula

What is it

[SamT]

I always have trouble using Double-quotes in a VBA String. Try making all " into "", and all "" into """".

[msussams]

I always have trouble using Double-quotes in a VBA String. Try making all " into "", and all "" into """".

You are a genius,
Thank you

Now, do you know how to make the cells relative so each row down it will move down

[SamT]

I take it that this Event Sub is not in sheet "Bugle"
Is the value in column H supposed to change according to changes in Bugle?

What do you mean
"IT will move down?"

[msussams]

I take it that this Event Sub is not in sheet "Bugle"
Is the value in column H supposed to change according to changes in Bugle?

What do you mean


so sorry - the bold cell references to move to the next row

thank you for this help


Target.Offset(0, 8).formula = "=IF(I156="","",IF(I156="TEFA",IF(O156="D/D","Disputed","Unallocated"),VLOOKUP(I156,Bugle!A:C,3)))"

[SamT]

Is the value in column H supposed to change according to changes in Bugle?
In other words, is the Target sheet dynamic with reference to Sheet Bugle?

In still more words, If a change in Bugle does not force a change in the Macro'ed sheet, you don't even need a formula

In order to solve the Row issue, one must have a way to determine the desired Row, that can best be handled via a cross reference between Target.Row and Range("I" & *156*), the cell that may or may not contain "TEFA".

I'm just trying to figure out what is going on in your workbook.


WTF?!?!?! I just looked at the entirety of the code in your OP.
Is Range("I156") the same Range as Target.Offset(0, 7) ?

[p45cal]

A guess: you've been working on row 156 and you want the formula to refer to the same row.
Try:

Target.Offset(0, 8).FormulaR1C1 = "=IF(RC[-1]="""","""",IF(RC[-1]=""TEFA"",IF(RC[5]=""D/D"",""Disputed"",""Unallocated""),VLOOKUP(RC[-1],Bugle!C1:C3,3)))"

[Paul_Hossler]

Is it always row 156? I'm used to doing something like that for the row that I changed

What happens if you change multiple cells in column 2 with Ctrl-Enter?

[snb]

What Am I doing wrong

You are using VBA to solve something you should solve in Excel.
You can solve this easily in VBA without Excel.

[p45cal]

You are using VBA to solve something you should solve in Excel.
You can solve this easily in VBA without Excel.
…a confusing final statement.

[msussams]

Thank you all for your responses.
ive solved it now

thanks