New to Macros Help with Range apply to whole column/row not one cell

[h1271333]

Hi I'm working on a simple worksheet with 6 columns:
A = Buy Price (pre populated)
B = Sell Price (To be input by User or Calculated)
C = Amount (pre populated)
D = Buy Value ( A * C )
E = Sell Value (To be input by User or Calculated)
F = Pecentage (To be input by User or Calculated)

Basically I Want it so that a user will input Either B, E, or F (only one) and the other 2 are then automatically calculated.

So far I have :

[INDENT]Private Sub Worksheet_Change(ByVal Target As Range)[/INDENT]
[INDENT]Application.EnableEvents = False[/INDENT]
[INDENT]
[/INDENT]
[INDENT]If Target.Address = "$B$3" Then [E3] = (([B3] * [C3]) / 100)[/INDENT]
[INDENT]If Target.Address = "$B$3" Then [F3] = (([E3] / [D3]) * 100) - 100[/INDENT]
[INDENT]
[/INDENT]
[INDENT]If Target.Address = "$E$3" Then [B3] = ([E3] / [C3]) * 100[/INDENT]
[INDENT]If Target.Address = "$E$3" Then [F3] = (([E3] / [D3]) * 100) - 100[/INDENT]
[INDENT]
[/INDENT]
[INDENT]If Target.Address = "$F$3" Then [B3] = (([F3] / 100) * [A3]) + [A3][/INDENT]
[INDENT]If Target.Address = "$F$3" Then [E3] = (([B3] * [C3]) / 100)[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Application.EnableEvents = True[/INDENT]
[INDENT]End Sub[/INDENT]

This works great for Row 3 but won't work for other rows (rows 3 - xx)

I've tried some options I found through Google ( issue seems to be If Target.Address = )

Any Help Please.

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("B:B,E:E,F:F")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 Then
   [E3] = (([B3] * [C3]) / 100)
   [F3] = (([E3] / [d3]) * 100) - 100
ElseIf Target.Column = 5 Then
   [B3] = ([E3] / [C3]) * 100
   [F3] = (([E3] / [d3]) * 100) - 100
ElseIf Target.Column = 6 Then
   [B3] = (([F3] / 100) * [A3]) + [A3]
   [E3] = (([B3] * [C3]) / 100)
End If
Application.EnableEvents = True
End Sub

 

[h1271333]

Hi thanks for the reply.

I've tried the above but it still only works for B3, E3 or F3 and not for rows 4 onwards.

Like I said I'm new to this so unless I'm applying it incorrectly, (doubtful as it works for Row 3)

I have a feeling that If Target.Column = 2
Then [E3] = (([B3] * [C3]) / 100)

and the rest, needs to somehow not reference row 3 specifically but be a variable, such as If Target.Column = 2
Then [E#] = (([B#] * [C#]) / 100)

where # is any row where the change is made.

If that makes sense.

Cheers

 

[Fluff]

Apologies wasn't thinking.
You'll need to change the calculations to

  Range("E" & Target.Row) = ((Range("B" & Target.Row) * Range("C" & Target.Row)) / 100)

 

[h1271333]

Thank you very much.

for anyone else needing it here is complete code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("B:B,E:E,F:F")) Is Nothing Then Exit Sub


Application.EnableEvents = False


If Target.Column = 2 Then


Range("E" & Target.Row) = ((Range("B" & Target.Row) * Range("C" & Target.Row)) / 100)
    
Range("F" & Target.Row) = ((Range("E" & Target.Row) / Range("D" & Target.Row)) * 100) - 100
   
 


ElseIf Target.Column = 5 Then


Range("B" & Target.Row) = ((Range("E" & Target.Row) / Range("C" & Target.Row)) * 100)


 
   
Range("F" & Target.Row) = ((Range("E" & Target.Row) / Range("D" & Target.Row)) * 100) - 100
   


ElseIf Target.Column = 6 Then


Range("B" & Target.Row) = (((Range("F" & Target.Row) / 100) * Range("A" & Target.Row)) + Range("A" & Target.Row))


 
    
Range("E" & Target.Row) = (((Range("F" & Target.Row) * Range("C" & Target.Row)) / 100))






End If
Application.EnableEvents = True
End Sub

It should be easy enough to change the relevant parts to suit your own needs.