Simple Macro Question

[Gunti]

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

 

[JLatham]

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]

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(D5152)"
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("D3359").Value = ""
End If
End If

Greets & Thanks,
Gunti

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.

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

 

[JLatham]

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]

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

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(D5152)"
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("D3359").Value = ""
End If
End If

Greets & Thanks,
Gunti

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.

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

 

[Gunti]

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.

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

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

 

[Gunti]

Now i'm getting completely confused haha. I actually meant it to show
'0,05*D53*

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.

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

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

 

[Gary''s Student]

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

 

[JLatham]

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.

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(D5152)"
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("D3359").Value = ""
End If
End If

Greets & Thanks,
Gunti

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.

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

 

[JLatham]

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.

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.

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

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

 

[Gunti]

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

 

[Gunti]

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

 

[JLatham]

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]

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

 

[Gunti]

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

 

[JLatham]

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]

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]

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.