0

I've got a code to work for only cells D3 and E3, I need it to do the same for other cells like D4/E4, D5/E5 but with different formulae on the same worksheet. I'm trying to make new private subs for the other cells but they do not seem to compile? Thanks in advance:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DE As Range, t As Range, v As Variant
    Dim r As Long
    Set t = Target
    Set DE = Range("D3:E3")
    If Intersect(t, DE) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r = t.Row
        v = t.Value
        If v = "" Then
            Range("D" & r & ":E" & r).Value = ""
        End If
        If IsNumeric(v) Then
            If Intersect(t, Range("E3:E3")) Is Nothing Then
                t.Offset(0, 1).Value = v * 0.0393701
            Else
                t.Offset(0, -1).Value = v / 0.0393701
            End If
        End If
    Application.EnableEvents = True
End Sub
DavidPostill
  • 156,873
akil17
  • 1
  • @DavidPostill - Why on earth was this Q closed? It's one of the best Q's I've seen in weeks! You closed this within 11 minutes of it being posted yet left weeks of crap questions alone in this topic's question list. –  Dec 04 '19 at 11:01
  • @akil27 - Is Range("D3" & r & ":E3" & r).Value = "" correct? Shouldn't it be Range("D" & r & ":E" & r).Value = "" ? –  Dec 04 '19 at 11:07
  • @jeeped OP Is asking very basic questions "I'm new to VBA and do not know how to compile multiple Subs" and should go read some VBA tutorials. – DavidPostill Dec 04 '19 at 11:16
  • @DavidPostill - Why don't you take your diamond and go close a few of the truly awful questions in this tag? There are several on the first page of the [tag:microsoft-excel] list. –  Dec 04 '19 at 11:19
  • 1
    @Jeeped If I come across bad questions I close them. I don't go looking for them. Note anybody with a close vote privilege can also vote the close bad questions (and do so). Mods are not the only people who maintain the site. In addition I am not and Excel expert so cannot always recognise a truly bad question. I leave that to the experts. – DavidPostill Dec 04 '19 at 11:21
  • @DavidPostill - Well, let me help you. This was posted just 6-7 minutes ago and is largely the same question as this one. –  Dec 04 '19 at 11:24
  • @DavidPostill I've read/watched tutorials which is how I ended up with the code. Really do need some help compiling this. – akil17 Dec 04 '19 at 14:27
  • @Jeeped Thank you, and yes it should have been Range("D" & r & ":E" & r).Value = "" – akil17 Dec 04 '19 at 14:28
  • 1
    @akil17 I've reopened it. Now you need to [edit] the question and add the error messages from when you try to compile. – DavidPostill Dec 04 '19 at 14:30

1 Answers1

0

Fixed it now:

Private Sub Worksheet_Change(ByVal Target As Range) Dim BC As Range, t As Range, v As Variant Dim r As Long Set t = Target Set BC = Range("D:E") If Intersect(t, BC) Is Nothing Then Exit Sub Application.EnableEvents = False r = t.Row v = t.Value

    If IsNumeric(v) Then
        If Intersect(t, Range("E3")) Is Nothing Then
            Range("E3").Formula = Range("D3") * 0.0393701

        Else
            Range("D3").Formula = Range("E3") / 0.0393701

        End If

         If Intersect(t, Range("E4")) Is Nothing Then
            Range("E4").Formula = Range("D4") * 3.28084

        Else
            Range("D4").Formula = Range("E4") / 3.28084

        End If

            If Intersect(t, Range("E5")) Is Nothing Then
            Range("E5").Formula = Range("D5") * 14.5038

        Else
            Range("D5").Formula = Range("E5") / 14.5038

        End If

                        If Intersect(t, Range("E6")) Is Nothing Then
            Range("E6").Formula = Range("D6") * 14.5038

        Else
            Range("D6").Formula = Range("E6") / 14.5038

        End If

                        If Intersect(t, Range("E7")) Is Nothing Then
            Range("E7").Formula = Range("D7") * 14.5038

        Else
            Range("D7").Formula = Range("E7") / 14.5038

        End If

                        If Intersect(t, Range("E9")) Is Nothing Then
            Range("E9").Formula = Range("D9") * 0.02628

        Else
            Range("D9").Formula = Range("E9") / 0.02628

        End If

                        If Intersect(t, Range("E10")) Is Nothing Then
            Range("E10").Formula = Range("D10") * 35.3147

        Else
            Range("D10").Formula = Range("E10") / 35.3147

        End If

 End If
Application.EnableEvents = True

End Sub

akil17
  • 1