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