Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Hi,
I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Not a stupid question, you know what you want to do and you've actually
written the code to do it. Now, the question to me is why you want to do it in code rather than with a formula. If you put this formula in L45 then it will get done without a macro: =L43*Invulblad!AA47 The order doesn't matter, it could be =Invulblad!AA47 * L43 Excel will even do the work for you: Click in L45 and type the = symbol then click in cell L43 then type the * symbol finally go to sheet Invulblad, click in cell AA47 and hit the [Enter] key and Excel will have built the formula for you. A note on sheet names - if your sheet name has a space in it, you need to enclose it in single quote marks like: =L43 * 'Invul blad'!AA47 Hope this helps some. If you don't want to do it with the formula, let me know and I'll provide some Worksheet_Change() event code to do the same thing. "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
I know the normal way of doing this. The reason i'm doing it through a macro
is because i want this in a calculation to be accessible for people (I want the cell to read L43*0.05 instead of IF(blabla=empty;"";L43*0,05). I am currently using a sheet_activate code (the following:) Private Sub Worksheet_Activate() If Range("D32").Value < "" Then Range("D33").Value = Range("N5").Value Range("D34").Value = Worksheets("Invulblad").Range("N23").Value Range("D36").Value = "=D33*D34" Range("D37").Value = Worksheets("Invulblad").Range("AF9").Value Range("D39").Value = "=D36*D37" Range("D40").Value = Worksheets("Invulblad").Range("AE51").Value Range("D41").Value = Worksheets("Invulblad").Range("K43").Value Range("D43").Value = "=D39+D40+D41" Range("D45").Value = Worksheets("Invulblad").Range("AA47").Value * L43 Range("D46").Value = Worksheets("Invulblad").Range("AA48").Value * L43 Range("D47").Value = Worksheets("Invulblad").Range("AA49").Value * L43 Range("D48").Value = Worksheets("Invulblad").Range("AA50").Value * L43 Range("D50").Value = "=D43+D46+D47+D48+D49" Range("D51").Value = Worksheets("Invulblad").Range("AF25").Value Range("D53").Value = "=MAX(D51:D52)" Range("D55").Value = (Range("D53").Value / Worksheets("Invulblad").Range("AA51")) - Range("D53").Value Range("D57").Value = "=D53+D55" Range("D59").Value = Worksheets("Invulblad").Range("W59").Value Else If Range("D32").Value = "" Then Range("D33:D59").Value = "" End If End If Greets & Thanks, Gunti "JLatham" wrote: Not a stupid question, you know what you want to do and you've actually written the code to do it. Now, the question to me is why you want to do it in code rather than with a formula. If you put this formula in L45 then it will get done without a macro: =L43*Invulblad!AA47 The order doesn't matter, it could be =Invulblad!AA47 * L43 Excel will even do the work for you: Click in L45 and type the = symbol then click in cell L43 then type the * symbol finally go to sheet Invulblad, click in cell AA47 and hit the [Enter] key and Excel will have built the formula for you. A note on sheet names - if your sheet name has a space in it, you need to enclose it in single quote marks like: =L43 * 'Invul blad'!AA47 Hope this helps some. If you don't want to do it with the formula, let me know and I'll provide some Worksheet_Change() event code to do the same thing. "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Gunti,
Since your objective is to learn something about macros, I figured I'd show you how to get this done with code. Both of these routines would need to go into the worksheet's code module (get to it by right-clicking on the sheet's name tab and choosing [View Code] from the list). You'd need to automatically update the contents of L45 at 2 times: when the sheet is activated (selected) so that any changes over in AA47 on Invulblad get taken care of, and again in the sheet's _Change() event to take care of times when someone changes the value of L43 while looking at it. So just copy the code below and paste it into that code module: Private Sub Worksheet_Activate() 'note that .Value is the default property, 'so we don't HAVE to mention it Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("L43")) _ Is Nothing Then Exit Sub ' change was not in cell L43 End If Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
I do have another question for you. Is there an even which activates when a
formula value changes? For example: Cell a1= IF(A2="x";"Yes";"No") I want an event to activate whenever 'Yes' changes to 'No' or any value. Greets, Gunti "Gunti" wrote: I know the normal way of doing this. The reason i'm doing it through a macro is because i want this in a calculation to be accessible for people (I want the cell to read L43*0.05 instead of IF(blabla=empty;"";L43*0,05). I am currently using a sheet_activate code (the following:) Private Sub Worksheet_Activate() If Range("D32").Value < "" Then Range("D33").Value = Range("N5").Value Range("D34").Value = Worksheets("Invulblad").Range("N23").Value Range("D36").Value = "=D33*D34" Range("D37").Value = Worksheets("Invulblad").Range("AF9").Value Range("D39").Value = "=D36*D37" Range("D40").Value = Worksheets("Invulblad").Range("AE51").Value Range("D41").Value = Worksheets("Invulblad").Range("K43").Value Range("D43").Value = "=D39+D40+D41" Range("D45").Value = Worksheets("Invulblad").Range("AA47").Value * L43 Range("D46").Value = Worksheets("Invulblad").Range("AA48").Value * L43 Range("D47").Value = Worksheets("Invulblad").Range("AA49").Value * L43 Range("D48").Value = Worksheets("Invulblad").Range("AA50").Value * L43 Range("D50").Value = "=D43+D46+D47+D48+D49" Range("D51").Value = Worksheets("Invulblad").Range("AF25").Value Range("D53").Value = "=MAX(D51:D52)" Range("D55").Value = (Range("D53").Value / Worksheets("Invulblad").Range("AA51")) - Range("D53").Value Range("D57").Value = "=D53+D55" Range("D59").Value = Worksheets("Invulblad").Range("W59").Value Else If Range("D32").Value = "" Then Range("D33:D59").Value = "" End If End If Greets & Thanks, Gunti "JLatham" wrote: Not a stupid question, you know what you want to do and you've actually written the code to do it. Now, the question to me is why you want to do it in code rather than with a formula. If you put this formula in L45 then it will get done without a macro: =L43*Invulblad!AA47 The order doesn't matter, it could be =Invulblad!AA47 * L43 Excel will even do the work for you: Click in L45 and type the = symbol then click in cell L43 then type the * symbol finally go to sheet Invulblad, click in cell AA47 and hit the [Enter] key and Excel will have built the formula for you. A note on sheet names - if your sheet name has a space in it, you need to enclose it in single quote marks like: =L43 * 'Invul blad'!AA47 Hope this helps some. If you don't want to do it with the formula, let me know and I'll provide some Worksheet_Change() event code to do the same thing. "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Thanks alot for explaining this, even though it still isn't what i'm looking
for. I'm learning alot from looking at other people's code. The thing is that i've come as far as what code you gave me. Range("D55").Value = (Range("D53").Value / Worksheets("Invulblad").Range("AA51")) - Range("D53").Value If D53 = 50 and AA51 = 0,05 doesn't actually show '0,05*50' It however shows 2,5. "JLatham" wrote: Gunti, Since your objective is to learn something about macros, I figured I'd show you how to get this done with code. Both of these routines would need to go into the worksheet's code module (get to it by right-clicking on the sheet's name tab and choosing [View Code] from the list). You'd need to automatically update the contents of L45 at 2 times: when the sheet is activated (selected) so that any changes over in AA47 on Invulblad get taken care of, and again in the sheet's _Change() event to take care of times when someone changes the value of L43 while looking at it. So just copy the code below and paste it into that code module: Private Sub Worksheet_Activate() 'note that .Value is the default property, 'so we don't HAVE to mention it Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("L43")) _ Is Nothing Then Exit Sub ' change was not in cell L43 End If Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Now i'm getting completely confused haha. I actually meant it to show
'0,05*D53* "Gunti" wrote: Thanks alot for explaining this, even though it still isn't what i'm looking for. I'm learning alot from looking at other people's code. The thing is that i've come as far as what code you gave me. Range("D55").Value = (Range("D53").Value / Worksheets("Invulblad").Range("AA51")) - Range("D53").Value If D53 = 50 and AA51 = 0,05 doesn't actually show '0,05*50' It however shows 2,5. "JLatham" wrote: Gunti, Since your objective is to learn something about macros, I figured I'd show you how to get this done with code. Both of these routines would need to go into the worksheet's code module (get to it by right-clicking on the sheet's name tab and choosing [View Code] from the list). You'd need to automatically update the contents of L45 at 2 times: when the sheet is activated (selected) so that any changes over in AA47 on Invulblad get taken care of, and again in the sheet's _Change() event to take care of times when someone changes the value of L43 while looking at it. So just copy the code below and paste it into that code module: Private Sub Worksheet_Activate() 'note that .Value is the default property, 'so we don't HAVE to mention it Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("L43")) _ Is Nothing Then Exit Sub ' change was not in cell L43 End If Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
If the goal is to have the macro create an equation:
Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Where you are trying to set .Value to equal a new formula, as
Range("D36").Value = "=D33*D34" use .Formula instead for those cases, as Range("D36").Formula = "=D33*D34" and I think you'll get where you want to be. "Gunti" wrote: I know the normal way of doing this. The reason i'm doing it through a macro is because i want this in a calculation to be accessible for people (I want the cell to read L43*0.05 instead of IF(blabla=empty;"";L43*0,05). I am currently using a sheet_activate code (the following:) Private Sub Worksheet_Activate() If Range("D32").Value < "" Then Range("D33").Value = Range("N5").Value Range("D34").Value = Worksheets("Invulblad").Range("N23").Value Range("D36").Value = "=D33*D34" Range("D37").Value = Worksheets("Invulblad").Range("AF9").Value Range("D39").Value = "=D36*D37" Range("D40").Value = Worksheets("Invulblad").Range("AE51").Value Range("D41").Value = Worksheets("Invulblad").Range("K43").Value Range("D43").Value = "=D39+D40+D41" Range("D45").Value = Worksheets("Invulblad").Range("AA47").Value * L43 Range("D46").Value = Worksheets("Invulblad").Range("AA48").Value * L43 Range("D47").Value = Worksheets("Invulblad").Range("AA49").Value * L43 Range("D48").Value = Worksheets("Invulblad").Range("AA50").Value * L43 Range("D50").Value = "=D43+D46+D47+D48+D49" Range("D51").Value = Worksheets("Invulblad").Range("AF25").Value Range("D53").Value = "=MAX(D51:D52)" Range("D55").Value = (Range("D53").Value / Worksheets("Invulblad").Range("AA51")) - Range("D53").Value Range("D57").Value = "=D53+D55" Range("D59").Value = Worksheets("Invulblad").Range("W59").Value Else If Range("D32").Value = "" Then Range("D33:D59").Value = "" End If End If Greets & Thanks, Gunti "JLatham" wrote: Not a stupid question, you know what you want to do and you've actually written the code to do it. Now, the question to me is why you want to do it in code rather than with a formula. If you put this formula in L45 then it will get done without a macro: =L43*Invulblad!AA47 The order doesn't matter, it could be =Invulblad!AA47 * L43 Excel will even do the work for you: Click in L45 and type the = symbol then click in cell L43 then type the * symbol finally go to sheet Invulblad, click in cell AA47 and hit the [Enter] key and Excel will have built the formula for you. A note on sheet names - if your sheet name has a space in it, you need to enclose it in single quote marks like: =L43 * 'Invul blad'!AA47 Hope this helps some. If you don't want to do it with the formula, let me know and I'll provide some Worksheet_Change() event code to do the same thing. "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
What you are looking to do, as Gary''s Student has figured out, is to
actually rewrite the formula and he has shown how to do it. Use the .Formula property rather than the .Value property UNLESS you actually want to display the formula in the cell rather than the results. If you want the user to actually see the formula when looking at the cell and not the result of the rewritten formula, then change his line of code from Range("L45").Formula = "=" & v & "*L43" to Range("L45").Formula = "'=" & v & "*L43" note the added single quote mark in front of the "=" symbol, it's a little hard to see here. But placing that quote mark in front of the = symbol turns it into text in the cell. "Gunti" wrote: Thanks alot for explaining this, even though it still isn't what i'm looking for. I'm learning alot from looking at other people's code. The thing is that i've come as far as what code you gave me. Range("D55").Value = (Range("D53").Value / Worksheets("Invulblad").Range("AA51")) - Range("D53").Value If D53 = 50 and AA51 = 0,05 doesn't actually show '0,05*50' It however shows 2,5. "JLatham" wrote: Gunti, Since your objective is to learn something about macros, I figured I'd show you how to get this done with code. Both of these routines would need to go into the worksheet's code module (get to it by right-clicking on the sheet's name tab and choosing [View Code] from the list). You'd need to automatically update the contents of L45 at 2 times: when the sheet is activated (selected) so that any changes over in AA47 on Invulblad get taken care of, and again in the sheet's _Change() event to take care of times when someone changes the value of L43 while looking at it. So just copy the code below and paste it into that code module: Private Sub Worksheet_Activate() 'note that .Value is the default property, 'so we don't HAVE to mention it Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("L43")) _ Is Nothing Then Exit Sub ' change was not in cell L43 End If Range("L45") = _ Worksheets("Invulblad").Range("AA47") * Range("L43") End Sub "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Thanks alot both of you, for trying to help! I'm not getting it however. When
i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Mkay i'm being a retard. I put the code above my old code for M45 which was
'=175' : I'm trying it again, however, and i'm getting an object code error! Runtime error '1004', Application-defined or object-defined error. Again, this is my code: Private Sub Worksheet_Activate() If Range("m32").Value < "" Then Range("M33").Value = Range("O5").Value Range("m34").Value = Worksheets("Invulblad").Range("N23").Value Range("m36").Value = "=m33*m34" Range("m37").Value = Worksheets("Invulblad").Range("AH9").Value Range("m39").Value = "=m36*m37" Range("m40").Value = Worksheets("Invulblad").Range("AE51").Value Range("m41").Value = Worksheets("Invulblad").Range("K43").Value Range("m43").Value = "=m39+m40+m41" -------------------------------------------------------------- m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" ------------------------------------------------------------- Range("m46").Value = Worksheets("Invulblad").Range("AA48").Value * Range("m43").Value Range("m47").Value = Worksheets("Invulblad").Range("AA49").Value * Range("m43").Value Range("m48").Value = Worksheets("Invulblad").Range("AA50").Value * Range("m43").Value Range("m50").Value = "=m43+m45+m46+m47+m48" Range("m51").Value = Worksheets("Invulblad").Range("AH25").Value Range("m53").Value = "=MAX(m50:m51)" Range("m55").Value = (Range("m53").Value / Worksheets("Invulblad").Range("AA51")) - Range("m53").Value Range("m57").Value = "=m53+m55" Range("m59").Value = Worksheets("Invulblad").Range("Y59").Value Else If Range("m32").Value = "" Then Range("m33:m59").Value = "" End If End If End Sub "Gunti" wrote: Thanks alot both of you, for trying to help! I'm not getting it however. When i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
m = Worksheets("Invulblad").Range("AA47").Value
Range("M45").Formula = "=M43 * " & m & "" Try it this way, 1st line OK, but change the second one to: Range("M45").Formula = "=M43 * " & m also change .Value to .Formula in the following lines to get what I think you are after: Range("m36").Value = "=m33*m34" Range("m39").Value = "=m36*m37" Range("m43").Value = "=m39+m40+m41" those should read (again, I think) Range("m36").Formula = "=m33*m34" Range("m39").Formula = "=m36*m37" Range("m43").Formula = "=m39+m40+m41" same thing on down with lines that begin with Range("m50").value = Range("m53").value = Range("m59").value = Hope this helps get you to the next step. "Gunti" wrote: Mkay i'm being a retard. I put the code above my old code for M45 which was '=175' : I'm trying it again, however, and i'm getting an object code error! Runtime error '1004', Application-defined or object-defined error. Again, this is my code: Private Sub Worksheet_Activate() If Range("m32").Value < "" Then Range("M33").Value = Range("O5").Value Range("m34").Value = Worksheets("Invulblad").Range("N23").Value Range("m36").Value = "=m33*m34" Range("m37").Value = Worksheets("Invulblad").Range("AH9").Value Range("m39").Value = "=m36*m37" Range("m40").Value = Worksheets("Invulblad").Range("AE51").Value Range("m41").Value = Worksheets("Invulblad").Range("K43").Value Range("m43").Value = "=m39+m40+m41" -------------------------------------------------------------- m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" ------------------------------------------------------------- Range("m46").Value = Worksheets("Invulblad").Range("AA48").Value * Range("m43").Value Range("m47").Value = Worksheets("Invulblad").Range("AA49").Value * Range("m43").Value Range("m48").Value = Worksheets("Invulblad").Range("AA50").Value * Range("m43").Value Range("m50").Value = "=m43+m45+m46+m47+m48" Range("m51").Value = Worksheets("Invulblad").Range("AH25").Value Range("m53").Value = "=MAX(m50:m51)" Range("m55").Value = (Range("m53").Value / Worksheets("Invulblad").Range("AA51")) - Range("m53").Value Range("m57").Value = "=m53+m55" Range("m59").Value = Worksheets("Invulblad").Range("Y59").Value Else If Range("m32").Value = "" Then Range("m33:m59").Value = "" End If End If End Sub "Gunti" wrote: Thanks alot both of you, for trying to help! I'm not getting it however. When i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
This is perfect, i've got exactly what i need. Thanks for taking the time and
effort to help me. :) If you've got any time left i'd like to refer to an earlier question of mine. If it is possible to have my macro activate when a formula recalculates it's value. (In this case D33 changes from "" to "All-in" from it's formula. Greetz, Can't thank you enough Gunti "JLatham" wrote: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" Try it this way, 1st line OK, but change the second one to: Range("M45").Formula = "=M43 * " & m also change .Value to .Formula in the following lines to get what I think you are after: Range("m36").Value = "=m33*m34" Range("m39").Value = "=m36*m37" Range("m43").Value = "=m39+m40+m41" those should read (again, I think) Range("m36").Formula = "=m33*m34" Range("m39").Formula = "=m36*m37" Range("m43").Formula = "=m39+m40+m41" same thing on down with lines that begin with Range("m50").value = Range("m53").value = Range("m59").value = Hope this helps get you to the next step. "Gunti" wrote: Mkay i'm being a retard. I put the code above my old code for M45 which was '=175' : I'm trying it again, however, and i'm getting an object code error! Runtime error '1004', Application-defined or object-defined error. Again, this is my code: Private Sub Worksheet_Activate() If Range("m32").Value < "" Then Range("M33").Value = Range("O5").Value Range("m34").Value = Worksheets("Invulblad").Range("N23").Value Range("m36").Value = "=m33*m34" Range("m37").Value = Worksheets("Invulblad").Range("AH9").Value Range("m39").Value = "=m36*m37" Range("m40").Value = Worksheets("Invulblad").Range("AE51").Value Range("m41").Value = Worksheets("Invulblad").Range("K43").Value Range("m43").Value = "=m39+m40+m41" -------------------------------------------------------------- m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" ------------------------------------------------------------- Range("m46").Value = Worksheets("Invulblad").Range("AA48").Value * Range("m43").Value Range("m47").Value = Worksheets("Invulblad").Range("AA49").Value * Range("m43").Value Range("m48").Value = Worksheets("Invulblad").Range("AA50").Value * Range("m43").Value Range("m50").Value = "=m43+m45+m46+m47+m48" Range("m51").Value = Worksheets("Invulblad").Range("AH25").Value Range("m53").Value = "=MAX(m50:m51)" Range("m55").Value = (Range("m53").Value / Worksheets("Invulblad").Range("AA51")) - Range("m53").Value Range("m57").Value = "=m53+m55" Range("m59").Value = Worksheets("Invulblad").Range("Y59").Value Else If Range("m32").Value = "" Then Range("m33:m59").Value = "" End If End If End Sub "Gunti" wrote: Thanks alot both of you, for trying to help! I'm not getting it however. When i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
I was a bit hasty when replying. It now points out that it works.. when 'm'
is 0,00 As soon as i change it to 0,05 it gets a debug?? Gunti "Gunti" wrote: This is perfect, i've got exactly what i need. Thanks for taking the time and effort to help me. :) If you've got any time left i'd like to refer to an earlier question of mine. If it is possible to have my macro activate when a formula recalculates it's value. (In this case D33 changes from "" to "All-in" from it's formula. Greetz, Can't thank you enough Gunti "JLatham" wrote: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" Try it this way, 1st line OK, but change the second one to: Range("M45").Formula = "=M43 * " & m also change .Value to .Formula in the following lines to get what I think you are after: Range("m36").Value = "=m33*m34" Range("m39").Value = "=m36*m37" Range("m43").Value = "=m39+m40+m41" those should read (again, I think) Range("m36").Formula = "=m33*m34" Range("m39").Formula = "=m36*m37" Range("m43").Formula = "=m39+m40+m41" same thing on down with lines that begin with Range("m50").value = Range("m53").value = Range("m59").value = Hope this helps get you to the next step. "Gunti" wrote: Mkay i'm being a retard. I put the code above my old code for M45 which was '=175' : I'm trying it again, however, and i'm getting an object code error! Runtime error '1004', Application-defined or object-defined error. Again, this is my code: Private Sub Worksheet_Activate() If Range("m32").Value < "" Then Range("M33").Value = Range("O5").Value Range("m34").Value = Worksheets("Invulblad").Range("N23").Value Range("m36").Value = "=m33*m34" Range("m37").Value = Worksheets("Invulblad").Range("AH9").Value Range("m39").Value = "=m36*m37" Range("m40").Value = Worksheets("Invulblad").Range("AE51").Value Range("m41").Value = Worksheets("Invulblad").Range("K43").Value Range("m43").Value = "=m39+m40+m41" -------------------------------------------------------------- m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" ------------------------------------------------------------- Range("m46").Value = Worksheets("Invulblad").Range("AA48").Value * Range("m43").Value Range("m47").Value = Worksheets("Invulblad").Range("AA49").Value * Range("m43").Value Range("m48").Value = Worksheets("Invulblad").Range("AA50").Value * Range("m43").Value Range("m50").Value = "=m43+m45+m46+m47+m48" Range("m51").Value = Worksheets("Invulblad").Range("AH25").Value Range("m53").Value = "=MAX(m50:m51)" Range("m55").Value = (Range("m53").Value / Worksheets("Invulblad").Range("AA51")) - Range("m53").Value Range("m57").Value = "=m53+m55" Range("m59").Value = Worksheets("Invulblad").Range("Y59").Value Else If Range("m32").Value = "" Then Range("m33:m59").Value = "" End If End If End Sub "Gunti" wrote: Thanks alot both of you, for trying to help! I'm not getting it however. When i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Is the worksheet this is happening on protected? You can't alter cell
contents from within a macro if the cell is "Locked" and the sheet is protected. For your other question, it's going to be difficult. Excel does not 'see' a change made because of a formula. What we would have to do is find one of the cells that causes the cell to go from "" to "All in", watch for a change it one of those, and when it does change, then test to see if D33 then contains "All-in". What is the formula in D33? "Gunti" wrote: I was a bit hasty when replying. It now points out that it works.. when 'm' is 0,00 As soon as i change it to 0,05 it gets a debug?? Gunti "Gunti" wrote: This is perfect, i've got exactly what i need. Thanks for taking the time and effort to help me. :) If you've got any time left i'd like to refer to an earlier question of mine. If it is possible to have my macro activate when a formula recalculates it's value. (In this case D33 changes from "" to "All-in" from it's formula. Greetz, Can't thank you enough Gunti "JLatham" wrote: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" Try it this way, 1st line OK, but change the second one to: Range("M45").Formula = "=M43 * " & m also change .Value to .Formula in the following lines to get what I think you are after: Range("m36").Value = "=m33*m34" Range("m39").Value = "=m36*m37" Range("m43").Value = "=m39+m40+m41" those should read (again, I think) Range("m36").Formula = "=m33*m34" Range("m39").Formula = "=m36*m37" Range("m43").Formula = "=m39+m40+m41" same thing on down with lines that begin with Range("m50").value = Range("m53").value = Range("m59").value = Hope this helps get you to the next step. "Gunti" wrote: Mkay i'm being a retard. I put the code above my old code for M45 which was '=175' : I'm trying it again, however, and i'm getting an object code error! Runtime error '1004', Application-defined or object-defined error. Again, this is my code: Private Sub Worksheet_Activate() If Range("m32").Value < "" Then Range("M33").Value = Range("O5").Value Range("m34").Value = Worksheets("Invulblad").Range("N23").Value Range("m36").Value = "=m33*m34" Range("m37").Value = Worksheets("Invulblad").Range("AH9").Value Range("m39").Value = "=m36*m37" Range("m40").Value = Worksheets("Invulblad").Range("AE51").Value Range("m41").Value = Worksheets("Invulblad").Range("K43").Value Range("m43").Value = "=m39+m40+m41" -------------------------------------------------------------- m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" ------------------------------------------------------------- Range("m46").Value = Worksheets("Invulblad").Range("AA48").Value * Range("m43").Value Range("m47").Value = Worksheets("Invulblad").Range("AA49").Value * Range("m43").Value Range("m48").Value = Worksheets("Invulblad").Range("AA50").Value * Range("m43").Value Range("m50").Value = "=m43+m45+m46+m47+m48" Range("m51").Value = Worksheets("Invulblad").Range("AH25").Value Range("m53").Value = "=MAX(m50:m51)" Range("m55").Value = (Range("m53").Value / Worksheets("Invulblad").Range("AA51")) - Range("m53").Value Range("m57").Value = "=m53+m55" Range("m59").Value = Worksheets("Invulblad").Range("Y59").Value Else If Range("m32").Value = "" Then Range("m33:m59").Value = "" End If End If End Sub "Gunti" wrote: Thanks alot both of you, for trying to help! I'm not getting it however. When i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Hi, i figured it out. It was depending on a checkbox to check or uncheck. So
i created a submodule for the checkbox.. It works perfectly. I'm also getting a debug error on an empty book with the following code Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("A1").Value < "" Then v = Range("A1").Value Range("A2").Value = "=D43*" & v End If End Sub I think i'll just make it Range("A2").Value = "=D43*AA47" Thanks for the help anyway ;) "JLatham" wrote: Is the worksheet this is happening on protected? You can't alter cell contents from within a macro if the cell is "Locked" and the sheet is protected. For your other question, it's going to be difficult. Excel does not 'see' a change made because of a formula. What we would have to do is find one of the cells that causes the cell to go from "" to "All in", watch for a change it one of those, and when it does change, then test to see if D33 then contains "All-in". What is the formula in D33? "Gunti" wrote: I was a bit hasty when replying. It now points out that it works.. when 'm' is 0,00 As soon as i change it to 0,05 it gets a debug?? Gunti "Gunti" wrote: This is perfect, i've got exactly what i need. Thanks for taking the time and effort to help me. :) If you've got any time left i'd like to refer to an earlier question of mine. If it is possible to have my macro activate when a formula recalculates it's value. (In this case D33 changes from "" to "All-in" from it's formula. Greetz, Can't thank you enough Gunti "JLatham" wrote: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" Try it this way, 1st line OK, but change the second one to: Range("M45").Formula = "=M43 * " & m also change .Value to .Formula in the following lines to get what I think you are after: Range("m36").Value = "=m33*m34" Range("m39").Value = "=m36*m37" Range("m43").Value = "=m39+m40+m41" those should read (again, I think) Range("m36").Formula = "=m33*m34" Range("m39").Formula = "=m36*m37" Range("m43").Formula = "=m39+m40+m41" same thing on down with lines that begin with Range("m50").value = Range("m53").value = Range("m59").value = Hope this helps get you to the next step. "Gunti" wrote: Mkay i'm being a retard. I put the code above my old code for M45 which was '=175' : I'm trying it again, however, and i'm getting an object code error! Runtime error '1004', Application-defined or object-defined error. Again, this is my code: Private Sub Worksheet_Activate() If Range("m32").Value < "" Then Range("M33").Value = Range("O5").Value Range("m34").Value = Worksheets("Invulblad").Range("N23").Value Range("m36").Value = "=m33*m34" Range("m37").Value = Worksheets("Invulblad").Range("AH9").Value Range("m39").Value = "=m36*m37" Range("m40").Value = Worksheets("Invulblad").Range("AE51").Value Range("m41").Value = Worksheets("Invulblad").Range("K43").Value Range("m43").Value = "=m39+m40+m41" -------------------------------------------------------------- m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" ------------------------------------------------------------- Range("m46").Value = Worksheets("Invulblad").Range("AA48").Value * Range("m43").Value Range("m47").Value = Worksheets("Invulblad").Range("AA49").Value * Range("m43").Value Range("m48").Value = Worksheets("Invulblad").Range("AA50").Value * Range("m43").Value Range("m50").Value = "=m43+m45+m46+m47+m48" Range("m51").Value = Worksheets("Invulblad").Range("AH25").Value Range("m53").Value = "=MAX(m50:m51)" Range("m55").Value = (Range("m53").Value / Worksheets("Invulblad").Range("AA51")) - Range("m53").Value Range("m57").Value = "=m53+m55" Range("m59").Value = Worksheets("Invulblad").Range("Y59").Value Else If Range("m32").Value = "" Then Range("m33:m59").Value = "" End If End If End Sub "Gunti" wrote: Thanks alot both of you, for trying to help! I'm not getting it however. When i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Macro Question
Once again, I'm not sure what you want to put into A2, with .Value you are
trying to make A2 show the text of the formula rather than changing the formula itself. Try this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim v As Integer If Range("A1") < "" Then v = Val(Range("A1").Value) Range("A2").Formula = "=D43*" & v End If End Sub This will prevent 'Type Mismatch" errors. A Type Mismatch would occur in your formula if A1 contained some text, such as your name and not a number (an integer number in this case). Think about it, A1 contains "JLatham", so your code would build the formula =D43*JLatham which won't work. By using the Val() operator, the numeric value of the contents of A1 is obtained, and since the numeric value of "JLatham" is zero (see, I am pretty worthless), then the formula ends up as =D43*0 which is a valid formula. "Gunti" wrote: Hi, i figured it out. It was depending on a checkbox to check or uncheck. So i created a submodule for the checkbox.. It works perfectly. I'm also getting a debug error on an empty book with the following code Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("A1").Value < "" Then v = Range("A1").Value Range("A2").Value = "=D43*" & v End If End Sub I think i'll just make it Range("A2").Value = "=D43*AA47" Thanks for the help anyway ;) "JLatham" wrote: Is the worksheet this is happening on protected? You can't alter cell contents from within a macro if the cell is "Locked" and the sheet is protected. For your other question, it's going to be difficult. Excel does not 'see' a change made because of a formula. What we would have to do is find one of the cells that causes the cell to go from "" to "All in", watch for a change it one of those, and when it does change, then test to see if D33 then contains "All-in". What is the formula in D33? "Gunti" wrote: I was a bit hasty when replying. It now points out that it works.. when 'm' is 0,00 As soon as i change it to 0,05 it gets a debug?? Gunti "Gunti" wrote: This is perfect, i've got exactly what i need. Thanks for taking the time and effort to help me. :) If you've got any time left i'd like to refer to an earlier question of mine. If it is possible to have my macro activate when a formula recalculates it's value. (In this case D33 changes from "" to "All-in" from it's formula. Greetz, Can't thank you enough Gunti "JLatham" wrote: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" Try it this way, 1st line OK, but change the second one to: Range("M45").Formula = "=M43 * " & m also change .Value to .Formula in the following lines to get what I think you are after: Range("m36").Value = "=m33*m34" Range("m39").Value = "=m36*m37" Range("m43").Value = "=m39+m40+m41" those should read (again, I think) Range("m36").Formula = "=m33*m34" Range("m39").Formula = "=m36*m37" Range("m43").Formula = "=m39+m40+m41" same thing on down with lines that begin with Range("m50").value = Range("m53").value = Range("m59").value = Hope this helps get you to the next step. "Gunti" wrote: Mkay i'm being a retard. I put the code above my old code for M45 which was '=175' : I'm trying it again, however, and i'm getting an object code error! Runtime error '1004', Application-defined or object-defined error. Again, this is my code: Private Sub Worksheet_Activate() If Range("m32").Value < "" Then Range("M33").Value = Range("O5").Value Range("m34").Value = Worksheets("Invulblad").Range("N23").Value Range("m36").Value = "=m33*m34" Range("m37").Value = Worksheets("Invulblad").Range("AH9").Value Range("m39").Value = "=m36*m37" Range("m40").Value = Worksheets("Invulblad").Range("AE51").Value Range("m41").Value = Worksheets("Invulblad").Range("K43").Value Range("m43").Value = "=m39+m40+m41" -------------------------------------------------------------- m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "=M43 * " & m & "" ------------------------------------------------------------- Range("m46").Value = Worksheets("Invulblad").Range("AA48").Value * Range("m43").Value Range("m47").Value = Worksheets("Invulblad").Range("AA49").Value * Range("m43").Value Range("m48").Value = Worksheets("Invulblad").Range("AA50").Value * Range("m43").Value Range("m50").Value = "=m43+m45+m46+m47+m48" Range("m51").Value = Worksheets("Invulblad").Range("AH25").Value Range("m53").Value = "=MAX(m50:m51)" Range("m55").Value = (Range("m53").Value / Worksheets("Invulblad").Range("AA51")) - Range("m53").Value Range("m57").Value = "=m53+m55" Range("m59").Value = Worksheets("Invulblad").Range("Y59").Value Else If Range("m32").Value = "" Then Range("m33:m59").Value = "" End If End If End Sub "Gunti" wrote: Thanks alot both of you, for trying to help! I'm not getting it however. When i use this: m = Worksheets("Invulblad").Range("AA47").Value Range("M45").Formula = "'=M43 * " & m & "" The result is that i just see a number in cell M45 (175,-) The result i want is indeed 175, but i want people to actually see that it is Cell M43*0.05 (in this case AA47 = 0.05) I've tried both options you guys gave, can't thank you enough for taking the time to helping me anyway ;) Gunti "Gary''s Student" wrote: If the goal is to have the macro create an equation: Sub gunti() v = Worksheets("Invulblad").Range("AA47").Value Range("L45").Formula = "=" & v & "*L43" End Sub -- Gary''s Student - gsnu200820 "Gunti" wrote: Hi, I'm very new to creating macro's. I've got a (maybe stupid question). I have the following situation: Basiscly what i want (what i would make of it) is the following code: Range("L45").Value = "Worksheets("Invulblad").Range("AA47").Value * L43" I want cell L45 to say: =0,05*L43 if AA47 on sheet 'Invulblad' is 0,05 and =0,00*L43 if AA47 on sheet 'Invulblad' is 0,00 Any help appreciated, Greets, Gunti |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
simple macro question | Excel Worksheet Functions | |||
question about syntax in a simple macro... | Excel Discussion (Misc queries) | |||
Macro question - I'm sure it's simple... | Excel Discussion (Misc queries) | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) |