sometimes vba works sometimes not

[cicada]

hey everyone i am obviously doing something fundamentally though rudimentary wrong. Previously i entered code using visual basic and it ran beatifully and executed exactly as i wanted didnt save as it was a test run and reentered the same way (yes i am sure) and it does nothing. could someone give me a step by step on how to make the code run tried using help etc but cant find exactly what im looking. previously i didnt do anything just entered it and it started running. please help very frustrating. going backwards. thanks

Cicada

 

[RoryA]

It would help if we knew what the code was. From what you say, my best guess would be that it is event code that is supposed to run in response to you doing something and you have probably entered it into the wrong place (such as a normal module instead of a worksheet or ThisWorkbook module.)

 

[cicada]

thanks for the reply Rory. In fact im now having trouble getting anything to run. as you guessed i am running a change evnt procedure though i am pretty sure i am running it from a sheet object in a private module. I think i am doing something wrong i am new to all this. i am not interested in the old code anymore and am interested in your thoughts on the following code (that I also cant get to function



Private Sub Worksheet_Change(ByVal Target As Range)
With ThisWorkbook.Sheets("HILO_TURBO")
If Target <> Range("J7")Then Exit Sub
If Len(.Range("J7").Value) = 6 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("D3:D6").Value = .Range("J19:J22").Value
If Len(.Range("J7").Value) = 10 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("G3:G6").Value = .Range("J27:J30").Value
If Len(.Range("J7").Value) = 14 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("J3:J6").Value = .Range("J34:J37").Value
If Len(.Range("J7").Value) = 18 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("M3:M6").Value = .Range("J41:J44").Value
If Len(.Range("J7").Value) = 22 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("P3:P6").Value = .Range("J48:J51").Value
If Len(.Range("J7").Value) = 26 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("D9:D12").Value = .Range("J55:J58").Value
If Len(.Range("J7").Value) = 30 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("G9:G12").Value = .Range("J62:J65").Value
If Len(.Range("J7").Value) = 34 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("J9:J12").Value = .Range("J69:J72").Value
If Len(Range("J7").Value) = 38 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("M9:M12").Value = .Range("J76:J79").Value
End If
End With
End Sub
First of all a bit of background i have a workbook the main sheet (excuse technical language there probably is another word) is constantly updated with data from an external source. When certain conditions are met i would like for certain ranges on that sheet to be copied to sheet 1 and if those conditions are met again in subsequent updates for the old values on sheet one to be replaced with the new values. hope this is clear. firstly does this code look ok and secondly where would i put it and thirdly and i think im missing something here once entered do i press anything to effect it. in the past i have been pressing butttons with mixed results obviously sometimes having code work sometimes not. thanks for you help

Regards
Cicada

 

[RoryA]

That code belongs in the code module for the sheet where you are changing the value of J7. It could be simplified and corrected to:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet
    If Intersect(Target, Range("J7")) Is Nothing Then Exit Sub
    
    With ThisWorkbook.Sheets("HILO_TURBO")
        Set wks = .Parent.Sheets("sheet1")
        If .Range("E40").Value > 0 Then
            Select Case Len(.Range("J7").Value)
                Case 6
                    wks.Range("D3:D6").Value = .Range("J19:J22").Value
                Case 10
                    wks.Range("G3:G6").Value = .Range("J27:J30").Value
                Case 14
                    wks.Range("J3:J6").Value = .Range("J34:J37").Value
                Case 18
                    wks.Range("M3:M6").Value = .Range("J41:J44").Value
                Case 22
                    wks.Range("P3:P6").Value = .Range("J48:J51").Value
                Case 26
                    wks.Range("D9:D12").Value = .Range("J55:J58").Value
                Case 30
                    wks.Range("G9:G12").Value = .Range("J62:J65").Value
                Case 34
                    wks.Range("J9:J12").Value = .Range("J69:J72").Value
                Case 38
                    wks.Range("M9:M12").Value = .Range("J76:J79").Value
            End Select
        End If
    End With
End Sub

 

[cicada]

thanks for the code rory looks really good. I did all that you told me and still the code is not running. have checked security settings etc and still it doesnt want to cooperate. would it matter that J7 is a comma delimited string dont think so but worth the question. step by step this is what i am doing in HiLo_Turbo going to developer, clicking on visual basic editor, under microsoft excel objects clicking on HiLo_Turbo then entering code in page that comes up that does have some other stuff on it. after entering do i need to press run sub user form, when i do it doesnt work it brings up a pop up that has a list other macros created by developer. any clues. Thankyou for your time much appreciated.

Regards
Cicada

 

[RoryA]

The best way to make sure the code is in the right place is to right-click the worksheet tab and choose View Code. If the code appears, then it's in the right module. If not, then it isn't. I assume you have enabled macros when opening the workbook?

 

[cicada]

have enabled macros and when i do as instructed it brings up the code. no idea whats wrong. could there e something else. thanks

cicada

 

[RoryA]

Change it to this so we can see if it's even running:

Private Sub Worksheet_Change(ByVal Target As Range)
   Msgbox "Change event firing!"
    Dim wks As Worksheet
    If Intersect(Target, Range("J7")) Is Nothing Then Exit Sub
    
    With ThisWorkbook.Sheets("HILO_TURBO")
        Set wks = .Parent.Sheets("sheet1")
        If .Range("E40").Value > 0 Then
            Select Case Len(.Range("J7").Value)
                Case 6
                    wks.Range("D3:D6").Value = .Range("J19:J22").Value
                Case 10
                    wks.Range("G3:G6").Value = .Range("J27:J30").Value
                Case 14
                    wks.Range("J3:J6").Value = .Range("J34:J37").Value
                Case 18
                    wks.Range("M3:M6").Value = .Range("J41:J44").Value
                Case 22
                    wks.Range("P3:P6").Value = .Range("J48:J51").Value
                Case 26
                    wks.Range("D9:D12").Value = .Range("J55:J58").Value
                Case 30
                    wks.Range("G9:G12").Value = .Range("J62:J65").Value
                Case 34
                    wks.Range("J9:J12").Value = .Range("J69:J72").Value
                Case 38
                    wks.Range("M9:M12").Value = .Range("J76:J79").Value
            End Select
        End If
    End With
End Sub

and just to check - you need to be changing J7 manually, not as the result of a formula.

 

[cicada]

hi rory, sorry didnt respond right away, J7 is not data that i insert myself it changes automatically. It is updated every 10 seconds or so and its contents are determined by an external source. it is simply a comma delimited string that can contain from 0 to 13 numbers. does this impact on the code. thanks again for your time.

Cicada

 

[cicada]

ok so i entered data into j7 manually and nothing happened. assuming that when i enter number etc with length of six charcters i should get numbers appearing in sheet1 range D3:D6 from range J19:J22. nothing happening. no idea. any ideas. thanks again.

Cicada