| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with this macro?
The intent of the macro was to force information into cell(s) once, but allow
the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is wrong with this macro?
Sub Worksheet_Change(ByVal Target As Range)
Means that anytime this sheet changes run macro "Brad" wrote: The intent of the macro was to force information into cell(s) once, but allow the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is wrong with this macro?
Maybe try this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target < "" Then Exit Sub -- Your code here -- End Sub "Brad" wrote: The intent of the macro was to force information into cell(s) once, but allow the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is wrong with this macro?
The macro - works correctly the first time - but ...
If I am not mistaken, each time the code runs, SW is initialized to "" Dim SW As String When you set it later in your code... SW = "used" it is not remembered between calls. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Brad" wrote in message ... The intent of the macro was to force information into cell(s) once, but allow the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is wrong with this macro?
You answered my question - but it leads me to another question.
There must be a way to have the Macro remember the variable. "Dana DeLouis" wrote: The macro - works correctly the first time - but ... If I am not mistaken, each time the code runs, SW is initialized to "" Dim SW As String When you set it later in your code... SW = "used" it is not remembered between calls. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Brad" wrote in message ... The intent of the macro was to force information into cell(s) once, but allow the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is wrong with this macro?
Brad,
Instead of: Dim SW As String Use: Static SW As String "Brad" wrote: You answered my question - but it leads me to another question. There must be a way to have the Macro remember the variable. "Dana DeLouis" wrote: The macro - works correctly the first time - but ... If I am not mistaken, each time the code runs, SW is initialized to "" Dim SW As String When you set it later in your code... SW = "used" it is not remembered between calls. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Brad" wrote in message ... The intent of the macro was to force information into cell(s) once, but allow the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is wrong with this macro?
There must be a way to have the Macro remember the variable.
Hi. Since I'm not familiar with the project, here is just a general idea: If you run this small demo a few times, you will see that Sw gets larger and larger with each call. Is this something you can use? Sub Example_Static() Static Sw As String Sw = Sw & "x" Debug.Print Sw End Sub It appears to me there is some type of logic error somewhere. If I am not mistaken, it appears the code could be written as: If Left(Range("Prod1").Value, 3) = "LBD" Then Sw = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And Sw < "used" Then Sw = "on" Range("D17").Value = Range("C9").Value Range("D19").Value = Range("C9").Value Sw = "used" End If If so, I don't see why Sw would be turned "On", and then set to "Used." There may be other combinations missing as well. Anyway, just a thought. :~ -- HTH :) Dana DeLouis Windows XP & Office 2007 "Brad" wrote in message ... You answered my question - but it leads me to another question. "Dana DeLouis" wrote: The macro - works correctly the first time - but ... If I am not mistaken, each time the code runs, SW is initialized to "" Dim SW As String When you set it later in your code... SW = "used" it is not remembered between calls. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Brad" wrote in message ... The intent of the macro was to force information into cell(s) once, but allow the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What is wrong with this macro?
That did it - thank you very much.
"Art" wrote: Brad, Instead of: Dim SW As String Use: Static SW As String "Brad" wrote: You answered my question - but it leads me to another question. There must be a way to have the Macro remember the variable. "Dana DeLouis" wrote: The macro - works correctly the first time - but ... If I am not mistaken, each time the code runs, SW is initialized to "" Dim SW As String When you set it later in your code... SW = "used" it is not remembered between calls. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Brad" wrote in message ... The intent of the macro was to force information into cell(s) once, but allow the user to change the information if they had better numbers. The macro - works correctly the first time - but it overrides any changes entered by the user. Sub Worksheet_Change(ByVal Target As Range) Dim lookrng1 As Range Dim lookrng2 As Range Dim lookrng3 As Range Dim lookrng4 As Range Dim lookrng5 As Range Dim key2 As String Dim SW As String Application.EnableEvents = True If Left(Range("Prod1").Value, 3) = "LBD" Then SW = "off" ElseIf Left(Range("Prod1").Value, 3) < "LBD" And SW < "used" Then SW = "on" End If If SW = "on" Then Range("d17").Value = Range("c9").Value Range("d19").Value = Range("c9").Value SW = "used" End If |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vb macro format is wrong | New Users to Excel | |||
| What is wrong with this Macro? | Excel Worksheet Functions | |||
| What's wrong with this macro? | Excel Programming | |||
| What have I done wrong with this Macro??? | Excel Programming | |||
| What have I got wrong? (Macro),,, | Excel Programming | |||
Linear Mode
